Analyzing Equity Trading Strategies with Power BI

BI and Analytics
Technology
21 August 2017

As I noted in my previous blog post relating to analyzing data from Food Network’s Diners, Drive-Ins and Dives, I try to combine my passion for data and analytics with my outside interests. 

Outside of work, I manage some of the investments for my immediate family. As I have grown older, my investments have tended to focus on equities that pay dividends, since I appreciate a steady stream of income and relative lower volatility, compared to other equity categories.

I wouldn’t consider myself an active trader of equities. However, one trading strategy I have often thought to be worth exploring would be to purchase a dividend paying stock on the ex-dividend date (date that you must be stockholder of record in order to receive the dividend) and then sell the stock the next day. A couple of points to consider are: investors who are interested in selling their position may wait until the day after the ex-dividend date so the price is more likely to fall, and second, dividends are typically paid about 30 days after the ex-dividend date so there is the time value of money to reflect. Some key questions I wanted to answer are:

  • Overall, is this a successful trading strategy?
  • Are there specific time-periods when this strategy is successful or unsuccessful?
  • Are there certain equity securities where this strategy performs well or poorly?
  • Does the size the dividend impact this strategy? 

Based on my experience with Power BI, I thought this would be an ideal platform for backtesting this strategy. For those unfamiliar with the term backtesting, a common definition is the process of testing a trading strategy on relevant historical data to ensure its viability before the trader risks any actual capital.

In order to conduct backtesting of this “ex-dividend” trading strategy, I needed a historical data set comprised of applicable equity securities. Since all 30 stocks in the Dow Jones Industrial Average (DJIA) pay dividends and have done so for many years, I thought that this pool of equities would make an ideal candidate.

To gather the data, I needed to find a source that had captured historical closing prices and dividends for the DJIA securities. Fortunately, there are a number of websites that allow you to download historical stock prices. I was able to download a .CSV file, containing ten years of historical DJIA closing prices and dividends from a website called Quandl.

Since the file contained only closing prices, an important assumption I used was that I would purchase the stock at the closing price on the ex-dividend date and sell the stock on the closing price of the next day following the ex-dividend date.

I then proceeded to load the .CSV file to Power BI. Upon reviewing the Power Query preview window in Power BI, I noticed an immediate challenge. The dataset contained one row for each security per day.  Consequently, if I wanted to calculate the difference between the closing price on the ex-dividend date and the closing price on the following day, I needed to either transform the data set in Power Query or use the time intelligence features of Power BI.

Initially, I loaded the data to Power BI and created some DAX (Power BI formula language) measures using time intelligence and the “DateAdd” function. However, I realized that this would be more complicated than I thought, since getting the next day closing price for any ex-dividend date that was on a Friday or prior to a holiday would require a custom calendar. Therefore, I decided to consult with one of my fellow Rightpoint BI & Analytics colleagues, Josh Chung. Josh suggested a better approach might be to manipulate the data set in Power Query.

One of the things I love about Power BI is that there are usually a variety of ways to solve a problem. In this case, Josh was absolutely correct, and in fact, I only needed about 30 minutes to transform the data using Power Query.  Below is a summary of the steps I completed:

  1. Sorted the data by ticker symbol and in ascending order by date. 
  2. Used the Power Query Add Index Column feature and added a new column called “Index Column 0” to add an index starting at 0 and incrementing by 1.
  3. Added another index column called “Index Column 1”, starting at 1 and incrementing by 1.
  4. Used the Merge Queries feature to merge the table with itself where “Index Column 1” = “Index column 0” (bringing in only the next day closing price column).
  5. Now that I had the current closing price and next day closing price on the same row, I just performed some cosmetic changes including adding a company name conditional column based on the stock ticker symbol, making certain the data types were correct, and filtering out rows that did not include a dividend payment.
When I completed the Power Query steps and loaded the data model, I proceeded to add a number of DAX calculations including:
  1. Subtracting the next day closing price from the current closing price.
  2. Reflecting the impact of the dividend payment including discounting the payment using an assumed 12% cost of capital rate.
  3. Calculating an annual return percentage.
  4. Calculating the number of positive and negative quarterly returns.

I then proceeded to create the report shown below by adding slicers, charts, tables and styling, including conditional formatting.

 

Summary Analysis

Getting back to the questions I listed previously, below are my conclusions:

  • Overall, this trading strategy reveals a small net loss with the number of negative quarterly returns slightly exceeding the number of positive quarterly returns.
  • 14 stocks had positive returns with Home Depot being the clear leader with an annual average return of 8%.
  • The second worst performing stock after Nike, was Verizon, which surprisingly has the highest dividend yield (5.38%) among the DJIA stocks. I did not see any correlation between the dividend yield and the average annual return for this strategy.
  • 2008 was the worst performance year (loss of 5.05%), which indicates that this strategy performs poorly during a down market.
  • Expanding the universe of equities to the S&P 500 might be useful and may provide additional insight based on a larger sample population.

In investing, the past is not always a predictor of the future and this blog post is not an investment recommendation but merely an illustration of how Power BI can help you in testing your trading strategies. Good luck with your investments and Power BI!

Let us help you drive data and find insights. Learn more about our Business Intelligence & Analytics capabilities.


Neal Levin is the Vice President of Business Intelligence & Analytics at Rightpoint. Follow Neal on Twitter and LinkedIn.

Loading Next Article