Profit statements

ETF Aggregate Profit Margin Indicator in Python | by Luke Hardy | XCode | March 2022

Analyzing overall ETF profitability is not a simple task, but with this simple script and a robust API, it’s relatively easy!

Photo by Tomas Eidsvold on Unsplash

Exchange-traded funds (ETFs) have grown in popularity over the past decade due to their simplicity, cost-effective approach to investing, and the diversification they offer. The buy-and-hold outperformance provided by ETFs over the past decade is truly excellent and hard to match. However, considering the recent events around Ukraine and the global rise in bond yields, the stock market is undergoing a regime shift, which would make stock and ETF pickers more supportive.

Thus, it would be beneficial if we could find a way to easily rate ETFs or compare them at an aggregate level. I propose a method to assess the overall profit margin of an ETF, taking into account the most recent margin and weighting of each constituent in the ETF. Also, to compare each ETF rating on a predefined list with different ETFs. Ultimately, we would be able to easily compare the evolution of profit margins with the latest quarterly statements from different companies. This would be a continuous daily analysis, which a relatively well-informed investor should be able to integrate into his procedures.

The biggest challenge I’ve always had with putting together such indicators is the quality of the data and how to put it together. I have worked with many different APIs, some of them good some not, but lately one of the best is the Financial Modeling Prep API. They provide a wide range of the highest quality financial data at a truly affordable price and I believe they are on for strong growth in the future! In this particular project, we will be using ETF constituent weights and Fundametals stock endpoints.

If you are ultimately satisfied with the data and results of this analysis, know that FMP helped you provide this link with which you can get 25% off their monthly subscription! Do not hesitate to also consult their documentation.

  • Download the latest ETF component weights for a given ETF.
  • Download the fundamental ratios of the last 4 quarters for a given company.
  • Merge the constituent weights and their corresponding latest ratios, in our case this is the operating profit margin.
  • View the latest aggregate operating profit margin for each of the ETFs in the list.

Before you begin, you will need the following:

  • Python: Install version 3.9.7.
  • Jupyter Notebook: Install the individual version of Anaconda for your operating system, it comes with Jupyter already installed.
  • FMP API key: Get it here.

Once this is in place, we can continue with the implementation.

1. First load the necessary packages for the whole project and define the function to get the components of an ETF.

Here, we download the latest constituent data from the ‘SOXX’ ETF, then remove any unnecessary potential symbols, then simply divide the weightPercentage column by 100 to get each constituent’s weight.

2. Download the last 4 quarters of fundamental ratios for a given company.

What we are doing here is downloading the financial ratios for the last 4 quarters of a given company and then selecting only the ratios that are of interest for our analysis.

3. Define a list of ETFs to filter and estimate the overall operating profit margin for each ETF in the list.

The methodology described above follows these steps:

  • Download ETF component weights.
  • Then, for each ETF constituent, download the latest financial ratios, including operating profit margin.
  • Then merge the two databases to obtain each constituent weight and the corresponding operating profit margin, extracted from the latest quarterly financial statement.
  • Finally, multiply the weights of all constituents and their corresponding operating profit margin, then add them up to get the aggregate operating profit margin for the given ETF.

4. View and compare the latest aggregate operating profit margin for each of the listed ETFs.

Our analysis leads to the conclusion that currently “XLE”, the energy sector ETF in SP500, has a truly outstanding aggregate operating profit margin of almost 300% and that “XTN”, the transportation sector has a negative aggregate operating profit margin.

Of course, these estimates would change if not daily, at least weekly, with new information coming from companies’ quarterly financial statements. Thus, an investor should seek to integrate and execute such analysis on a daily basis.

This demo would benefit from a little more development to become a one-run daily scanning solution, but that should be an easy task from now on. Some suggestions to personalize it and take it to the next level:

  • Try using other columns like “NetProfitMargin” or “GrossProfitMargin”
  • Record it daily so that you can observe significant changes.
  • Deploy it on a cloud solution.

I want to emphasize how useful I find the Financial Modeling Prep API and the huge analytical possibilities it offers on its own! I’ve chosen just a few of all the financial ratio columns available and there’s a lot more to explore!

That’s it for this one. Feel free to share your thoughts and suggestions. And if you want to see more content like this, follow me to get the latest updates!

Note that this article does not provide personal investment advice and I am not a qualified licensed investment adviser. All information found here is for entertainment or educational purposes only and should not be construed as personal investment advice.