# Time Series in Xpansiv Data

## Overview

Time Series Data represents a sequence of data points collected or recorded at specific time intervals. In Xpansiv Data, time series data is used to track changes in market instruments, environmental assets, and other metrics over time.

**Advantages of Time Series Data:**

- Enables trend analysis and forecasting
- Provides historical context for decision-making
- Supports advanced analytics like deltas and corrections


**What are Deltas?**
Deltas represent the latest changes or updates to values within a time series. They help users identify what has changed since the last recorded state, making it easier to track updates and corrections.

## CSV File Specification (NCSV)

### File Structure Rules

The input file is a standard CSV file interpreted based on the following rules:

1. The file must be comma-separated
2. Rows starting with `#` are considered comments and will be ignored
3. The first non-comment row will be used as the header
4. All column names in the header must be unique
5. Only one "Date" column is allowed (i.e., one column labeled "Date" in the header)
  * The date should follow [ISO_8601](https://en.wikipedia.org/wiki/ISO_8601) standards
6. All columns to the **left** of the "Date" column will be considered part of the unique identifier for that row and record
7. All columns to the **right** of the "Date" column will be considered value columns. The default type (column without a type indicator) is "double" (a double-precision floating-point number)
8. If there are no values in a file, the "Date" column should not be present (see "Inserting Metadata Only" section)


### Example File Structure

The following is a simple example file containing two unique identifiers (`MySymbol01` and `MySymbol02`), two double value columns (`val01` and `val02`), and a Description column.


```csv
#Sample comment line
MySymbol01,MySymbol02,Date,val01,val02,Description(MD-S)
ABC.01D,XYZ.01E,2019-05-26T00:00:00Z,22,21,description1
ABC.02D,XYZ.02E,2019-05-27T00:00:00Z,1,21,description1
ABC.03D,XYZ.03E,2019-05-28T00:00:00Z,2,21,description2
ABC.04D,XYZ.04E,2019-05-29T00:00:00Z,78,21,description2
# this row will be ignored
ABC.05D,XYZ.05E,2019-05-30T00:00:00Z,,21,description2
ABC.05D,XYZ.06E,2019-06-01T00:00:00Z,1,21,description3
ABC.06D,XYZ.07E,2019-05-22T00:00:00Z,5,21,description3
```

## Time Zones

All dates are stored in milliseconds to maintain a common chronology. However, they can be inserted with specific time zones or offsets. You can specify a timezone by appending it to the date column name, or include an offset directly in the date string.

If no timezone or offset is specified, UTC is assumed. Do not use both a timezone in the header and an offset in the value.

### Timezone in Header Example


```csv
MySymbol,Date(Europe/Warsaw),Val1(I)
ABC.01.D,2019-05-26T12:00:00,1
ABC.01.D,2019-05-23T12:00:00,2
ABC.01.D,2019-05-22T12:00:00,3
```

### Offset in Value Example


```csv
MySymbol,Date,Val1(I)
ABC.01.D,2019-05-26T12:00:00+02:00,1
ABC.01.D,2019-05-23T12:00:00+02:00,2
ABC.01.D,2019-05-22T12:00:00+02:00,3
```

### Default Viewing Timezone

In the time series view, data can be displayed in a specific time zone. If you do not select a viewing timezone, the defined default timezone will be used. You can define this default by adding a metadata column named `TimeZone`. If this column is missing, it will be generated using the timezone from the date column.


```csv
MySymbol,Date(Europe/Warsaw),Val1(I),TimeZone(MD-S)
ABC.01.D,2019-05-26T12:00:00,1,Europe/London
ABC.01.D,2019-05-23T12:00:00,2,Europe/London
ABC.01.D,2019-05-22T12:00:00,3,Europe/London
```

In this case, the date is loaded in the `Europe/Warsaw` timezone. However, if you fetch data using the default settings, you will receive `Europe/London` time (e.g., 11:00 instead of 12:00).

## Value Types & Metadata

### Supported Types

We support two categories of data:

**1. Values:** Stored in the Time Series

- `(DB)` - Double (default)
- `(S)` - String
- `(I)` - Integer
- `(D)` - ISO-8601 Date


**2. Metadata:** Contextual information

- `(MD-DB)` - Double
- `(MD-S)` - String
- `(MD-I)` - Integer
- `(MD-D)` - ISO-8601 Date


If a `Description(MD-S)` column is provided, it will be displayed in the time series search results.

### Formatting Dates

When a column is of type Date, Value Date, or Metadata Date, the date can be inserted in various formats. Since dates must be stored in UTC, the import process allows for conversion:

- **Select columns to convert**
- **Select the timezone of the inserted dates**
- **Select current date format** *Note: If you provide a date with time but choose a format that ignores time, the time component will be cut off.*


**Examples:**

*Scenario 1:*

- Date: `2021-07-07T06:57:18`
- Selected timezone: `Europe/Warsaw`
- Current date format: `yyyy-MM-dd'T'HH:mm:ss`
- **Result:** `2021-07-07T04:57:18` (Simple conversion from Warsaw to UTC)


*Scenario 2:*

- Date: `2021-07-07T06:57:18`
- Selected timezone: `Europe/Warsaw`
- Current date format: `yyyy-MM-dd`
- **Result:** `2021-07-06T22:00:00`
- *Explanation:* Before conversion, the time is stripped, resulting in `2021-07-07T00:00:00`. The timezone conversion then subtracts 2 hours, resulting in the previous day.


### Metadata Relationships

There is a 1:1 relationship between the metadata column and the unique identifier. Therefore, there is no need to repeat the metadata on every row. The last non-blank metadata record is considered the most recent value.

**Example Input:**


```csv
MySymbol,Date,Val1(I),Val2(S),Val3,Description(MD-S),Year of production(MD-I),Units(MD-S)
ABC.01.D,2019-05-26T00:00:00Z,1,BA,22,Description1,1999,ft
ABC.01.D,2019-05-23T00:00:00Z,3,CD,21,Description2,1998,ft
ABC.01.D,2019-05-22T00:00:00Z,4,FD,11,Description3,1996,ft
```

**Interpretation:**

- **Unique Identifier:** MySymbol & Date
- **Val1:** Integer type value (exists in time series)
- **Val2:** String type value (exists in time series)
- **Val3:** Double type value (exists in time series)
- **Description:** String Metadata (exists in time series search)
- **Year of production:** Integer Metadata (does not exist in time series values)
- **Units:** String Metadata (does not exist in time series values)


## Special Cases

### Inserting Data with Empty Values

You can insert a file with empty cells in the value columns. The rows will be loaded correctly with nulls where appropriate.

**Example Input:**


```csv
MySymbol,Date,Val1(I),Val2(S),Val3,Description(MD-S),Year of production(MD-I),Units(MD-S)
ABC.01.D,2019-05-26T00:00:00Z,,BA,22,,1999,ft
ABC.01.D,2019-05-23T00:00:00Z,3,,21,Description2,1998,ft
ABC.01.D,2019-05-22T00:00:00Z,4,FD,,Description3,1996,ft
```

### Full Update (Overwrite)

When uploading a file with keys that already exist in the system, the new data will completely overwrite the existing rows for those specific timestamps.

**Existing Data:**


```csv
MySymbol,Date,Val1(I),Val2(S),Val3,Description(MD-S)
ABC.01.D,2019-05-26T00:00:00Z,1,BA,22,Description1
```

**New Upload (Update):**


```csv
MySymbol,Date,Val1(I),Val2(S),Val3,Description(MD-S)
ABC.01.D,2019-05-26T00:00:00Z,,,100,New Description
```

**Result:** The row is completely updated. `Val1` and `Val2` become empty, and `Val3` becomes 100.

### Column Structure Updates

If you upload a file with the same keys but a different set of columns (e.g., omitting `Val1` entirely), the new file structure overrides the previous one.

**Existing Data:**


```csv
MySymbol,Date,Val1(I),Val2(S),Val3
ABC.01.D,2019-05-26T00:00:00Z,1,BA,22
```

**New Upload (Missing `Val1`):**


```csv
MySymbol,Date,Val2(S),Val3
ABC.01.D,2019-05-26T00:00:00Z,,100
```

**Result:** The data from the new file overrides the previous one. `Val1` is effectively removed from this record's context in the update.

### Partial Update

It is possible to perform a partial update on time series data. This retains existing data if a column is missing from the new file. To do this, the `PARTIAL_UPDATE` flag must be set to `true` in the API payload or the "Partial Update" checkbox selected in the UI.

**Example Payload:**


```json
"fields":[{"name":"PARTIAL_UPDATE","value":"true"}...]
```

**Existing Data:**


```csv
MySymbol,Date,Val1(I),Val2(S),Val3
ABC.01.D,2019-05-26T00:00:00Z,1,BA,22
```

**New Upload (Missing `Val1` column):**


```csv
MySymbol,Date,Val2(S),Val3
ABC.01.D,2019-05-26T00:00:00Z,,100
```

**Interpretation:** Unlike a full update, the partial update retains the data from the old column if the column is missing in the new file. `Val1` remains `1`. However, if the column exists in the new file but the cell is empty (like `Val2`), it will be updated to an empty value.

### Inserting Metadata Only (No Values)

You can insert a file containing only symbols and metadata columns, without any value columns or a "Date" column. This generates symbols in the system that contain no time-series values.

These symbols can be searched for using the symbol search endpoint. If a client wishes to filter these out, they can use the `withValuesOnly` query parameter.

**Example of Metadata-Only File:**


```csv
Symbol1,Symbol2,Symbol3,Symbol4,Currency(MD-S),Description(MD-S)
S1,S2,S3,S4,CUR1,Desc1
S11,S22,S33,S44,CUR2,Desc2
```

## API Reference

### 1. Search Time Series Symbols

Symbols refer to products or instruments represented in the time series data.

**HTTP Request:**


```
GET https://api.data.xpansiv.com/ts/symbol/search?query=<symbol>
```

**Example:**


```shell
curl "https://api.data.xpansiv.com/ts/symbol/search?query=symbol.Country=USA&symbol.name=test"
```

**Response:**


```json
{
    "from": 0,
    "totalSize": 2,
    "items": [
        {
            "groupName": "group name",
            "symbols": {
                "SYM1": "A1",
                "SYM2": "B1"
            },
            "metadata": ["meta 1", "meta 2"],
            "values": ["val 1", "val 2"]
        }
    ]
}
```

**URL Parameters:**

| Parameter | Description |
|  --- | --- |
| symbol | Name of the symbol to search |
| size | Optional. Max size of returned hits list. Default is 100. Max is 1000 |
| from | Optional. Index of the first hit for paging. Default is 0 |


### 2. Get Time Series Data by Symbols

Retrieve time series data for specific symbols in the provided time range.

**HTTP Request:**


```
POST https://api.data.xpansiv.com/ts?size=<size>
```

**Example:**


```shell
curl -X POST "https://api.data.xpansiv.com/ts?size=5" \
  -d '{
    "startDate":"2017-01-11T10:01:03",
    "endDate":"2017-12-11T10:01:03",
    "stats":"true",
    "timeZone":"America/NewYork",
    "corrections":true,
    "onDateTime":"2018-12-01T10:01:03.999",
    "formatType":"STANDARD",
    "keys":[{
      "columns":["Open","Close","High"],
      "symbols":{"SYM1":"A1","SYM2":"B1"},
      "groupName":"fileGroupName",
      "timeZone":"America/Chicago"
    }]
  }'
```

**Response (JSON Example):**


```json
{
  "hasNextPage": true,
  "columns": [
    {
        "symbols": {
            "SYM2": "B1",
            "SYM1": "A1"
        },
        "valueName": "Value"
    }
  ],
  "values": [
    {
        "time": "2018-01-01T12:00:00",
        "values": [14.1]
    }
  ],
  "stats": [
    {
        "name": "count",
        "values": [20.0]
    }
  ]
}
```

**Response (CSV Example):**


```csv
time,"[B1, A1]|High","[B1, A1]|Close","[B1, A1]|Open"
1501218000000,73.11,72.11,71.11
1503896400000,83.11,82.11,81.11
1506574800000,93.11,92.11,91.11
```

**Request Body Parameters:**

| Parameter | Description |
|  --- | --- |
| startDate | Date from (ISO 8601) |
| endDate | Date to (ISO 8601) |
| stats | [true / false] statistics |
| timeZone | Time zone offset (default UTC) |
| corrections | [false / true] |
| onDateTime | Show results as it was at the given time (ISO 8601) |
| keys | Symbols to get from timeseries - **symbols**: Map of name-value pairs, **groupName**: Group name, **columns**: Optional list of column names, **timeZone**: Timezone name for the symbol |
| formatType | STANDARD, PANDAS, or NCSV |
| metadata | [false / true] includes symbol's metadata |
| metadataType | [false / true] includes metadata types |


### 3. Search Deltas and Corrections

Retrieve deltas (latest values) and optionally corrections for a given time period.

**HTTP Request:**


```
POST https://api.data.xpansiv.com/ts
```

**Example:**


```shell
curl -X POST "https://api.data.xpansiv.com/ts" \
  -d '{
    "corrections":true,
    "delta":true,
    "range":"BETWEEN",
    "startDate":"2022-01-01T00:00:00",
    "endDate":"2022-01-07T23:59:59",
    "formatType":"NCSV",
    "groupName":"groupName"
  }'
```

**Response (CSV Example):**


```csv
Instrument,Market,Order Ref,Original,Side,Date(America/New_York),Balance,Ccy(S),Country(S),Date In(S),Instrument Mkt Name(S),Instrument Name(S),Price,Project ID(S),Project Type(S),Quantity,Time In(S),Type(S),ValueTax,Vintage(S)
INST1,SIP,123,321,BID,2022-01-01T16:00:00,20,USD,,2022.01.01,INST1-SIP,Instrument 1 Emission Offset,1.23,,,20,9:54:01,LIMIT,,
INST2,Voluntary,456,654,ASK,2022-01-01T16:00:00,1000,USD,Asia/China,2022.01.01,INST2-Voluntary,Instrument 2,2.3,ABC,Biogas - Cogeneration,1300,8:10:01,LIMIT,,
INST3,Voluntary,789,987,ASK,2022-01-01T16:00:00,20000,USD,Asia/India,2022.01.01,INST3-Voluntary,Instrument 2,4.5,2026,Energy Industries - renewable/non-renewable sources,500,13:48:17,AON,,
```

**Body Parameters:**

| Parameter | Description |
|  --- | --- |
| corrections | [false / true] - Return all versions of updated values |
| delta | [false / true] - Return new values |
| range | [BETWEEN] |
| startDate | Date from (ISO 8601) |
| endDate | Date to (ISO 8601) |
| formatType | [NCSV / STANDARD] |
| groupName | Name of group to be searched |
| dateFormat | (Optional) - Date format string, defaults to yyyy-MM-dd'T'HH:mm:ss.SSS |
| onDateTime | (Optional) - Date, shows results as it was at the given time (ISO 8601) |