How can i align data dates when pulling price data from python API?

Hi there, I am using the refinitiv python API to pull price data for stocks in a universe. Both the "date" and "calc date" fields yield slightly different dates for different stocks. My code (see below) should be pulling weekly dates for the past year starting from the most Friday.

How can I ensure the dates returned are aligned?

Find more posts tagged with

python

rdp-api

#technology

lseg-data-library

python api

Accepted answers

Jirapongse

@Michael_B

Did you try this one (TR.PriceClose)?

df = ld.get_data(
    universe = ['MILP.WA','1010.SE'],
    fields = [
        'PERCENT_CHG(TR.PriceClose(Curn=USD, Frq=CW, Sdate=0D,EDate=-51), LAG=1AW).calcdate',
        'PERCENT_CHG(TR.PriceClose(Curn=USD, Frq=CW, Sdate=0D,EDate=-51), LAG=1AW).date',
        'PERCENT_CHG(TR.PriceClose(Curn=USD, Frq=CW, Sdate=0D,EDate=-51), LAG=1AW)'])

The Calc Date is Friday.

All comments

wasin.w V3

Hello @Michael_B

Could you please clarify the world "align"? Do you mean you want to data to order by the Date column?

I have tested a given code with the https://developers.lseg.com/en/api-catalog/lseg-data-platform/lseg-data-library-for-python (aka Data Library version 2, see https://developers.lseg.com/en/article-catalog/article/essential-guide-to-the-data-libraries for more detail). The result is as follows.

The return dataframe object is grouped by the RIC code by default. You can change the dataframe object to reorder by the Date column manually.

Firstly, check and convert the Date column to be a datetime type.

import pandas as pd


stockReturns_df["Date"] = pd.to_datetime(stockReturns_df["Date"])

Next, use the pandas.sort_values method to sort data. The example of Ascending (oldest first) is as follows.

 stockReturns_df = stockReturns_df.sort_values(by="Date").reset_index(drop=True)


stockReturns_df

The example of Descending (newest first) is as follows.

stockReturns_df = stockReturns_df.sort_values(by="Date", ascending=False).reset_index(drop=True)

stockReturns_df

Jirapongse

@Michael_B

Thank you for reaching out to us.

I tested it on Workspace Excel and it returns the different dates.

I assume that Since 1010.SE is listed on the Saudi Arabian Stock Exchange, which is closed on Fridays, Thursday is used for weekly data.

You can try this one instead.

df = ld.get_data(
    universe = ['MILP.WA','1010.SE'],
    fields = [
        'PERCENT_CHG(TR.PriceClose(Curn=USD, Frq=CW, Sdate=0D,EDate=-51), LAG=1AW).calcdate',
        'PERCENT_CHG(TR.PriceClose(Curn=USD, Frq=CW, Sdate=0D,EDate=-51), LAG=1AW).date',
        'PERCENT_CHG(TR.PriceClose(Curn=USD, Frq=CW, Sdate=0D,EDate=-51), LAG=1AW)'])

The calcdate dates are similar.

However, it is better to contact the helpdesk team via LSEG Support to verify the formula and content.

Michael_B

Hi @wasin.w V3, by "aligning dates" I wasn't exactly referring to the order of the dates. More so, if I am trying to pull the price as of 03/04/2026, for certain stocks in the universe instead of returning that date it might yield 02/04/2026. This creates problems later when pivoting out of that dataframe's format. For example, the last screenshot in your response shows data for MILP.WA and 1010.SE. Row 0 and Row 1 are the first entries for both stocks, i.e., last week - I would expect to show the same date of 03/04/20206,

Michael_B

Hi @Jirapongse, thanks for taking a look! I had thought it could be due to the Stock Exchange being closed on Fridays but I was under the impression that "calcdate" would return the date of which the data is attempted to be pulled. So if my formula is looking for the 1 week price move to last Friday, the calcadate field would return last Friday. Or in other words, for the Saudi stocks, go to Friday's date and calculate the price change for the week leading up to it even if Friday has no impact because the exchange is closed if that makes sense? Is this not the functionality of calcdate and if not is there no way to achieve this?

Because I'd imagine this could occur for several other exchanges whether it be national holidays or some other reasons for date discrepancies, it seems unachievable to attempt to manually adjust for everything if you were to look the ACWI universe for example.

Jirapongse

@Michael_B

Did you try this one (TR.PriceClose)?

df = ld.get_data(
    universe = ['MILP.WA','1010.SE'],
    fields = [
        'PERCENT_CHG(TR.PriceClose(Curn=USD, Frq=CW, Sdate=0D,EDate=-51), LAG=1AW).calcdate',
        'PERCENT_CHG(TR.PriceClose(Curn=USD, Frq=CW, Sdate=0D,EDate=-51), LAG=1AW).date',
        'PERCENT_CHG(TR.PriceClose(Curn=USD, Frq=CW, Sdate=0D,EDate=-51), LAG=1AW)'])

The Calc Date is Friday.

Michael_B

@Jirapongse it does seem to align well with this, thank you for the help!

EXPLORE OUR SITES