REST API XML Example

A currency example with the REST API connector.

Dataset

This is an FX price dataset provided by CLS on Quandl. You can find the documentation for the API here:

https://www.quandl.com/databases/CLSRP/documentation

Sample Response

{
  "datatable": {
    "data": [
      [
        "USDCAD",
        "SPT",
        "2018-01-02",
        "2018-01-02",
        12,
        0,
        1.2548,
        1.25479,
        1.25479,
        1.25479
      ],
      [
        "USDCAD",
        "SPT",
        "2018-01-02",
        "2018-01-02",
        12,
        5,
        1.25453,
        1.25454,
        1.25451,
        1.25452
      ],
      [
        "USDCAD",
        "SPT",
        "2018-01-02",
        "2018-01-02",
        12,
        10,
        1.25417,
        1.25418,
        1.25418,
        1.25418
      ],
      [
        "USDCAD",
        "SPT",
        "2018-01-02",
        "2018-01-02",
        12,
        15,
        1.25416,
        1.25415,
        1.25415,
        1.25414
      ]
    ],
    "columns": [
      {
        "name": "currency",
        "type": "String"
      },
      {
        "name": "instrument",
        "type": "String"
      },
      {
        "name": "fx_business_date",
        "type": "Date"
      },
      {
        "name": "london_date",
        "type": "Date"
      },
      {
        "name": "hour",
        "type": "Integer"
      },
      {
        "name": "minute",
        "type": "Integer"
      },
      {
        "name": "vwap_matched",
        "type": "double"
      },
      {
        "name": "twap_matched",
        "type": "double"
      },
      {
        "name": "vwap_unmatched",
        "type": "double"
      },
      {
        "name": "twap_unmatched",
        "type": "double"
      }
    ]
  },
  "meta": {
    "next_cursor_id": null
  }
}

Data Sync Config

Note that you'll have to get your own api key to use the endpoint.

<?xml version="1.0" encoding="utf-16"?>
<BatchDataSyncConfig name="Quandl CLS FX Prices" version="1.0.0" xmlns="http://www.cinchy.co">
    <Parameters>
    <Parameter name="date" />
    </Parameters>
    <RestApiDataSource  format="JSON" 
                        rootPath="$.datatable.data" 
                        endpoint="https://www.quandl.com/api/v3/datatables/CLS/IDHP?fx_business_date=@date&amp;api_key=API_KEY"
                        nextPageUrlPath="$.meta.next_cursor_id">
        <Schema>
            <Column name="$.item[0]" dataType="Text" />
            <Column name="$.item[3]" dataType="Date" />
            <Column name="$.item[4]" dataType="Number" />
            <Column name="$.item[5]" dataType="Number" />
            <Column name="$.item[6]" dataType="Number" />
            <Column name="$.item[7]" dataType="Number" />
            <CalculatedColumn name="Date" formula="CONCAT(@date,'')" dataType="Date" />
        </Schema>
    </RestApiDataSource>
    <CinchyTableTarget model="" domain="FX" table="CLS Prices" suppressDuplicateErrors="false">
        <ColumnMappings>
            <ColumnMapping sourceColumn="$.item[0]" targetColumn="Currency" linkColumn="CLSFX Abbreviation"/>
            <ColumnMapping sourceColumn="$.item[3]" targetColumn="Date"/>
            <ColumnMapping sourceColumn="$.item[4]" targetColumn="Hour"/>
            <ColumnMapping sourceColumn="$.item[5]" targetColumn="Minute"/>
            <ColumnMapping sourceColumn="$.item[6]" targetColumn="VWAP Matched"/>
            <ColumnMapping sourceColumn="$.item[7]" targetColumn="TWAP Matched"/>
        </ColumnMappings>
        <SyncKey>
            <SyncKeyColumnReference name="Currency" />
            <SyncKeyColumnReference name="Date" />
            <SyncKeyColumnReference name="Hour" />
            <SyncKeyColumnReference name="Minute" />
        </SyncKey>
        <NewRecordBehaviour type="INSERT" />
        <ChangedRecordBehaviour type="UPDATE" />
        <DroppedRecordBehaviour type="DELETE" />
    </CinchyTableTarget>
</BatchDataSyncConfig>

.\Cinchy.CLI.exe syncdata -s "CinchyURL" -u "username" -p "password" -m "Cinchy" -f "Quandl CLS FX Prices" -d "C:\temp" -v date:"2018-01-02"

Table Model

This model and reference data will allow you to run the data sync above in your own environment.

4KB

CLS Prices.xml

Currencies Model

503B

Currencies.csv

Currencies Data Import

1KB

Currency Pairs.csv

Currency Pairs Data Import