Overview
When investing in a retirement fund using DCA(Dollar-Cost Averaging) strategy, does it perform better than the inflation rate?
In this project, we will find out what is the past inflation rate in Thailand using the Data Library for Python to consume data from the Workspace Desktop application.
Then we will find out how the DCA on a fund performs and plot a bar chart to compare the average return against inflation.
Introduction to the Data Library for Python
The Data Library for Python provides a set of ease-of-use interfaces offering coders uniform access to the breadth and depth of financial data and services available on the Workspace, RDP, and Real-Time Platforms. The API is designed to provide consistent access through multiple access channels and target both Professional Developers and Financial Coders. Developers can choose to access content from the desktop, through their deployed streaming services, or directly to the cloud. With the Data Library, the same Python code can be used to retrieve data regardless of which access point you choose to connect to the platform.
The Data Library are available in the following programming languages:
For more deep detail regarding the Data Library for Python, please refer to the following articles and tutorials:
Disclaimer
This article is based on Data Library Python versions 2.1.1 using the Desktop Session only.
The Coding part
Step 1: Import Data Library and other libraries
import lseg.data as ld
import pandas as pd
pd.set_option('display.max_rows', 14)
pd.options.display.float_format = '{:,.2f}'.format
The next step is call the Data Library open_session() function to start a session with the Workspace Desktop application. You should have input your Workspace App-Key in a lseg-data.config.json configuration file in the same location as Python application file.
Please note that the LSEG Workspace desktop application integrates the API proxy that acts as an interface between the Data library and the Workspace Platform. For this reason, the Workspace application must be running when you use the Data library with Desktop Session.
ld.open_session()
#example output: <lseg.data.session.Definition object at 0x12117b680d0 {name='workspace'}>
Step 2: Find out the inflation rate.
We use the Data Library get_history() function to get inflation rate historical data.
This function returns data as DataFrame object.
#Overview on Thailand Consumer Price Index in the past 10+ years
CPIs_RIC = ['aTHCPI', #Thailand Consumer Price Index
'aTHCPIBK', #Thailand Bangkok Price Index
'aTHCPIFB', #Food and Non-Alcohol Price Index
'aTHCPIHOSF/C', #Housing and Furnishing Price Index
'aTHHPSDHHE/C', #Single House Price Index
'aTHCPIPMCF/C'] #Personal and Medical Care Price Index
CPIs = ld.get_history(CPIs_RIC,interval='quarterly', start='2015-01-01', end='2025-03-03')
CPIs
To make this DataFrame object easier to read, we rename all columns names. Now we got the price indexes.
CPIs = CPIs.rename(columns={'aTHCPI': 'Thailand',
'aTHCPIBK': 'Bangkok',
'aTHCPIFB': 'Food & Non-Alcohol',
'aTHCPIHOSF/C': 'Housing and Furnishing',
'aTHHPSDHHE/C': 'Single-Detached House',
'aTHCPIPMCF/C':'Personal and Medical Care'})
CPIs
Next, we convert them to percent changes per year.
#Show percent change compare to previous year
#This is actually the inflation
CPIs = CPIs.pct_change()*100
CPIs.dropna(inplace=True)
CPIs
Step 3: Retrieve Fund NAV for the past years.
I am using Fidelity Advantage Pf Fd-Hong Kong Equity-Ord (RIC Code: LP63500988) as an example Fund data.
#Get Fund NAV from RIC
FundRIC = 'LP63500988'
df1 = ld.get_data(FundRIC,['TR.FundNAV.date','TR.FundNAV'],{'SDate':'2015-01-01','EDate':'2025-03-03'})
df1
Step 4: Define your past contribution.
Please note that these are sample numbers which include random increment per year.
contributions = []
contributions.append([pd.Timestamp('2015-01-01'),100])
contributions.append([pd.Timestamp('2015-02-01'),100])
contributions.append([pd.Timestamp('2015-03-01'),100])
contributions.append([pd.Timestamp('2015-04-01'),100])
contributions.append([pd.Timestamp('2015-05-01'),100])
contributions.append([pd.Timestamp('2015-06-01'),100])
...
contributions.append([pd.Timestamp('2025-01-01'),179])
contributions.append([pd.Timestamp('2025-02-01'),179])
contributions.append([pd.Timestamp('2025-03-01'),179])
Step 5: Fill contribution data into NAV DataFrame using the date.
If the date could not be found in NAV DataFrame, add 1 day to the date (until the NAV is found). Then remove all the rows without a contribution.
#Step 5:
#Add Amount Invested column and set its value to 0
df1['AmountInvested'] = 0.0
#Convert Date column from object to datetime64
df1['Date'] = pd.to_datetime(df1['Date'], infer_datetime_format=True)
#Loop through contributions and add Amount Invested to dataframe
#If the NAV of the date contributed could not be found, add 1 day until NAV is found
for contribution in contributions:
searchingDay = contribution[0]
while (len(df1.loc[df1['Date'] == searchingDay, 'AmountInvested'])==0):
searchingDay += pd.DateOffset(1)
df1.loc[df1['Date'] == searchingDay, 'AmountInvested'] = contribution[1]
#Drop any row without contribution from dataframe
df1 = df1[df1["AmountInvested"] > 0]
df1 = df1.reset_index(drop=True)
df1
Step 6: Add calculated data to the DataFrame. They are Total Amount Invested, Unit Invested, Total Unit Invested and Gain/Loss columns.
Once you complete this step, you are able to see how much you gain(or loss). But this still does not give you the idea of how much it grows per year on average.
#Step 6:
# Add Total Amount Invested column and set its value to cumulative summation of Amount Invested
df1['TotalAmountInvested'] = df1['AmountInvested'].cumsum()
#Add Unit Invested column and set its value to Amount Invested / NAV
df1['UnitInvested'] = df1['AmountInvested']/df1['NAV']
#Add Total Unit Invested column and set its value to cumulative summation of Unit Invested
df1['TotalUnitInvested'] = df1['UnitInvested'].cumsum()
#Add Total Value column and set its value to Total Unit Invested * NAV
df1['TotalValue'] = df1['NAV']*df1['TotalUnitInvested']
#Add Absolute Gain or Loss and set its value to Total Value - Total Amount Invested
df1['Gain/Loss'] = df1['TotalValue']-df1['TotalAmountInvested']
df1
Step 7: Get current NAV
#Step 7:
#Get current NAV and calculate Total Unit Invested, Total Value and Absolute Gain/Loss
df2 = ld.get_data(FundRIC,['TR.FundNAV.date','TR.FundNAV'])
df2['Date'] = pd.to_datetime(df2['Date'], infer_datetime_format=True)
df2["AmountInvested"] = 0
df2["TotalAmountInvested"] = df1.tail(1)["TotalAmountInvested"].values[0]
df2["UnitInvested"] = 0
df2["TotalUnitInvested"] = df1.tail(1)["TotalUnitInvested"].values[0]
df2['TotalValue'] = df2['NAV']*df2['TotalUnitInvested']
df2['Gain/Loss'] = df2['TotalValue']-df2['TotalAmountInvested']
df2
Append it to the previous Fund DataFrame object.
df1 = pd.concat([df1, df2], ignore_index = True)
df1
Step 8: Define xirr() function
This XIRR is the internal rate of return for a schedule of cash flows that is not necessarily periodic.
#Step 8:
#Define xirr function
#https://stackoverflow.com/questions/8919718/financial-python-library-that-has-xirr-and-xnpv-function
def xirr(transactions):
years = [(ta[0] - transactions[0][0]).days / 365.0 for ta in transactions]
residual = 1
step = 0.05
guess = 0.05
epsilon = 0.0001
limit = 10000
while abs(residual) > epsilon and limit > 0:
limit -= 1
residual = 0.0
for i, ta in enumerate(transactions):
residual += ta[1] / pow(guess, years[i])
if abs(residual) > epsilon:
if residual > 0:
guess += step
else:
guess -= step
step /= 2.0
return guess-1
Step 9: Prepare data from DataFrame for the xirr() function
#Step 9:
#Extract data from Dataframe and prepare it for xirr function
#For xirr function, invested money is negative
df1['AmountInvested'] = df1['AmountInvested']*-1
#Get Date and amount
tas = df1[['Date','AmountInvested']].values.tolist()
#The current date(last row) value is a positive number and it is the Total Value
tas[-1][1] = df1.tail(1)["TotalValue"].values[0]
We calculate xirr with our data.
AverageReturn = xirr(tas) * 100
print("%.2f" % AverageReturn)
Step 10: Compare between Inflation and Average Return
First, let add "Return" to the CPIs DataFrame.
#Step 10:
#Add Average Return to the dataframe
CPIs['Return'] = AverageReturn
CPIs
Next, plot bar chart to compare the inflation rates and average return.
#Plot Bar Chart to compare inflation and average return
CPIs.plot.bar(figsize=(15,12))
You can see from the bar chart that the average return from the sample DCA investing on "LP63500988" fund usually lost against inflation.
You can try running this Jupyter Notebook sample using your own contribution data and use the actual fund you are DCA investing in.
The final step is closing the Workspace session.
ld.close_session()
References
You can find more detail regarding the Data Library and related technologies for this Notebook from the following resources:
- LSEG Data Library for Python on the LSEG Developer Community website.
- Data Library for Python - Reference Guide
- The Data Library for Python - Quick Reference Guide (Access layer) article.
- Essential Guide to the Data Libraries - Generations of Python library (EDAPI, RDP, RD, LD) article.
- Upgrade from using Eikon Data API to the Data library article.
- Data Library for Python Examples on GitHub repository.
- XIRR function
- XIRR function in Python.
For any question related to this example or Data Library, please use the Developers Community Q&A Forum.
