Using Tableau & Alteryx to pick stocks during a major recession

It may seem like a cold-hearted thing to do, but I saw the recession resulting from the COVID-19 epidemic as an opportunity. It was March 2020 – the markets were beginning to crash, and incredibly stable companies were plummeting in price. I was one of the incredibly fortunate ones to not have been majorly affected by the virus, so I was in a good position to take advantage of the rough seas. I thought to myself: “A lot of these companies will surely recover… They’re just at a discount right now.”

This turned out to be true, and the market had a mostly V-shaped recovery. Luckily, I was there to buy up a bunch of stocks at the bottom of that trough. This post is about how I decided to choose the stocks I invested in, and the results of the analytics driven process.

The Idea

The main principle behind the method for identifying the stocks I was going to buy was: buy companies that I believe will recover (eventually), and buy the ones that have lost the most in the shortest amount of time. This, to me, made sense. If a company was highly likely to recover, given their strong performance in the past or just overall stability, then it was a lower risk investment. If I targeted those with larger losses, then I stood to make more profit (even if it took a longer amount of time).

Finding the Data

Obviously the first step in a data-driven decision process was to source the data. I did a search for a free API that would allow me to pull stock price histories for ASX listed shares. This proved difficult, particularly the free part. However, I eventually found Alpha Vantage, a free stock API. This allowed me to pull as much info as I needed (with a couple of workarounds to abide by their rate limits).

Specifically, I used the TIME_SERIES_DAILY_ADJUSTED method and pulled histories of all ASX200 listed companies.

Unfortunately this API no longer supports ASX listed companies.

Extracting the Data Using Alteryx

After a bit of fiddling in Postman (to figure out how it all fits in together), I moved in to Alteryx for the main data pulling. First, I created a database (using a local instance of SQL Server) for this project, which contained a table with the same fields as the output of the API.

Then, in Alteryx, I created a workflow that iterated through each ASX200 listed stock and pulled the price history for that stock. After each iteration, it stored the output in the database. Note that there were a few transformations needed during this step, but nothing too overwhelming. I did, however, need to set a ‘wait’ command that stopped for a few seconds before running the next iteration. This was to overcome Alpha Vantage’s rate limitations.

Finding Stocks Using Tableau

Once I had this data, I could then utilise Tableau to visualise it and find my favourite picks.

I used about the 10th of February as Day 0. This was about when it all started to tumble (actually, it was more the 20th or so, but I wanted a bit of buffer). I then plotted the price over time for each stock from this date, and used a table calculation to calculate the price relative to the Day 0. This helped me ensure all of the axes were on the same scale. I put the stocks on a ‘small multiple’ style chart, which helped me see them all at the same time. I added a tooltip to show me the drop relative to different time scales (e.g. YTD, 2 years, 5 years, and 15 years). This also allowed me to see the drop relative to the GFC, which gave me an idea of how this stocks recovers from recessions.

To actually hone in on the stocks that I thought were worth the investment, I applied a few intuition-driven rules:

  • The company must be one that I believe to be strong and stable (based on general knowledge but also level of fluctuation over time)
  • The stock must not have only had success in very recent times
  • The price must be below the rough average from the last year or so (i.e. if it crashed due to COVID-19, but only to a slightly lower level, then I don’t want it)
  • The drop in price must be substantial
  • The price must have dropped, and not increased (e.g. Woolworths, who was improving due to COVID-19. I didn’t want to ride the wave up, I wanted to buy in during a trough)
  • If the company went through the last major recession (GFC), then it must have recovered for the most part (i.e. if it still hadn’t recovered from the last recession, then I want no part in it!)
  • The resulting portfolio should be relatively diverse

The Portfolio

I made a shortlist of about 30-40 stocks, but only picked about 30 of them based on the biggest drop and the best likelihood of recovering.

Here are the stocks I ended up deciding on. I bought them all at around the same time, which seemed like the bottom.

Symbol / CompanyPrice Paid
ADH / Adairs Limited$0.70
ALL / Aristocrat Leisure$20.00
ANZ / ANZ Banking Group$16.00
APT / Afterpay Limited$12.65
BEN / Bendigo and Adelaide Bank$6.16
BHP / BHP Group$29.40
BOQ / Bank of Queensland$4.92
CBA / Commonwealth Bank$69.91
CCL / Coca Cola Amatil$9.88
DMP / Domino’s Pizza Enterprises$51.67
FLT / Flight Centre Travel Group$9.67
JBH / JB Hifi$23.85
MQG / Macquarie Group$89.70
NAB / National Australia Bank$15.90
ORG / Origin Energy$4.82
PLS / Pilbara Minerals$0.65
QAN / Qantas Airways$3.61
REA / REA Group$81.80
S32 / South32 Limited$1.93
SCG / Scentre Group$1.60
SGP / Stockland$2.31
SUN / Suncorp$8.60
SYD / Sydney Airport$6.17
TCL / Transurban$11.14
TLS / Telstra$3.09
URW / Unibail Rodamco Westfield$4.57
VCX / Vicinity Centres$1.05
WBC / Westpac$15.20
WEB / Webjet$2.71
WPL / Woodside Petroleum$18.52

The Results

Overall, I ended up making a handsome profit. I more than tripled my initial investment in a matter of months – and some stocks, like Afterpay (APT), ended up skyrocketing over 1100%. Adairs was similar, increasing from $0.70 to $4.90 (600%).

The Lessons

None, really. Recessions don’t come around very often, and it’s even more rare to be in a position to take advantage of it. This may very well have been a once-in-a-lifetime opportunity for me, and I can accept that. I don’t imagine this strategy to work consistently through a bull market – but you can be sure that I will be investing in stocks during the next recession.

Well, that’s not a great way to end a post – so here’s an inspirational quote to bring us home:

Success is about taking advantage of opportunity

Mike Ditka
 
(Visited 231 times, 1 visits today)

Leave A Comment

Your email address will not be published. Required fields are marked *