Manually upserting Horizon: DataScope Select data into the LUSID Quote Store

We recommend you configure the Horizon: DataScope Select EOD pricing and FX rate jobs to automatically upsert quotes on a daily basis for the instruments you're interested in; see how to do this. However, Horizon: DataScope Select also gives you the option to directly query the DataScope Select API and import quotes for one or more instruments on an ad hoc basis.

To do so, you must write a Luminesce SQL query that uses the MarketData.Refinitiv.EndOfDay.Price, Refinitiv.DataScopeSelect.EndOfDay.Quote and Lusid.Instrument.Quote.Writer providers together to do the following:

1. Retrieve EOD pricing data for a table of instrument identifiers using MarketData.Refinitiv.EndOfDay.Price.
2. Convert the EOD pricing data into a table of data that is ready to upsert to a specified quote scope in the LUSID Quote Store using Refinitiv.DataScopeSelect.EndOfDay.Quote.
3. Upsert the table of data from step 2 to the LUSID Quote Store using Lusid.Instrument.Quote.Writer.

Note that while this example uses the EOD pricing providers, the same principle applies when upserting FX rate data using the MarketData.Refinitiv.EndOfDay.FxRate and Refinitiv.DataScopeSelect.EndOfDay.FxRate providers.  

Note: The LUSID user running the query must have sufficient access control permissions to both use the providers and read instrument quote data in LUSID. This should automatically be the case if you are the domain owner.

For example, to retrieve and upsert EOD prices for some instruments using their ISINs, you can run the following query using a suitable tool:

@vals = values
  ('Isin', 'GB00BPQY8M80'),
  ('Isin', 'GB00BH4HKS39');

@table_of_data = select column1 as IdentifierType, column2 as Identifier from @vals;

@prices = select * from MarketData.Refinitiv.EndOfDay.Price where Request = @table_of_data;

@quotes = select 'Finbourne-Horizon-Examples' as QuoteScope, * from Refinitiv.DataScopeSelect.EndOfDay.Quote where EndOfDayData = @prices;

select * from Lusid.Instrument.Quote.Writer where ToWrite = @quotes

The table of data returned by the query looks like this:

Once your quotes are upserted to the Quote Store, you can configure a recipe to use this market data when performing valuations of your holdings. See how to configure the DataScope Select jobs to import the data you need on a daily basis.