I’ve recently been looking for a tool/service that can help automate the triage process when conducting due diligence on new stocks. Ideally I wanted to build something that used Google Finance API and Google Sheets for the API look-ups then do some Excel Magic (or Google Fu in this case) to give a high level recommendation of if a stock is a good candidate for further due diligence. To my surprise Google Finance API’s only gives you a limited number of parameters to query. I preferred to use Yahoo Finance’s API, yet during my searches I found that this has been discontinued a number of years ago and is no longer publicly available. To my surprise I was lucky enough to find an “Add-on” for Google Sheets (called YAHOOFINANCE – by WebDataHub.com) that interfaced with the Yahoo Finance API to provide the data I was looking for.
Please Note: The YahooFinance Add-on by WebDataHub.com is only free for 7 days, and after that is a paid license for roughly $20/Month. Despite that the add-on is only free for 7 days, the results were beyond what I expected.
To use this you simply enter the stock ticker that you are interested in (in yellow below – in this case Enbridge), and select the Exchange from the drop down, and wait for the data to reload. This was fairly simple to do and only took roughly 2 hours to make and works great on my iphone as well.
To do this first you need to install the “Add-On” for Google Sheets called “YAHOOFINANCE“.
To do this there are 2 ways.
Install the Add-on directly through Google Sheets
To install the Add-on through Google Sheets simple open a new sheet and goto: Add-ons -> Get Add-ons.
Search for YAHOOFINANCE
Select this Add-on and click the “Install” Button as shown below. The next screen will prompt you for your permission for the app to access your Google Sheet Data, Run-Third Party web content in sidebars, connect to an external service, see your email address and any other personal data that is public.
Install the Add-on Through the “Google Workspace Marketplace”
The second way to install this Add-on it by going directly to the “Google Workspace Marketplace” website by going the the following URL:
https://workspace.google.com/u/0/marketplace/app/yahoofinance/881284038348
Once the Add-on has been install let’s begin by opening a new Google Sheet and create a new Worksheet called “General”, and in the first row and column goto”Add-ons” -> “YAHOOFINANCE” -> “Form Builder” and you will see a sidebar show up as shown below, and select the “YAHOOFINANCE()” Formula.
When you click on the “YAHOOFINANCE” Formula, the parameters windows will appear and show you how to use the api query and asking which of these attributes you would like to have returned (there is literally any imaginable attribute you could think of). I suggest to enable them all and then at the bottom select the “True” options for show headers. Once this has been completed click the “Insert =YAHOOFINANCE” button.
This will enter the appropriate query string in the first cell of your spreadsheet and will look something like the below. Don’t worry about this being the data for Apple, we will change that later and make is a dynamic field so you will not have to update the query every time you want to change the stock.
Once this has been done create the following new tabs:
- Quotes
- Statements
- Options
Continue the same process as mentioned above, and for each new tab that was created, import all the YahooFinance data from the appropriate formulas within the “Add-on”. Please Note I did not use the “Statements” Formula as I found this contained duplicate data. Once this is done, you should now have 4 tabs that contain the query data.
Once that has been completed, go back to your first blank worksheet, this should be titled Sheet1 – but you can name this anything you would like such as summary, details, etc… but please not this will be difficult to change later. In cells A1:A5 enter the following:
- Ticker (This is the ticker you would like to look up)
- Exchange (This is the exchange that it is trading on – selected from a drop-down menu)
- Exchange Symbol (This is to resolve the exchange symbol)
- Ticker Lookup (This will concatenate the ticker entered above and the exchange symbol to enable to lookup)
- Company Name (Looks up the full company name
Ticker Field
This is just a plain-text field with nothing fancy. Simply enter your ticker in the B1 cell.
Exchange Field
To create the drop-down menu for this create a new worksheet and name it “xLookups”, and in this tab and in the first row enter “Exchange” in A1 and “Exchange Symbol” in B1.
List the names of all the exchanges you would like to use in column A.
For the Ticker Symbols you will need to goto Yahoo Finance and lookup any stock on the exchange you are interested in. In the example below we will use Enbridge on the Toronto Stock Exchange.
You will notice that after the/quote/ in the URL, you see the ticker “ENB” followed by a period, then “TO” for the exchange. This is the convention that we will also need to use when making the Formula Builder queries dynamic. Complete this for all the exchanges that you are interested in and when you are complete you should have a table like the below in the “xLookups” worksheet.
Exchange Symbol Field
Now go back to the first tab “Sheet1” if you have not renamed it and select cell B2. Once selected right-click on the cell and select “Data Validation” from the bottom of the menu. This is where we will select the data to use for our dropdown. In the pop-up that is displayed, under Criteria select “List from a Range” and in the field beside it select the “xLookup” worksheet and select A2 to A30. This will enable you to add more exchanges in the future without having to change and code. When you click the save button this will create the dropdown arrow in cell B2 on the first tab.
Next we will need to create the vLookup that will find the exchange entered in cell B2 with the appropriate exchange symbol identified in the table created in the xLookup” worksheet. By entering the formula below this will look for the data within cell B2 on the Sheet1 worksheet, in column A2 within the xLookups worksheet, and get the second value which is the exchange symbol we want.
=vlookup(B2,xLookups!A2:B30,2,false)
Ticker Lookup
The Ticker Lookup field simply concatenates the ticker entered in Cell B1 with the exchange symbol from cell B3 in the format needed by Yahoo to query its finance data. To do this enter the following formula in cell B4.
=CONCATENATE(B1,”.”,B3)
Company Name
The purpose of this field is to just get a lookup for the full plain text company name. This data is found in the General worksheet in Cell B2. To enable this lookup, simply enter the below formula into cell B5 on Sheet1. Please note at this time it will probably display Apple as we have not changed the YAHOOFINANCE queries to be dynamic which we will do next.
=General!B2
Making Dynamic Queries
Now that we have a few cells that allow us to create the appropriate ticker/symbol convention for lookups. Now all we need to do is replace the default lookups with the concatenated string in cell B4 on Sheet1. We will need to do this once for each of the 4 worksheets that does the Yahoo Financ e queries. To do this select the first cell on each sheet, and in the formula bar, you will see the YahooFinance Query. To change the formula to include the ticker and symbol from the first sheet we just need to change the hard coded “AAPL” to “Sheet1!B4” as shown below. Be careful of any quotes, or comma’s that may result in an invalid formula. Continue this change for all the other sheets.
Once this has been done go back to your first sheet “Sheet1” and play with the ticker and exchanges to ensure they work and update appropriately. As you change the ticker and symbol on the first sheet this will update the subsequent sheets with all the necessary data and more to help us with our assessment.
Summarizing the Data
The summary of data all depends on the trader/investor and what data points they are interested in. For me I was interested in corporate details such as current stock price, volume, average volume, market cap, beta and enterprise value. This data is all found in the “General” tab and is just a simple reference to pull that data into the appropriate cells on the summary sheet.
See below for example:
Continue to pull in the data and organize as you like. I prefer working in just 2 columns and a long list as this makes it easier to use this while I am on my phone. When you are completed you should have something similar to this.
I took this a step further and also used conditional formatting (shown here as green and red highlights) to indicate a positive or negative data point. As shown below Cell B46 is looking for a profit margin which is defined as green if the value is above 10 percent, and red if below 10 percent.
These numbers are arbitrary and you will need to define what works best for you based on what you are looking for, and the more conditional formatting that you include the easier and quicker it will be to quickly analyze and assess a stock and its financials.
