How to pull historical price data for multiple companies in Excel using Workspace?

Options
Ashish_Tiwari260
Ashish_Tiwari260 LSEG
in FX Market Data

Hi everyone,

I'm building a database in Excel with about 100 listed companies and indices. I want to pull historical closing price data for each of them—ideally with the dates in the first column, and each company's closing price in separate columns.

I've used the formula builder tool in Excel to get the latest share price for one company, but I’m not sure how to:

1. Pull daily closing prices (historical data) for each company

2. Arrange the data in a format where the dates are in one column and each company has its own price column

Is there a specific formula I should use for this? Or is there an easier way to set it up?

Would appreciate any guidance!
Thank you.

Tagged:

Answers

  • Gurpreet
    Gurpreet admin

    Hello @Ashish_Tiwari260

    The RDP.Data API call in excel can be used with data from cell references. I used the following formula along with the dates in Column1 and instruments in the Row1:

    =@RDP.Data(B$1,"TR.PriceClose",CONCAT("SDate=",TEXT($A2,"yyyy-mm-dd")))

    Result -

    image.png
  • Ashish_Tiwari260
    Ashish_Tiwari260 LSEG

    Hi Gurpreet,
    Thank you for the above.

    There's something more I want to know.
    I have a set of companies in my excel which is further categorized into groups (please see the image).
    Now without disturbing the order can I pull data from the workspace? If so can you tell me how its done.

    For background: The team I work for now, had a S&P's subscription but now is moving to LSEG for data. So the cells have formula of S&P.

    So, as I've mentioned above, can I use the same excel sheet/company list to pull data from workspace?

    Grateful if you could help me on this.

    Regards,
    Ashish

    image.png

Categories