Posts in category: google sheets

Heat maps can help you easily identify high and low values. You’ll often find them to display stock prices to show which stocks did well, and which ones didn’t. They can also make it easy to see whether it was a good or bad day on the markets when looking at a list of stocks. In this post, I’ll show you how to create a heat map in Google Sheets to do this.

Step 1: Populating your list of stocks

To start with, I’m going to need a list of stocks to track. I’m going to use the top 20 most valuable stocks as of today’s date and put them into a list:

Step 2: Calculate the percentage change

Next, I’ll need to setup the percentage change. This can be either the percent change from the previous day, or I could calculate how a stock has done over a specific timeframe, such as a 12-month period. In Google Sheets, you can use the GOOGLEFINANACE function to track the percent change from the previous day. Here’s what that formula would look like, assuming I want to calculate this for the ticker symbol in cell A2:

=GOOGLEFINANCE(A2,”changepct”)

This will return a value to display the percent change.

I’m going to use a more complicated example, however, to show how the stock has performed over the past year. First, I’ll pull in the current stock price, using the following formula:

=GOOGLEFINANCE(A2,”price”)

The trickier part is to pull in the price from a year ago. To go back 365 days, I can set the start date equal to today’s date minus 365 days:

=GOOGLEFINANCE(A2,”price”,TODAY()-365)

The problem is that this returns a table, occupying two rows and columns:

To ensure I’m just pulling in the closing price, I’ll use the INDEX function to grab the value from the second row and second column:

=INDEX(GOOGLEFINANCE(A2,”price”,TODAY()-365),2,2)

Now, to calculate the percent change, I will take the current price and divide it by the historical price:

=GOOGLEFINANCE(A2,”price”)/INDEX(GOOGLEFINANCE(A2,”price”,TODAY()-365),2,2)-1

I add the -1 at the end to get just the percent change. Now, if I format my values in percentages, I can see how the stocks have performed over the past 12 months:

Step 3: Ranking the values

Using the RANK function in Google Sheets, I can easily determine which stocks were the best and worst performers in the range. The following formula just takes the percentage value in column B and compares it against all the values in that column:

=RANK(B2,B:B)

By copying this formula down, I can now see a ranking of all the values:

Step 4: Populate the stocks in order of their performances

Now that the data is setup, I can start arranging the values in order of largest to smallest. To do this, I’m going to use the INDEX function along with the MATCH function to extract the stocks based on their performances. Here is what the first formula will be:

=INDEX($A:$A,MATCH(COLUMN(A1),$C:$C,FALSE),1)

I use the COLUMN function because what I am going to do is drag this formula to the right, so that my largest values go from left to right. And as I copy the formula, A1 will become B1, then C1, and so on. The purpose of this is to increment the function to get the next value. Here is what my table looks like for the first five values:

These were the five best-performing stocks that were in my list. Below these values, I’m going to also pull in the percentages. This is accomplished through the following formula:

=INDEX($B:$B,MATCH(COLUMN(A1),$C:$C,FALSE),1)

Now I have a list of the top ticker symbols and their percentage gains:

Step 5: Creating conditional formatting rules

I have the values setup and next I’ll need to create conditional formatting rules to display different colors based on their relative performances. I’ll use a bright green for the best performance, and gradually show a white color when the values are close to zero, and red when they are negative.

I’m going to setup a table which shows the different thresholds I want to track, so it’s easy to change these conditional formatting rules right on the spreadsheet:

This is my table of values in column K. To setup the rules, I’m going to select the ticker symbols which I ranked in step 4, and create the following conditional formatting rule to start with:

I’m going to repeat these steps for the values in K2, K3, K4, and K5. I’ll adjust the colors to differentiate between the colors ranges I specified earlier. I use the same formula but simply adjust the cell I’m comparing to:

One thing to note here is that the value I’m using as my comparison is in row two, which is where my percentages are. This means for the conditional formatting rules in the first row, Google Sheets is looking the row below, which contains the percentages.

I’ll need to create another set of conditional formatting rules for the actual percentages themselves in row two. In order to avoid disrupting the formulas and the logic for the first row, I’ll need to create these rules from scratch again. It’s important not to just copy the formatting rules as Google Sheets will end up misinterpreting what I want it to do.

After selecting the values in E2:I2, I create the same conditional formatting rules. The one key difference is that this time I’m referencing the values in row two, not the row below. Once that’s setup, you should now see the same conditional formatting rules applied to both rows:

Step 6: Create borders and setup additional formatting

Before I copy over the formulas and conditional formatting to more rows, I’m going to setup additional formatting and borders. I’m going to make the ticker font larger and bold. And I’ll also outline a border for each stock and its percent change. Here’s what my tickers look like after making these changes:

Step 7: Copying the formatting rules and formulas to accommodate more stocks

I created a row of top 5 stocks but I’m going to expand this so that I have four rows of five stocks each, so that I’m capturing all 20 stocks in my list. To do this, I’m going to copy the cells in E1:I2 multiple times:

I’ve copied the formulas but I need to adjust them so that they aren’t all starting from the top-ranking stock all over again. Here’s how I’m going to adjust this. For the formulas in cells E3:E4, I’m going to add five so that they start at the sixth value. This is the updated formula in E3:

=INDEX($A:$A,MATCH(COLUMN(A3)+5,$C:$C,FALSE),1)

Now I’m going to do the same thing for cell E4. Once I’m done, I’ll copy the formulas across and now my second row is updated to show the stocks in the 6th, 7th, 8th, 9th, and 10th positions:

For the next row, I’ll add 10 to the formulas in column E. Then copy those across. Repeat the same process for the next set of rows and add 15, and do the same. Here’s what my updated table looks like:

Now my heat map is setup to show the percent changes. But as you can see, there’s not a lot of variability here. I’m going to change my values in column K as follows: 100%, 60%, 30%, 20%, -100%. I always leave the last one to -100% to ensure it captures everything else. With the updated rules, now my heat map looks as follows:

Although the values in red are not negative, based on the rules I’ve set out, it highlights the lowest-performing stocks in this list as red, and thus, the sheet follows the rules correctly. You can expand this to include more stocks or to track daily changes or other values.

If you like this post on How to Create a Stock Heat Map in Google Sheets, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.

Google Sheets makes it easy to pull in data from the internet, including stock prices. An advantage it has over Excel’s StockHistory function is that it can pull prices even before the trading day has finished. This gives users access to more up-to-date information. Plus, it’s easy to track not just one or two stock prices in Google Sheets but even hundreds.

How to pull in a stock price for a ticker symbol in Google Sheets

Using the GOOGLEFINANCE function, you can quickly pull in a stock price easily. Here are the main components of the function:

• Ticker
• Attribute. Below are the attributes you can use for stocks:
  • “price”: current price, up to 20 minutes delayed.
  • “priceopen”: the opening price.
  • “high”: the current day high.
  • “low”: the current day low.
  • “volume”: the current day’s volume.
  • “marketcap”: the stock’s current market cap.
  • “tradetime”: the time the last trade was made.
  • “datadelay”: how delayed the real-time data is.
  • “volumeavg”: the stock’s average trading volume.
  • “pe”: the price-to-earnings ratio.
  • “eps”: the most recent earnings per share.
  • “high52”: the stock’s 52-week high.
  • “low52′: the stock’s 52-week low.
  • “change”: the change in stock price from the previous day’s close.
  • “changepct”: the percentage change in price from the previous day’s close.
  • “beta”: the stock’s beta value.
  • “closeyest”: the previous day’s closing price.
  • “shares”: the number of shares outstanding.
  • “currency”: the stock’s currency
• Start Date
• End Date
• Interval

You don’t, however, need to fill in all of the arguments. For example, the following formula only uses the ticker and the attribute field and it will pull in Amazon’s current stock price:

=GOOGLEFINANCE(“AMZN”,”price”)

If you want to pull in Amazon’s stock price for the first trading day of the year, you could use the following formula:

=GOOGLEFINANCE(“AMZN”,”price”,”1/1/2024″)

This will return the following table:

Although January 1 was not a trading day, the formula automatically gets the data for the next trading day. If you just want to get the closing price and don’t want the rest of the table, you can nest this formula within the INDEX function as follows:

=INDEX(GOOGLEFINANCE(“AMZN”,”price”,”1/1/2024″),2,2)

Since we are getting the second column and the second row, it will only retrieve the closing price for that day. This method works when you are just pulling in the stock price for a single date.

Adding a prefix for exchanges

If you want to track a lot of stocks, the one thing you may inevitably run into is a situation where Google Sheets doesn’t correctly identify your stock ticker. If, for example, you want to pull in a stock from a different exchange, entering just the ticker symbol alone won’t be enough. If I wanted to pull in the price for Air Canada stock, which has a ticker symbol AC on the Toronto Stock Exchange (TSX), this formula won’t work:

=GOOGLEFINANCE(“AC”,”price”)

Instead, that formula will return the value for Associated Capital Group, which trades on the NYSE. Google Sheets effectively takes its best guess as to which ticker you want to pull in. But as you can imagine, it may get it wrong if you have a symbol which is active on multiple exchanges.

To get around this, you can incorporate an indicator for the exchange. For the TSX, it’s TSE. If you’re not sure which one to use, go to the Google Finance website and look for the stock you want, and take note of the code for the exchange:

To ensure the GOOGLEFINANCE function is retrieving the correct stock, I can adjust my formula as follows:

=GOOGLEFINANCE(“TSE:AC”,”price”)

You can follow the same methodology for other stocks and exchanges.

Creating a template to track hundreds of stocks

To create a template to help you track stocks in Google Sheets, all you really need are a few fields. One for the ticker, one for the exchange, plus one for the stock price. I’ll also add one for the % change. This can help you build out a dashboard.

If I have my tickers in column A and the exchange code in column B, I can combine the values to create a dynamic formula which will update based on those combinations. This way, I can avoid having to hardcode the individual stock tickers. Here’s how that formula would look:

=GOOGLEFINANCE(B2&”:”&A2,”price”)

The key is to combine those values and separate them with a colon in-between, so that the format is exchange:ticker. Now, when I create my template, I can copy that formula down and it will pull in stock prices which aren’t based solely on just the stock ticker:

Let’s extend this a bit further and now also include the percent change from the previous day. If I want to format it as a percentage, I need to make sure I divide the value by 100:

=GOOGLEFINANCE(B2&”:”&A2,”changepct”)/100

And now I can display both the stock price and the percent change from the previous day:

You can copy these formulas down hundreds of rows, making it possible to track as many stocks as you need in Google Sheets.

If you like this post on How to Track Hundreds of Stocks in Google Sheets, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.