Gathering information from Refinitiv API - Lipper ID problem

edoardo.modotti01

Hello,

I’m writing this since I have a problem with the Refinitiv Workspace API regarding the Lipper RIC and the Lipper ID.

Let me explain step by step what I’m trying to do.

NOTE: I would like to use Python since is the easiest way to do this and I have a PDF file with all the codes - provided by a friend of mine - to perform this task (I’m currently using python through the jupiter notebook in ANACONDA).

1. The problem is that I’m trying to gather, from the refinitiv API, a list of fund based on their Lipper ID and, for each fund, his NAV, his TER and the relative date expressed in month/year. The problem is that, since I’m looking also for dead (liquidated) funds (due to survivorship bias problem), the Lipper RIC does not work (because the dead funds have a slightly different code) and I must use the Lipper ID (because, starting from the Lipper ID I can obtain the correct RIC code to, then download the NAV). Infact, furthermore, the NAV is the only data suitable for this search.

2. So, in the end, starting from the Lipper ID that I have attached i would like to have an excel file with (in the columns) Lipper ID, Name of the fund, his NAV, his TER and in the rows the relative date repeating for each single entries starting from january 2000 to january 2021 (20 years), monthly.

Is this possibile? Please contact me, I'm able to pay if needed

Thank you

PS: As said I have a written guide to do all of this in python but I would like some help since I'm new to the program. I also have the list of Lipper ID to start the research

bob.lee

@edoardo.modotti01 ,

Summary of the study of your requirement use the CODEBK app in Eikon/Workspace:

1. Using native Lipper ID (i.e. the 8-digit number) Eikon API does able to return data for Liquidated and Merged funds.
2. Eikon API does have historical prices (e.g. NAV) data
3. Eikon API does have historical TER data.

For historical NAV, below is the sample code for one fund’s monthly month-end historical prices during the period. Actually, it would be simpler to get daily prices rather than setting month-end dates like I did but the data size would be much larger.

import eikon as ek
import calendar, pandas as pd

ek.set_app_key('<Your app key code string>')
lipper_id = '65000900'
data_table = None
for year in range(2000, 2022):
    for month in range(1, 13):
        (weekday, day) = calendar.monthrange(year, month)
        data_date = str(year) + '-' + str(month).zfill(2) + '-' + str(day)
        fund_NAV, err = ek.get_data(lipper_id, ['TR.FundNAV.Date', 'TR.FundNAV'], {'SDate':data_date, 'EDate':data_date, 'Curn':'Native'})
        if type(fund_NAV.iat[0,2]) != pd._libs.missing.NAType:
            data_table = data_table.append(fund_NAV) if isinstance(data_table, pd.DataFrame) else fund_NAV

The Dataframe: data_table should then contains data like:

Instrument    Date    NAV
0    65000900    2005-03-31T00:00:00Z    100
0    65000900    2005-04-29T00:00:00Z    97.51
0    65000900    2005-05-31T00:00:00Z    99.16
0    65000900    2005-06-30T00:00:00Z    101.5
0    65000900    2005-07-29T00:00:00Z    103.89
...    ...    ...    ...
0    65000900    2012-11-30T00:00:00Z    121.71
0    65000900    2012-12-31T00:00:00Z    125.5
0    65000900    2013-01-31T00:00:00Z    134.22
0    65000900    2013-02-28T00:00:00Z    134.25
0    65000900    2013-03-29T00:00:00Z    130.01

* This is a "merged" fund that launched in 2005 and merged to another fund in 2013.

For historical TER, data, below is the sample code for one fund’s data. You also need to aware that TER data may not cover full history of the fund due to different reasons (e.g. Lipper may only have good TER coverage after 2010), and for the TER’s effective dates, the TER should used for the fund from its “data as of date” until the next TER data point.

import eikon as ek

ek.set_app_key('<Your app key code string>')
lipper_id = '65124150'
fund_TER, err = ek.get_data(lipper_id, ['TR.FundTERDate', 'TR.FundTER'], {'SDate':'1999-12-31', 'EDate':'2019-12-31'})

You should get the data in Dataframe: fund_TER:

Instrument    Total Expense Ratio Date    Total Expense Ratio
0    65124150    2011-12-31    3.09
1    65124150    2012-12-31    3.10
2    65124150    2013-12-31    3.00
3    65124150    2014-12-31    3.24

For both samples above, I assume you will have an outer loop to loop through all the funds in your list. Notes, it takes a while for one fund’s full price history. So for the complete data of your funds, it may take a whole day to complete.

bob.lee

@edoardo.modotti01 , I do not find any attachment you mentioned. It will help people to understand the requirements better if you have the attachment of the input you plan to use. My understanding is you want to historical NAV prices of funds in a list (list of Lipper IDs). I assume you only meant to use the Eikon API not the RDP Funds API. The main issues based on my current understanding are:

1. Officially, only Active funds are available in the Eikon API. There are some cases non-active funds got some characters appended to the Lipper ID to support some specific display or applications in Eikon. However, I believe you cannot access the usual data-points from these funds.

2. The size is a concern as you are describing historical data for 20 years. How frequent is that monthly? daily?

3. You need to understand prices (e.g. NAV) cannot be used to derive fund's performance (total returns). Because prices provided by Lipper is kept as the "official" prices reported by the fund companies, not adjusted for distributions, nor taking case the potential unit merge/split events.

Lastly, if you are entitled to access RDP Funds API instead, then what you described should be able to achieved using Python natively (I am not sure about Jupiter notebook/ANACONDA). The main thing using that API is the initial effort to gain access to it.

edoardo.modotti01

Hello b.lee,

thank you for your answer.

Yes, the main point is that I want historical monthly NAV for each fund from 2000 to 2020 using the Lipper ID and yes, I was referring to the Eikon API .

I’m looking for this information as I’m trying to recreate the Carhart work “On persistence in mutual funds”, with different data (European UCITS mutual funds).

1. The problem is that since I would need all the type of funds (still active, merged and dead to avoid survivorship bias problems as I mentioned) I have been told that you can gather those only starting from LipperID to obtain Lipper RIC and, in the end, the NAV for each fund. The “original” Lipper RIC (not that one gathered from the LipperID) have a problem: all funds are indeed recognized by RIC codes which are needed for accessing data through the program API in the Python environment, however’ dead’ funds have this code modified by a suffix and this change is not explicit in the screener on Refinitiv, where funds are still identified by their old RIC code even if inactive, thus downloading directly from there is not possibile. However, starting from the funds’ LipperIDs it is possible to retrieve the correct RIC code using a Python function of the Eikon module.

1. Monthly historical data from 2000 to 2020. Yes it’s quite big

1. NAV is available for both active and dead funds, so it’s the only suitable and yes, the dataset would be free of dividends etc. It would be impossibile to adjust everything.

However during these days I will try to analyse this situation.

After this what do you think?

Thanks

bob.lee

@edoardo.modotti01 , I got your further message in email notification, but I do not see it here. Not sure what happened. If you already raised a case to our customer support helpdesk, please ask them to forward the case to bob.lee@refinitiv.com Or you can contact me directly using that email and send the fund list you got. I can have a better study on the case.