In this article we take a closer look at Refinitiv Eikon DAPI and Python and Refinitiv partner xlwings, then observe how they function in a real application.
- There are challenges using financial models that are built in Excel. It’s not always easy to feed live data into Excel-based models, and complex models can be limited by Excel’s capabilities.
- The Eikon DAPI for Python provides real-time and historical data on a wide range of financial instruments. Meanwhile, xlwings enables power users to write Excel macros and functions in Python.
- The Eikon DAPI, xlwings and Excel work together to help data scientists, quants, traders, and developers build more sophisticated models to gain unique, deeper insights more rapidly.
For more data-driven insights in your Inbox, subscribe to the Refinitiv Perspectives weekly newsletter.
Excel is an essential tool for finance professionals. However, it is not without its limitations, and in certain scenarios, a complex Excel model can cause a PC or Mac to run much more slowly.
In Excel, larger datasets and pivot tables can’t always handle complex multi-dimensional analytics. Visual Basic for Applications (VBA) seems like an outdated scripting language that lacks the libraries available in modern languages. As a result, copying and pasting code snippets from the internet is a common workaround.
Version control in Excel is also painful. It’s hard to see how models have changed over time, and unless we save a separate copy before making changes, it’s impossible to go back to an earlier version of a model.
A smart adaptation to Excel is to enhance the spreadsheet tool by using the Refinitiv Eikon DAPI and Python, and also deploy Refinitiv partner xlwings to connect Python and Excel.
Let’s take a closer look at these tools and then observe how they function in a real application.
The Eikon DAPI: Harness Refinitiv APIs to identify stocks matching volatility criteria — relative to market — to meet target risk profile
Eikon DAPI Python library
The Eikon Data API provides access to real-time and historical market data, as well as news headlines, all of which are available in the Eikon platform.
When retrieving market data, the Eikon DAPI supports different instrument identifiers: RIC, ISIN, IMO, PermID, OrgID, and more.
In many cases, pricing is just part of the requirement. The DAPI provides related news and financial statements for many instruments, and for deeper analysis the DAPI can use chain RICs to find related futures and options.
The DAPI and Python can be used to streamline models. To speed up calculations on a PC or Mac, we can pipe DAPI data into Python libraries like NumPy, which is 50 to 100 times faster than VBA for large datasets.
Bridging Excel and Python with xlwings
The Eikon DAPI is central to the solution, but xlwings acts as a two-way bridge between Excel and Python. This allows us to run models and analytics in Python, before piping the results into a prepared worksheet.
This solution delivers the best of both worlds: Faster models, powered by the DAPI, and delivered in a familiar Excel interface. xlwings can even update worksheets in real-time, and combined with the DAPI, it can turn Excel into a powerful reporting engine.
The Eikon DAPI in action
Let’s use the Eikon DAPI, xlwings and Python to solve a real-world problem: Computing value at risk (VaR) with a historical simulation.
We use VaR to help understand our market exposure at a given confidence level and calculate VaR by computing the daily return for each instrument in a portfolio. We use that return to compute the profit or loss, given the capital used to buy the instrument.
Next, let’s sum the profit or loss for all instruments in the portfolio and apply the confidence level percentile to derive the capital risk for any given day.
This historical VaR computation relies on a large amount of historical data. With greater availability of this data, the more accurate the computed VaR will be. It’s worth mentioning that entering data in Excel manually is daunting for large portfolios.
However, using the Eikon DAPI, xlwings and a Python library like NumPy makes generating a VaR computation simple. Once the program is in place, it’s easy to change historical date ranges and portfolio make-up.
To run the computation in Python: Define a portfolio, the capital spend for each instrument, a start and end date for data retrieval and a VaR confidence level, as seen below.
portfolio = ['IBM', 'F', 'AMZN.O']
start_date = '2019-01-01'
end_date = '2020-04-10'
dollarValue = 1000
varConfidence = 95
The portfolio is made up of IBM, Ford, and Amazon. Each instrument is attributed a value of $1,000. We are looking for VaR at 95 percent confidence.
We use the Eikon DAPI to retrieve this historical data:
varDf = ek.get_timeseries(portfolio,
fields='*',
start_date=start_date,
End_date=end_date)
This is all it takes to get all the market data for each instrument in the portfolio. It’s just a matter of computing the daily return and daily profit and loss for each instrument, and summing it at the portfolio level.
When all the raw data is computed, it’s put into a Python dataframe (named ‘varDf’ in the example). Then we derive VaR by using the NumPY ‘percentile’ function.
var = np.percentile(varDf['Portfolio'], 100-varConfidence)
Finally, we can display the data in an Excel spreadsheet for portfolio manager analysis.
wb = xw.Book()
wb.sheets[0]['A1'].value = 'Value at Risk 95%'
wb.sheets[0]['B1'].value = var
wb.sheets[0]['A4'].value = varDf
When we run the program, a new Excel workbook is displayed as follows:
With a few additional lines of Python, it would be possible to read the original portfolio information from a spreadsheet, resulting in a full circle from Excel to Python, through the Eikon DAPI and xlwings, and back to Excel in under a dozen lines of code.
Watch: Introduction to the Eikon Data API
Focusing on Excel’s strengths
While calculating VaR for a large portfolio in Excel can be very time-consuming, we can combine the Eikon DAPI, xlwings and Python to work together to get our VaR into Excel quickly.
Having worked around Excel’s limitations and focused on its strengths, the example described above shows that it’s easy to rerun the model after changing the portfolio make-up or date ranges.
Better still, Python code is easier to maintain than VBA, and it can be checked into version control, so it’s easy to track changes. And by storing the model code in a central repository, there’s no longer any need to have to rely on opaque VBA models running on a single PC or Mac.
Quants, developers and analysts are often focused on trying to get more done in less time. For a complete look at what the Eikon DAPI offers, start by reviewing the technical product documentation, tutorials and API libraries, containing hands-on examples covering multiple use cases.
However, using the Eikon DAPI to solve your own problems is the best way to understand its value. To get started, please contact us for a free Eikon trial (which includes the DAPI).
Watch: How to generate your Eikon Data API App Key in Eikon
