In Pt I of this tutorial, we demonstrated a quick workflow to automate research for an upcoming IPO. You can find that article here: Part I: Equity Analyst's IPO Workflow with Python & OpenBB. In Pt II, we will walk you through retrieving financial data from open source finance terminal OpenBB directly into your Excel spreadsheets while Python seamlessly integrates with Microsoft Office Excel in the background via Pyxll software library.
Table of Contents
- Required Software for IPO Pipeline
- Installing OpenBB, Pyxll and pyxll-openbb
- OpenBB Login & API Key Settings
- Automating Financial Data Stream to Excel
- Powerpoint Roadshow Presentations
- Summary
Required Software
OpenBB - Finance Terminal: The OpenBB Terminal stands as a pioneering investment research platform, setting itself apart through its Python-powered and open source nature. Functioning as a financial hub, OpenBB seamlessly connects to a multitude of data providers and productivity software offering users access to a wide array of investment research and financial data within their desired computer environment. You can install OpenBB by following our OpenBB tutorial.
Introducing Pyxll - Merger between Python & Excel:
Pyxll-openbb - Pyxll OpenBB Plugin: pyxll-openbb is a Pyxll plugin that automatically reads the functions from openbb to Excel for convenience. Pyxll is a software solution that allows users to program Python inside Excel. Pyxll-openbb automates the communication between OpenBB SDK (Software Development Kit) and Pyxll. We will install pyxll-openbb with a single command below along with the other necessary tools.
Installing Pyxll:
OS Requirement: Please note that to be able to install Pyxll, Microsoft Windows (minimum Windows XP) operating system is required. According to the Pyxll system requirements article MacOS and Linux are not supported. Alternatively, there is still a solution around this limitation. Windows Excel can be run in Mac and Linux via virtual machines such as VirtualBox.
The best approach to installing pyxll is running necessary installers inside a virtual environment. We will walk you through the installation process in this tutorial.
Conda is a great tool for this job and it comes with the miniconda package. If you have followed the OpenBB tutorial, you must have already installed it. You can access these Miniconda Installation links for convenience.
In the OpenBB Tutorial, we discussed the creation of a virtual environment called obb and installing the OpenBB Finance Terminal inside obb.
Now, we’ll install Pyxll. This is a two step process.
-
Install Pyxll Installer via pip first. pip install pyxll
- Once the pyxll module is installed, Run the “pyxll install” command to install Pyxll software on your computer.
Please note for all the components including Pyxll, MS Excel, OpenBB and pyxll-openbb plugin to work seamlessly, you will need to be running on a 64-bit Windows OS with 64-bit Excel and Python applications. If your Excel isn’t 64-bit, and here is how to check if Excel is 64bit or not, you can consider uninstalling the existing suite and reinstalling the 64-bit version to be ready for Pyxll applications.
Alternatively, pyxll install can be run as a python module using the -m flag as below:
python -m pyxll install
After running pyxll install, you will get a number of prompts which are necessary to complete the pyxll installation.
Simply answer the Pyxll installation prompts in 7 simple steps each time pressing the “Enter Key” after your response.
The answers to the questions will be as following:
- n (and the installation package will be downloaded automatically)
- Your name
- Your email address
- y (to subscribe to Pyxll newsletter) or n (to opt-out of the Pyxll newsletter)
- y (to accept the terms and conditions of the software)
- Just press enter here unless you want to change the default installation directory. Otherwise you can type a specific directory on your operating system.
- y (if you have purchased a Pyxll license key - highly recommended for premium features) or n (if you’d like to experiment with the free Pyxll version).
Congratulations! You now have Pyxll installed on your Windows machine. When you open Excel, you will see a new menu on the top ribbon called PyXLL Example Tab. Initially it’s very useful to click “Reload PyXLL” in there as it reloads all the new functions and scripts of Pyxll (Such as after installing the pyxll-openbb plugin, you will need to reload Pyxll so all the new functions can be imported, this is something to keep in mind).
Once you press Reload Pyxll, Excel will reload the whole module again and if you have new scripts or plugins installed, they will be loaded too. If there are any bugs or incompatibility issues then you will likely get an error while Reloading Pyxll.
Installing pyxll-openbb
Now that you have OpenBB & Pyxll installed, we can install the pyxll-openbb plugin as the final step. Enter the command below while you are still inside the same virtual environment (reminder: conda activate obb):
pip install pyxll-openbb
Hint: You can start the Run command using Windows+R buttons. Then enter cmd to start a command prompt in Windows. After that you can enter the commands mentioned and demonstrated above to install the pyxll-openbb plugin.
We now see the OpenBB Tab next to Pyxll Tab. This shows that we have the pyxll-openbb plugin successfully installed and imported as well.
With pyxll-openbb is installed open Excel and if type =obb in any cell you’ll see new Openbb formulas in the suggestions.
OpenBB Login & API Key Settings
Let’s start with the API Keys. Visit https://my.openbb.co/app/hub and click on the first option, “OpenBB Terminal.”
Providing API Keys to OpenBB Terminal Hub
In the new page you will see a menu item named API keys. By clicking this you can enter some of the necessary API keys which will be useful in collecting and aggregating data into Excel via OpenBB & Pyxll.
Financial Modeling Prep (FMP), Federal Reserve Economic Data (FRED) and FinnHub are incredibly useful financial data sources. You can generate free API keys and save them on your OpenBB Hub page’s API Keys section.
Here are the links for generating the API keys mentioned above:
Once you generate the API keys, you can enter them in relevant sections. It will look like this:
Don’t forget to save the keys by clicking the save button at the bottom of the page.
Logging in to pyxll-openbb
Log in to the OpenBB Hub to create the necessary connection between your OpenBB account and the Excel Spreadsheet. We will use the =obb.account.login() function from Pyxll to achieve this. Continue with executing the 3 functions below inside Excel to establish a login session.
We need to log in to OpenBB from inside Excel Spreadsheet using the pyxll-openbb functions. This is a simple step that needs to be taken care of before we can start applying the OpenBB functions to Excel.
Use the =obb.account.login() function for this purpose.
For good measure I also add =obb.account.refresh() in the cell below, in case I need to refresh the login attempt or there’s a glitch.
Finally it’s a good idea to use the =obb.account.save() as well. This comes in handy in the event you experience login problems.
Building an Automated Excel Dashboard for Finance (IPO-focused)
Once all the necessary software packages are installed and after confirming OpenBB functions are available in Excel Spreadsheets via Pyxll, we can start leveraging this technology for specific purposes such as creating specialized financial dashboards and presentations. Here, we’ll automate data flows important to the IPO process and significantly enhance an equity analyst’s IPO experience.
We will start with the Fed Fund Rate which can be useful in reading the status of the overall economy, liquidity in the markets and recently formed trends relative to the bigger economic cycles. It’s a privilege to be able to access so much relevant data from state-of-art open-source systems which simply hasn’t existed before.
1- Fed Rate:
The federal funds rate, or Fed rate, is the interest rate that U.S. banks pay one another to borrow or loan money overnight.
The Federal Reserve raised the target range for the federal funds rate by 25 basis points in July 2023, as of September 2023 the Fed Fund Rate range remains the same at 5.25%-5.5% which are the highest levels since January 2021. This is highly valuable information for an equity analyst which may be overlooked sometimes but the convenience of the tech tools we are using significantly increases efficiency and brings optimization to the equity analyst workflow.
We can quickly convert this data to a line chart to see the big picture and historical evolution of the Fed Fund Rate.
2- Comparable Stock Ratios:
We will use the =obb.stocks.fa.ratios() formula to access numerous ratios concerning a specific comparable stock. This returns an OpenBB object which is really a handle for the data frame. We can then use the =obb.to_dataframe() formula to create a data frame from the data object we previously obtained.
Formulas used below:
- =obb.stocks.fa.ratios()
- =obb.to_dataframe()
Equity analysts involved in evaluating a comparable stock's IPO process rely on a diverse array of financial ratios to provide comprehensive insights into the company's financial health and prospects. Asset Turnover and Inventory Turnover ratios can offer an understanding of the company's operational efficiency and inventory management, crucial for forecasting future growth. The Debt Equity Ratio and Long-Term Debt to Capitalization provide insights into the company's financial leverage, aiding in assessing its risk profile. Cash Flow Coverage Ratios
and the Cash Flow to Debt Ratio offer a glimpse into the company's ability to meet its financial obligations and service debt, essential for potential investors.
Gross Profit Margin and Operating Profit Margin help gauge profitability, while Return on Assets, Return on Capital Employed, and Return on Equity measure overall performance. Moreover, Price-to-Earnings Ratio, Price-to-Sales Ratio, and Price-to-Book Value Ratio aid in valuing the stock and understanding its relative attractiveness in the market. These ratios collectively assist equity analysts in providing a holistic evaluation of the IPO candidate, contributing to well-informed investment decisions and a successful IPO process.
Stock ratios for the comparable stock will automatically return all of these ratios below:
Asset Turnover, Capital Expenditure Coverage Ratio, Cash Conversion Cycle, Cash Flow Coverage Ratios, Cash Flow to Debt Ratio, Cash Per Share, Cash Ratio, Company Equity Multiplier, Current Ratio, Days of Inventory Outstanding, Days of Payables Outstanding, Days of Sales Outstanding, Debt Equity Ratio, Debt Ratio, Dividend Paid and Capex Coverage Ratio, Dividend Payout Ratio, Dividend Yield, EBIT per Revenue, EBT per EBIT, Effective Tax Rate, Enterprise Value Multiple, Fixed Asset Turnover, Free Cash Flow to Operating Cash Flow Ratio, Free Cash Flow Per Share, Gross Profit Margin, Interest Coverage, Inventory Turnover, Long-Term Debt to Capitalization, Net Income per EBT, Net Profit Margin, Operating Cash Flow Per Share, Operating Cash Flow to Sales Ratio, Operating Cycle, Operating Profit Margin, Payables Turnover, Payout Ratio, Period, Pretax Profit Margin, Price-to-Book Value Ratio, Price-to-Cash Flow Ratio, Price-to-Earnings Ratio, Price-to-Earnings to Growth Ratio, Price Fair Value, Price-to-Sales Ratio, Price-to-Book Ratio, Price-to-Free Cash Flows Ratio, Price-to-Operating Cash Flows Ratio, Price-to-Sales Ratio, Quick Ratio, Receivables Turnover, Return on Assets, Return on Capital Employed, Return on Equity, Short-Term Coverage Ratios, Symbol, Total Debt to Capitalization.
3- Comparable Stock Multiples:
Part of the IPO analysis process involves assessing comparable stocks in the IPO process. Metrics, such as Price-to-Earnings (PE) Ratio, Debt-to-Equity Ratio, and Revenue Per Share, enable analysts to gauge the company's valuation, financial health, and growth potential.
- PE Ratio offers a glimpse into the stock's relative valuation compared to its earnings, aiding investors in determining whether the IPO price is reasonable.
- Debt-to-Equity Ratio helps assess the company's leverage and risk profile
- Revenue Per Share provides insights into revenue generation per unit of ownership.
By considering these and other key metrics, equity analysts contribute to informed investment decisions and help companies optimize their IPO strategies, ultimately facilitating a smoother and more successful transition to the public market.
OpenBB allows you to retrieve an extensive array of stock multiples automatically. Here we’re looking at comparable stocks in tech.
We will use the =obb.stocks.multiples() formula. Similarly, we can convert the object derived from multiples formula to data frame with the =obb.to_dataframe formula.
Formulas used below:
- =obb.stocks.multiples()
- =obb.to_dataframe()
Stock multiples will return following values for the provided stock:
Average Inventory (TTM), Average Payables (TTM), Average Receivables (TTM), Book Value Per Share (TTM), Capex Per Share (TTM), Capex to Depreciation (TTM), Capex to Operating Cash Flow (TTM), Capex to Revenue (TTM), Cash Per Share (TTM), Current Ratio (TTM), Days of Inventory on Hand (TTM), Days Payables Outstanding (TTM), Days Sales Outstanding (TTM), Debt to Assets (TTM), Debt to Equity (TTM), Debt to Market Cap (TTM), Dividend Per Share (TTM), Dividend Yield Percentage (TTM), Dividend Yield (TTM), Earnings Yield (TTM), Enterprise Value Over EBITDA (TTM), Enterprise Value (TTM), EV to Free Cash Flow (TTM), EV to Operating Cash Flow (TTM), EV to Sales (TTM), Free Cash Flow Per Share (TTM), Free Cash Flow Yield (TTM), Graham Net Net (TTM), Graham Number (TTM), Income Quality (TTM), Intangibles to Total Assets (TTM), Interest Coverage (TTM), Interest Debt Per Share (TTM), Inventory Turnover (TTM), Invested Capital (TTM), Market Cap (TTM), Net Current Asset Value (TTM), Net Debt to EBITDA (TTM), Net Income Per Share (TTM), Operating Cash Flow Per Share (TTM), Payables Turnover (TTM), Payout Ratio (TTM), PB Ratio (TTM), PE Ratio (TTM), PFCF Ratio (TTM), POCF Ratio (TTM), Price to Sales Ratio (TTM), PTB Ratio (TTM), Receivables Turnover (TTM), Research and Development to Revenue (TTM), Return on Tangible Assets (TTM), Revenue Per Share (TTM), ROE (TTM), ROIC (TTM), Sales General and Administrative to Revenue (TTM), Shareholders Equity Per Share (TTM), Stock-Based Compensation to Revenue (TTM), Tangible Asset Value (TTM), Tangible Book Value Per Share (TTM), Working Capital (TTM).
Retrieving Data from Excel To Powerpoint Presentations for IPO Roadshows
Once we have enough data on our Excel Dashboard, we can export charts and tables to Powerpoint to initiate our IPO’s Roadshow Presentation.
You can create a line chart using Excel’s Insert tab with the Fed Fund Rate data. Then simply copy this chart and paste into the Powerpoint with the third option which “Links” the chart using the original data source.
Similarly, you can create relevant charts and data tables and insert them in the IPO Roadshow Presentation as needed.
Summary
In this article, we explored the powerful combination of Pyxll and OpenBB to streamline the IPO workflow for equity analysts. We began by highlighting the demanding nature of investment banking roles and the benefits of leveraging automation tools in managing the workload. By acquiring financial programming skills, analysts can enhance their efficiency, confidence, and success rates in their careers.
The tutorial introduced the components required for building an IPO Workflow Pipeline, emphasizing the integration of OpenBB's open-source finance terminal with Microsoft Excel through the Pyxll software library. We walked through the installation process for OpenBB, Pyxll, and the pyxll-openbb plugin, ensuring they operate seamlessly on a MS Windows OS with 64-bit Excel and Python.
We then explored the practical application of this technology, starting with the retrieval of financial data such as the Fed Fund Rate, enabling equity analysts to monitor economic trends efficiently. We delved into the utilization of OpenBB's functions to access a wealth of comparable stock ratios and multiples providing valuable insights into a company's financial health and risk profile.
Written by Umut Sagir, MSc Finance
Click here to get started with a free trial of >>>> Pyxll <<<<< and use this coupon (PYFI10) to enjoy 10% off your subscription after you upgrade.
