How to dynamically pull stock data into Google sheets. No coding required!
I recently delved into the exciting and oscillating world of stock and cryptocurrency trading. Though I don’t have any substantial investments in this market yet, I invest time in soaking in as much knowledge as I possibly can within the limited time at my disposal every week. Luckily, resources like seeking Alpha, Google Finance, Reddit, Twitter, and so on, ensure you have the needed insights if you know how to look.
To keep things together, efficient, and mildly automated, I use Google sheets to track my portfolio. I find this really convenient and recommend it if you are struggling with manually updating ticker properties for your trade analysis and insights.
In this piece, I will break down how I built my portfolio tracking spreadsheet using Google sheets. You don’t need any special programming skills to build this, so grab yourself a cup of coffee, find a comfortable spot, read through the step-by-step instructions here, and build yourself a robust quasi-automated stock portfolio tracking system with the almighty Google sheets. It’s good to note that data import into Google sheets could be delayed up to 20mins. In the same vein, since I used the free Finviz screener tool, a lag of 3min is expected.
I have also shared a link to a sample spreadsheet below. You do not need to request permission to edit the sheet, simply make a copy for yourself and edit to suit your own peculiar situation. This is completely free. I will be happy to help if you are struggling with it. So feel free to reach out if there is any way I can help.
Finally, I am not a certified financial advisor and have provided this guide for information purposes only. This does not constitute trading advice in any way.
The Template
I built the spreadsheet for the different sectors of interest — Technology, Oil & Gas, Financial, etc. However, I will share one of the sectors here with few attributes you could possibly utilize in analyzing your positions.
Here is the link to the spreadsheet template. Depending on your strategy, the captured attributes may differ from your measurable market indicators. However, following the general principle presented here, you should be able to populate the attributes important to you using any data source.
Let’s dive in
Source Data
I relied on Finviz and Google Finance as the primary sources of data. Besides the quality of data and ease of scraping on these platforms, Finviz has a great stock screener with about 63 filters you could use free of charge. Note that the same Google sheets functions could be used for your preferred platforms like Yahoo Finance, Seeking Alpha. I am stuck with Finviz for now.
Step 1: Launch Google spreadsheet
Launch a new Google spreadsheet. Remember you can only access this with a Gmail account. Once your new spreadsheet is open, you are ready for the next step.
Step 2: Connect your spreadsheet to Google Finance
Connect the new google spreadsheet to GOOGLEFINANCE, an incredible free resource, that fetches current or historical securities information from Google Finance. If you want more information on Google Finance, check it out here.
I used GOOGLEFINANCE to import some fundamental data like ticker name, price, market cap, earnings per share (eps), and shares. Here is an example of how to do this using TESLA (TSLA) and Microsoft(MSFT).
Syntax: GOOGLEFINANCE (Ticker, [Attribute])
For names, I used “=GOOGLEFINANCE (Ticker, name)” to populate the stock names. Fill in the ticker symbols under the user input for all your stocks, use the formula to get the stock names, and then drag down to cover all your chosen stocks.
Using the Syntax above, import the remaining attributes of your choice. Note that GOOGLEFINANCE function does not support all attributes. You can check out the list of attributes you can import using the GOOGLEFINANCE function here. Check out the example of Walt Disney Co below.
Step 3: Head over to Finviz and extract the required data.
Finviz has an excellent free-to-use stock screener with over 60 filters that can be imported directly into Google sheets. Though Finviz does have a premium service that costs $39.50 per month, the free version gives you access to so much.
To extract data from Finviz, I used the IMPORTHTML function in the Google sheet. The IMPORTHTML function is used to Import data from a table or list within an HTML page.
Here is the Syntax: IMPORTHTML(url, query, index)
The url represents the URL of the page in question. The query is the list or table that contains the desired data and the index, which typically starts from 1, identifies the particular list or table as defined by the HTML source.
For more on how to navigate this function, have a look at this article by Zapier.
Here is how to apply this function using Microsoft (MSFT) as an example.
Head over to Finviz and search for “Microsoft” or “MSFT”. The resulting page shows charts, tables, and other information on Microsoft (MSFT) including this table below.
Assuming you want to import the sales value from the table to your Google spreadsheet and have it updated dynamically, you can use the formula below. Note the row and column numbers are 4 and 2 respectively on table (8).
=INDEX(IMPORTHTML(“http://finviz.com/quote.ashx?t="&B3, “table”, 8), 4, 2)
Using the formula above returns the sales value in the form of *153.28B*.
Sometimes you may need to right-click on the table and expect elements to confirm the table number. At other times, you could employ trial and error to get the right table number.
To remove the asterisks and make my result a little pretty, you could use the substitute function on Google sheets. This function substitutes existing text with new text in a string. In this case, we simply replace “ * “ with nothing “ “.
=SUBSTITUTE( INDEX(IMPORTHTML(“http://finviz.com/quote.ashx?t="&B3, “table”, 8), 4, 2), “*”, “”)
Result : 153.28B
Now, we are getting there. To transform the figure into one anyone could easily work with, you could use the following formula
=IFNA(LEFT(J3,LEN(J3)-1)*CHOOSE(MATCH(RIGHT(J3,1), {“K”,”M”,”B”},0),1000,1000000,1000000000),J3)
This simply transforms your sales number to the form you can work with (in this case, 153.28B to 153,280,000,000.00.
If you went through the steps, you now have a pretty quasi-automated spreadsheet populated with the different attributes of your choice that dynamically updates every 20mins — a good preparatory ground for your trading adventure.
Managing your portfolio has never been this exciting! I’m curious to know what tools you use in managing your stock portfolio?
