Refinitiv Data in Mediawatcher
Refinitiv Data
In MediaWatcher
| About | Description |
| Revision | 1 |
| Date | 19 Jan 2023 |
| Authors | Joe Woodley |
Overview
The purpose of this document is to outline the flow of the plugins used for the SABC/ Refinitiv API integration and a separate guide primarily intended for SABC to alter their request data through the external files provided.
Quotes Service
Typical plugin entry in .conf
| <inBox> <name>Refinitiv</name> <inputType>1</inputType> <outputType>1</outputType> <inputDirectory>C:UsersJoeDesktopRTSWTOGMediaWatcherinbox</inputDirectory> <outputDirectory>C:UsersJoeDesktopRTSWTOGMediaWatcheroutbox</outputDirectory> <databaseHost>localhost</databaseHost> <databaseUserName>root</databaseUserName> <databaseName>SABC_Financial</databaseName> <downloadPeriod>100</downloadPeriod> <urlScriptName>C:UsersJoeDesktopSVN4_6_5appsTOGMediaWatcherRTSWbinwgetwgetRefinitivTokenPowershell.bat</urlScriptName> <urlFileType>json</urlFileType> <urlFileName>RefinitivToken</urlFileName> <plugin>TMWImportRefinitivFinancialData</plugin> <urlURL>https://api.rkd.refinitiv.com/api/TokenManagement/TokenManagement.svc/REST/Anonymous/TokenManagement_1/CreateServiceToken_1</urlURL> </inBox> |
- Hitting wgetRefinitivTokenPowershell.bat first:
- Executes refinitivServiceToken.ps1 script
- Ensure BINDIR is set correctly and links to the .ps1 location
- Outputs RefinitivToken.json, using the above config
- Inbox picks up RefinitivToken.json
- Token extracted
- Types map built from the types CSV file (filepath of tmwContext.homeDirectory + “\bin\wget\RefinitivRICTypes.csv” in source
- Executes wgetRefinitivQuotes.bat
- wgetRefinitivQuotes.bat
- Ensure BINDIR is set correctly and links to the wget.exe location
- Sends the Quotes request to Refinitiv, the JSON string of RICs is in here, see the “SABC_RIC_Quotes_Guide” document on how to fully update the RIC list using the Refinitiv website and the types CSV file
- RefinitivQuotesData.json response
- RefinitivQuotesData.json
- Parsed and written into the database
- Outputs Refinitiv_Quotes table
- Outputs Refinitiv_RICs table; single column table of all RICs requested
Time Series Service
Typical entry in .conf
| <inBox> <name>Refinitiv_TimeSeries</name> <inputType>1</inputType> <outputType>1</outputType> <inputDirectory>C:UsersJoeDesktopRTSWTOGMediaWatcherinbox</inputDirectory> <outputDirectory>C:UsersJoeDesktopRTSWTOGMediaWatcheroutbox</outputDirectory> <databaseHost>localhost</databaseHost> <databaseUserName>root</databaseUserName> <databaseName>SABC_Financial</databaseName> <urlFileType>json</urlFileType> <urlFileName>RefinitivTimeSeriesRequest</urlFileName> <plugin>TMWImportRefinitivTimeSeriesData</plugin> </inBox> |
- Should work together with the Quotes plugin entry above
- Based on this .conf, will trigger off of RefinitivTimeSeriesRequest.json reaching the inbox; test format below:
Test format
| { “TimeSeriesRequest”: { “Symbol”: “ZA20YT=XX”, “StartTime”: “2022-09-15T00:00:00”, “EndTime”: “2022-12-15T23:59:00”, “Interval”: “WEEKLY”, “Field”: [ “ASK” ], “MetaField”: [ “NAME” ] } } |
- Interval takes values of HOURLY, DAILY, WEEKLY and MONTHLY.
- Field takes values of OPEN, CLOSE, HIGH, LOW, CLOSEYIELD, VOLUME, BID and ASK; currently the source does not parse multiple, just a choice of 1
- Hitting wgetRefinitivTokenPowershell.bat first:
- Executes refinitivServiceToken.ps1 script
- Ensure BINDIR is set correctly and links to the .ps1 location
- Outputs RefinitivTimeSeriesToken.json to the inbox
- This has to be a different name to the token JSON outputted in the Quotes plugin
- RefinitivTimeSeriesToken.json
- Token extracted
- Params from RefinitivTimeSeriesToken.json set up
- If Interval is HOURLY, wgetRefinitivIntradayService.bat executed, else wgetRefinitivInterdayService.bat is executed
- wgetRefinitivInterdayService.bat
- Ensure BINDIR is set correctly and links to the wget.exe location
- Sends the Time Series Interday request to Refinitiv
- RefinitivInterdayData.json response
- wgetRefinitivIntradayService.bat
- Ensure BINDIR is set correctly and links to the wget.exe location
- Sends the Time Series Intraday request to Refinitiv
- RefinitivIntradayData.json response
- JSON output parsed for the database
- Outputs Refinitiv_TimeSeries table
- This will be dropped and recreated on each request
- Outputs Refinitiv_TimeSeries table
Updating RICs to Mediawatcher system
There are two parts to updating RICs, updating the request to the Quotes service, and updating the RIC type file:
Part 1
- A JSON file is provided with the current body of the Quotes request in JSON format (as of 29th Nov ‘22)
- Go to the Customer site – https://support-portal.rkd.refinitiv.com/
- Log in, click ‘Catalog’ in the API box, then ‘VIEW DETAILS>’ in the QUOTES box, then click ‘Retrieve Item v3’
- Towards the top right you’ll see two tabs – click ‘DEVELOP’, highlight the ‘JSON’ bulletpoint and click ‘LOAD FROM’, then ‘JSON File…’ and browse to the provided JSON File
- You should now see the fields update on the screen with all the codes in the request, and their field codes, split into Commodities, Currencies, Bonds boxes etc.
- The ‘Add Item’ button will add a new RIC to the request, just make sure you enter the RIC into the correct box, as you will notice the Field Codes are different for each box
- Once satisfied (you should click SUBMIT to check for a correct response), go to the INSPECT tab:
- You will see the updated JSON in the darker box; highlight the entire body from the top curly brace to the bottom curly brace, and CTRL+C copy
- Go into your original JSON file, CTRL+A highlight all, DELETE, then CTRL+V paste to update that file with your latest request, save
- you are effectively keeping this file up to date so you could also remove RICs if you need to
- Go to https://jsontostring.com/ in your browser. Locate the ‘Paste your JSON here:’ box, remove the default, and CTRL+V paste your new JSON in, and click ‘Convert’
- Your converted JSON will be in the box below. Click in there, CTRL+A select all and CTRL+C copy it
- Locate the ‘wgetRefinitvQuotes.bat’ file in your MediaWatcher installation, it should be in the C:UsersAdministratorDesktopRTSWbinwget folder
- Find the text in the line starting with –post-data=
- Delete the text that is there, including the surrounding “” marks, so it only reads –post-data= –header= (**Notice the space in between, that is important**)
- Put your text cursor directly after the equals sign and CTRL+V paste, again, check there is a space between the end of your text and the following –header text.
Part 2
- Now we have to update the types file, this is called RefinitivRICTypes.csv and will be located in the same folder as the above .bat file, located in C:UsersAdministratorDesktopRTSWbinwget
- You will find a row per type on each line; the first entry on the row is the type itself, then followed by a comma-separated list of the RICs that belong to that type
- e.g. COMMODITIES,XAU=X,XPT=X,LCOc1,XPD=X,Cc1 , the type is COMMODITIES, and the RICs after it belong to that type
- Whatever new RICs you have added in Part 1 need to be added here too; in the same comma-separated format, add the RICs onto the row of the type that the RIC belongs to; no spaces, just commas! Hit save when finished
- You should only ever be adding new RICs onto the rows in this file, doing anything else to the file might break the system!
Once both files are saved, rerun MediaWatcher. Your new RICs should be included in the database output.
Raw request JSON to be used on Refinitiv website
Update 15/12/22
| { “RetrieveItem_Request_3”: { “ItemRequest”: [ { “Fields”: “CF_NAME:CF_ASK:CF_CLOSE:CF_NETCHNG:PCTCHNG”, “RequestKey”: [ { “Name”: “XAU=X”, “NameType”: “RIC” }, { “Name”: “XPT=X”, “NameType”: “RIC” }, { “Name”: “LCOc1”, “NameType”: “RIC” } ], “Scope”: “List” }, { “Fields”: “CF_NAME:CF_ASK:CF_NETCHNG”, “RequestKey”: [ { “Name”: “ZAR=X”, “NameType”: “RIC” }, { “Name”: “EURZAR=X”, “NameType”: “RIC” }, { “Name”: “GBPZAR=X”, “NameType”: “RIC” }, { “Name”: “JPYZAR=X”, “NameType”: “RIC” }, { “Name”: “AUDZAR=X”, “NameType”: “RIC” }, { “Name”: “BWPZAR=X”, “NameType”: “RIC” }, { “Name”: “USDEUR=X”, “NameType”: “RIC” }, { “Name”: “JPYUSD=X”, “NameType”: “RIC” }, { “Name”: “JPY=X”, “NameType”: “RIC” }, { “Name”: “GBP=X”, “NameType”: “RIC” }, { “Name”: “NGN=X”, “NameType”: “RIC” }, { “Name”: “ZARUSD=X”, “NameType”: “RIC” }, { “Name”: “ZAREUR=X”, “NameType”: “RIC” }, { “Name”: “ZARJPY=X”, “NameType”: “RIC” }, { “Name”: “ZARAUD=X”, “NameType”: “RIC” }, { “Name”: “ZARBWP=X”, “NameType”: “RIC” }, { “Name”: “EUR=X”, “NameType”: “RIC” }, { “Name”: “USDGBP=X”, “NameType”: “RIC” } ], “Scope”: “List” }, { “Fields”: “CF_NAME:ASK_YIELD:YLD_NETCHG”, “RequestKey”: [ { “Name”: “ZA10YT=XX”, “NameType”: “RIC” }, { “Name”: “ZA20YT=XX”, “NameType”: “RIC” } ], “Scope”: “List” }, { “Fields”: “CF_NAME:CF_ASK:CF_NETCHNG”, “RequestKey”: [ { “Name”: “0#.JTOPI”, “NameType”: “RIC” }, { “Name”: “0#.JALSH”, “NameType”: “RIC” }, { “Name”: “0#.JFINI”, “NameType”: “RIC” }, { “Name”: “0#.JINDI”, “NameType”: “RIC” }, { “Name”: “0#.JRESI”, “NameType”: “RIC” } ], “Scope”: “List” }, { “Fields”: “CF_NAME:CF_ASK:CF_NETCHNG”, “RequestKey”: [ { “Name”: “0#ALLSHARE.J”, “NameType”: “RIC” } ], “Scope”: “List” }, { “Fields”: “CF_NAME:CF_CLOSE:CF_NETCHNG”, “RequestKey”: [ { “Name”: “.DJI”, “NameType”: “RIC” }, { “Name”: “.SP500”, “NameType”: “RIC” }, { “Name”: “.NDX”, “NameType”: “RIC” }, { “Name”: “.FTSE”, “NameType”: “RIC” }, { “Name”: “.FCHI”, “NameType”: “RIC” }, { “Name”: “.GDAXI”, “NameType”: “RIC” }, { “Name”: “.N225”, “NameType”: “RIC” }, { “Name”: “.HSI”, “NameType”: “RIC” }, { “Name”: “.SSEC”, “NameType”: “RIC” } ], “Scope”: “List” } ], “TrimResponse”: true, “IncludeChildItemQoS”: false } } |
Latest update 18/01/23
| { “RetrieveItem_Request_3”: { “ItemRequest”: [ { “Fields”: “CF_NAME:CF_ASK:CF_CLOSE:CF_NETCHNG:PCTCHNG”, “RequestKey”: [ { “Name”: “XAU=X”, “NameType”: “RIC” }, { “Name”: “XPT=X”, “NameType”: “RIC” }, { “Name”: “LCOc1”, “NameType”: “RIC” } ], “Scope”: “List” }, { “Fields”: “CF_NAME:CF_ASK:CF_NETCHNG”, “RequestKey”: [ { “Name”: “ZAR=X”, “NameType”: “RIC” }, { “Name”: “EURZAR=X”, “NameType”: “RIC” }, { “Name”: “GBPZAR=X”, “NameType”: “RIC” }, { “Name”: “JPYZAR=X”, “NameType”: “RIC” }, { “Name”: “AUDZAR=X”, “NameType”: “RIC” }, { “Name”: “BWPZAR=X”, “NameType”: “RIC” }, { “Name”: “USDEUR=X”, “NameType”: “RIC” }, { “Name”: “JPYUSD=X”, “NameType”: “RIC” }, { “Name”: “JPY=X”, “NameType”: “RIC” }, { “Name”: “GBP=X”, “NameType”: “RIC” }, { “Name”: “NGN=X”, “NameType”: “RIC” }, { “Name”: “ZARUSD=X”, “NameType”: “RIC” }, { “Name”: “ZAREUR=X”, “NameType”: “RIC” }, { “Name”: “ZARJPY=X”, “NameType”: “RIC” }, { “Name”: “ZARAUD=X”, “NameType”: “RIC” }, { “Name”: “ZARBWP=X”, “NameType”: “RIC” }, { “Name”: “EUR=X”, “NameType”: “RIC” }, { “Name”: “USDGBP=X”, “NameType”: “RIC” } ], “Scope”: “List” }, { “Fields”: “CF_NAME:ASK_YIELD:YLD_NETCHG”, “RequestKey”: [ { “Name”: “ZA10YT=XX”, “NameType”: “RIC” }, { “Name”: “ZA20YT=XX”, “NameType”: “RIC” } ], “Scope”: “List” }, { “Fields”: “CF_NAME:CF_ASK:CF_NETCHNG”, “RequestKey”: [ { “Name”: “0#.JTOPI”, “NameType”: “RIC” }, { “Name”: “0#.JALSH”, “NameType”: “RIC” }, { “Name”: “0#.JFINI”, “NameType”: “RIC” }, { “Name”: “0#.JINDI”, “NameType”: “RIC” }, { “Name”: “0#.JRESI”, “NameType”: “RIC” } ], “Scope”: “List” }, { “Fields”: “CF_NAME:CF_ASK:CF_NETCHNG”, “RequestKey”: [ { “Name”: “0#ALLSHARE.J”, “NameType”: “RIC” } ], “Scope”: “List” }, { “Fields”: “CF_NAME:CF_CLOSE:CF_NETCHNG”, “RequestKey”: [ { “Name”: “.DJI”, “NameType”: “RIC” }, { “Name”: “.SPX”, “NameType”: “RIC” }, { “Name”: “.NDX”, “NameType”: “RIC” }, { “Name”: “.FTSE”, “NameType”: “RIC” }, { “Name”: “.FCHI”, “NameType”: “RIC” }, { “Name”: “.GDAXI”, “NameType”: “RIC” }, { “Name”: “.N225”, “NameType”: “RIC” }, { “Name”: “.HSI”, “NameType”: “RIC” }, { “Name”: “.SSEC”, “NameType”: “RIC” }, { “Name”: “.BSESN”, “NameType”: “RIC” }, { “Name”: “.INX”, “NameType”: “RIC” } ], “Scope”: “List” } ], “TrimResponse”: true, “IncludeChildItemQoS”: false } } |
Refinitiv Username and Password
For the following identification, please seek your appropriate administrator:
ApplicationID:
Username:
Password:
