The Problem: Yahoo finance shut down their stock data API in 2017. We can no longer make GET requests for stock information from their API.
Investors, competitors, the media, and even procurement departments follow the earnings calls of publicly traded organizations.
We are looking to automate the retrieval of the upcoming earnings calls for many companies at once instead of looking up each ticker one at a time. That would take too long and the dates/times of the earnings calls are subject to change.
Wouldn’t it be great if we had a simple list of past and upcoming earnings call dates in a simple excel sheet?
We would be able to quantify the companies that typically post earnings early, on time, or late and score them accordingly. It could bring up valid questions about the companies health or commitment to compliance.
Here’s an example of the data we are trying to retrieve, but it’s only one company:
The Solution: Microsoft Excel has a Power Query feature that allows you to get data from a website. To take it one step further, we are invoking a custom function that passes in each individual ticker URL as a parameter.
This means we are making one request to Yahoo for each ticker URL.
This will result in one table for each ticker and then we will stack them like jenga blocks. Refresh the data to often in a short time (make to many requests) and Yahoo will limit your access. Yes, this happened to me.
I will go into the details of how this was built in a future post but for now I will explain how to use it.
Click “Data” in the toolbar and then “Queries & Connections”.
Right-click on “URL_List” then click “Edit”.
On the right hand side under “Applied Steps”, click “Source” and then the cog on the right.
Replace the ticker symbols on the end of the URL with your own. Feel free to add or delete records if needed. I choose the top 15 most followed tickers.
Click “OK” and then “Close & Load”.
Click “Refresh All” in the middle of the toolbar.
The sky is the limit with this list. You can create a dashboard in PowerBI to track upcoming or past earnings calls. Create your own metrics based on tardy earning reports and how often it happens. Or you can filter the list by categories/sectors and get a quick update as the earnings call dates approach. Times/dates are never set in stone and data from Yahoo is not as up to date as the Investor Relations page on a company website.