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 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.

#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

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

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.

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:

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:

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:

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):

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:

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

New Upload (Missing Val1):

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:

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

Existing Data:

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

New Upload (Missing Val1 column):

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:

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.xsignals.xpansiv.com/ts/symbol/search?query=<symbol>

Example:

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

Response:

{
    "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:

2. Get Time Series Data by Symbols

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

HTTP Request:

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

Example:

curl -X POST "https://api.xsignals.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):

{
  "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):

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:

3. Search Deltas and Corrections

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

HTTP Request:

POST https://api.xsignals.xpansiv.com/ts

Example:

curl -X POST "https://api.xsignals.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):

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: