google sheets Archives - Page 2 of 3

How to Track Hundreds of Stocks in Google Sheets

May 28, 2024

by howtoexcel google sheets investing stocks

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:

Google Finance quote showing the stock ticker and the exchange code.

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:

Stock prices in Google Sheets based on multiple tickers.

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:

Stock prices in Google Sheets based on multiple tickers showing the price and the percent change.

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.

Loop Through Stocks in Google Sheets With a Macro

November 20, 2023

by howtoexcel appscript google sheets investing macros stocks

Google Sheets provides investors with a great way to pull in stock prices, ratios, and all sorts of information related to stocks. Pulling in a stock’s history, for example, can make it easy for you to calculate a stock’s relative strength index, or create a MACD chart. But doing any sort of analysis for multiple stocks at a time isn’t easy. One way around this is to create a macro using Google App script that can automate the process for you and cycle through multiple stocks. Don’t know how to do it? No problem, because below I’ll provide you with a setup and a code that you can use.

First, I’ll go through creating the file from scratch and how it works.

Setting up the template

In this example, I’m going to find the stock’s largest value for a specific period. To start, I’m going to use the GOOGLEFINANCE function to get the stock history going back to Jan. 1, 2020. In the below example, I’ve got the price history for Meta Platforms, aka Facebook:

Stock history in Google Sheets for Meta Platforms.

In cell B1 I’ve put a variable for the ticker symbol. This is to avoid hardcoding anything in the formula. This is important to make the process easy to update. In the macro, I’m going to cycle through ticker symbols. In Cell E2, I also have a formula that grabs the largest value in column B (the closing price):

=MAX(B:B)

However, this is where you can put your own formula or the results of your own calculation. Whether it’s a minimum, a maximum, or some other computation you want to do, you can put the results of that calculation here. This is the cell that will get copied during the macro.

Then, in column G, I have a list of the stocks that I want the macro to cycle through:

As long as it’s a valid ticker symbol that the GOOGLEFINANCE function recognizes, you can enter it in this column. You can expand it as far as you like. However, if the macro goes on for too long then it will eventually time out and stop. If you want to cycle through every stock in the S&P 500, it is possible, but just be aware that you’ll likely have to do it in chunks. When testing it myself, I estimated I could do somewhere in the neighborhood of 200+ stocks in a single run. Once done, I copied the values onto another place on the spreadsheet with the values, and then replaced the stocks in column G with the next batch.

In Cell J1, I also have a variable called tickercount. This is a helper calculation to make the macro efficient. Instead of it having to count the number of stocks in my list, I provide it for the macro — anything to make it run quicker.

The Apps Script Code

Now it’s time for the code to make this all work. To add code to your Google Sheet, select the Extensions menu and select Apps Script

Selecting Apps Script from Google Sheets.

Once in Apps Script, you can setup a new function. You should see the following:

United project in Google Sheets Apps Script.

Here’s the entire code that you can use based on my setup:

function myFunction() {
  
var sht = SpreadsheetApp.getActiveSheet();
var lastrow = sht.getRange("tickercount").getValue();

for (i=1; i<=lastrow;i++) {

  //change ticker
  sht.getRange('B1').setValue(sht.getRange('G' + i).getValue());

  //copy maximum value
  var result = sht.getRange('result').getValue();

  sht.getRange('H' + i).setValue(result);
  

}
}

Here’s a brief explanation of how the code works:

It begins by selecting the active sheet.
It determines the last value based on the ‘tickercount’ named range.
It loops through the values in column G.
It takes the value in column G and pastes it into cell B1 (the ticker variable).
The macro then gets the value from cell E1 (it has a named range called ‘result’)
It pastes the value of the result into column H, to the same row that the stock ticker was on.

If you leave my setup the way it is, what you can do is do any of your desired calculations on another part of the worksheet. As long as it doesn’t interfere with the ticker list or any of the ranges used in the macro, then you’re fine. You can also adjust where the cells are if that makes it easier. For example, you could move the ‘result’ named range from E1 to somewhere else in the spreadsheet. With a named range, you don’t need to worry about updating the cell reference.

Running the macro

A final part of this macro is actually running it. You need a way to trigger it. In my example, I’m using a button. This makes it easy to see what you need to click on for the macro to run. Here’s how you can create a button in Google Sheets and assign a macro to it:

1. Go to Insert and select Drawing

2. Create a shape, add text to it, and whatever colors/formatting you want. Then click Save and Close.

3. Select the button and click on the three dots on the right-hand side, where you will see an option to Assign Script.

4. In the following dialog box, enter the name of your function (don’t include the parentheses). The default function in Apps Script is called myFunction() and if that’s the macro you want to use, then you would just enter myFunction and click on OK.

If everything works, now when you click on your button, the macro will run. Check for any error messages to see if you run into any issues. If you need to edit the button afterwards, right-click on it first so that you don’t accidentally trigger the macro.

One thing to note is that when you run a macro on a Google Sheets file for the first time, you’ll be given a warning about doing so:

Click on Review permissions and select your Google account. You’ll get the next warning, saying that Google hasn’t verified this app and you’ll need to click on Advanced to continue despite the warnings. This is similar to the warnings you encounter in Microsoft Excel when enabling macros. Once you proceed and click on Allow, the macro will proceed to run.

Here’s how it looks in action:

Download my loop macro template

If you’ve gone through this post and run into issues or it is too complicated for you, feel free to download my loop macro template. Since it’ll create a copy for your use, you can modify it however you like to suit your needs.

If you like this post on Loop Through Stocks in Google Sheets With a Macro, 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.

How Much Money Would You Have if You Invested in the S&P 500 10, 20, and 30 Years Ago?

October 10, 2023

by howtoexcel google sheets investing stocks

Do you ever wonder how much of a return on an investment you would have made if you invested money into a stock or major index? In this post, I’ll show you how you can create a template to calculate those returns in Google Sheets. You can also download the one that I’ve made.

Setting up the inputs

To make a template like this versatile and dynamic, it’s important to create cells for inputs so that the values can easily be updated. One cell should be for the investment amount. Another should be for the index or ticker, and the last option should be for the # of years in the past that you want to look back.

In Google Sheets, if you want to lookup the values for the S&P 500, Nasdaq, or Dow Jones, you’ll need to use the following symbols:

Dow Jones: .DJI

Nasdaq: .IXIC

S&P 500: .INX

There is a period before each symbol. Regular stock symbols, such as GOOG for Alphabet are entered normally without any periods. But for an index, you need to add a period before the symbol. And as you can see from the symbols, they aren’t obvious as the S&P 500 uses INX while for the Nasdaq, it’s IXIC. Rather than entering in these symbols, it may be easier create a lookup list, which you can then use in data validation. For example, I have the list of related values posted in E1:F3

A list of googlefinance symbols and their related index.

I can then use this lookup so that the user selects Dow Jones, Nasdaq, or S&P 500 and then the corresponding symbol will populate:

Spreadsheet with a drop-down option to select the index.

To create a drop-down list in Google Sheets, select a cell and click on Data and press Data Validation. From there, you can either manually enter your options, or you can reference a named range. In my example, I’ve referenced a named range called Index, which holds these values.

Creating a drop-down list in Google Sheets.

Next, there’s the field for the # of years you want to look back. This will be used in calculating the stock or index’s previous value. That is the final input that I will use for this template:

Spreadsheet template to track returns with multiple inputs.

Calculating the return

To calculate the return from the investment, we need today’s value and the value from the past. To get the current value is simple and just requires the following formula:

=GOOGLEFINANCE(symbol,”price”)

In my file, I’ve created a named range called symbol which relates to the .INX value in the above screenshot. When no dates are entered, the formula will pull in the latest value for the symbol.

To get the previous value takes a bit more work. The formula will start off the same but I need to adjust the date so that it factors in the number of years I want to go back. To do this, I will use the DATE function and specify the year, month, and date values. Assuming I want the exact same date and only adjust the year, here is how I would adjust the formula:

=DATE(YEAR(TODAY())-yearsback,MONTH(TODAY()),DAY(TODAY())

In this formula, yearsback is the named range relating to the # of years I want to go back. In my example, it is set to 10. By adjusting the year argument in the date function by the number of years I want to go back, that will adjust the year and nothing else. The TODAY function returns the current date and acts as a starting point. For the last argument in the GOOGLEFINANCE function I set the value to 1, since I only want the value from a single day.

=GOOGLEFINANCE(symbol,”price”,date(year(today())-yearsback,month(today()),day(today())),1)

The only issue here is that this formula returns a table with headers. To extract just the value, I need to wrap it within an INDEX function:

=INDEX(GOOGLEFINANCE(symbol,”price”,date(year(today())-yearsback,month(today()),day(today())),1),2,2)

The formula will now grab the second row and second column, which relates to the value I want. Now that I have my current previous values, I can calculate the return. For this calculation, I only need to take the current value, divide it by the previous value, and subtract 1:

=currentvalue/previousvalue-1

Here again, I’m using named ranges to easily refer to those values and so it’s easy to see what I’m referencing. The result of this formula is a % change.

Lastly, I need to calculate the value of the investment today. This involves taking the original investment and multiplying it by 1 plus the return. This formula uses named ranges once more:

=originalinvestment*(pctreturn+1)

Here’s what my spreadsheet looks like now when I calculate what a $10,000 investment in the S&P 500 would be worth 10 years ago today:

Spreadsheet showing what an investment in the past would be worth today.

You can see both the % return as well as the dollar amount of that investment. With the cells highlighted in yellow and a drop-down option, it makes it easy to see the fields that can be adjusted. If you prefer to use this calculation for just stocks, you can do away with the lookup and instead just enter the ticker symbol directly. If you’d like to download my version of the template, you can access a copy of it here.

If you liked this post on How Much Money Would You Have if You Invested in the S&P 500 10, 20, and 30 Years Ago, 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.

How to Get Emails Into Google Sheets

April 4, 2023

by howtoexcel appscript google sheets

Did you know that you can pull in emails from your Gmail account into Google Sheets? This can be useful if you don’t want to open up Gmail and do a search; you can do it right within Google Sheets. You can extract the body, subject, and other attributes. This can make it easy to scan through your messages and potentially parse out data from the body. Below, I’ll share with you the code to do this and how it works. You can also download the template if you don’t want to create it yourself.

Creating the sheet and setting up the variables

You probably don’t want to pull every email into your Google Sheets file. For that reason, it’s important to set up variables that will allow you to do a search. In my template, I’ve got an area to search by the subject and by label, with the named ranges being keysubject, and keylabel, respectively. This is where the search terms go. And this is similar to how you would search within Gmail, searching by both the subject and the label.

The Google Apps Script code

To attach the code to your Google Sheets file, you’ll need to go the Extension tab and select the option for Apps Script

Selecting the Apps Script option in Google Sheets.

From there, you should see a new tab open that gives you an untitled project where you can enter in code:

The function name can remain as default, the key is to copy the code within the curly brackets, { and }. The code that I use for the function to pull in emails is as follows:

var ss = SpreadsheetApp;

var sht = ss.getActiveSheet();

var lastrow = sht.getLastRow();

var k = 6;

var rng = sht.getRange(k,1,lastrow,4);

rng.clearContent();

var emailstring = 'https://mail.google.com/mail/u/0/#inbox/';

var emaillink;

var keysubject = "subject:(" + sht.getRange("keysubject").getValue().toString()+")";

var keylabel = sht.getRange("keylabel").getValue().toString();

var searchquery = GmailApp.search(keylabel + " " + keysubject);

var allthreads = GmailApp.getMessagesForThreads(searchquery);

var emaildate;

var emailsubject;

for (var i=0; i<allthreads.length; i++) {

  var activethread = allthreads[i];

  for (var j=0; j<activethread.length; j++) {

            emaildate = activethread[j].getDate();

            emailsubject = activethread[j].getSubject();

            emailbody = activethread[j].getPlainBody().substring(0,300);

            emailID = activethread[j].getId();

            sht.getRange(k,1).setValue(emaildate);

            sht.getRange(k,2).setValue(emailsubject);

            emaillink = emailstring + emailID

            sht.getRange(k,3).setValue(emaillink);

            sht.getRange(k,4).setValue(emailbody);

          k +=1

  }

}

There are a couple things to note in the code, should you want to change the layout of your file and where you want the data to go.

At the beginning of the code, there is a variable, k. It determines the starting row for the data. In my code, the value is set to 6 because my headers are in row 5. That means row 6 is the starting point for the data. If you want your headers to be in row 10, for example, you’ll want to set the k value to 11, so that it starts on the following row.

Towards the end of the code, you’ll see where the values are being populated. For example, the date of the email is being populated with the following line:

sht.getRange(k,1).setValue(emaildate);

The k variable is specified at the beginning of the code. However, you can change the the column number (1) at this line. Do not change the k value here. If you do, then your data will be overwritten in the same row over and over. This is because in this part of the code the function is doing a loop and it will increment the k value. And so if you want to change it, you need to do it when the k variable is first set up — before the loop.

If, however, you want to change the column that the value is going to, this is the correct place to do so. For example, suppose you don’t want the date going into column A, then you can change the column number. For example, if you want to change it to column B, then you would change (k,1) to (k,2).

If there are certain fields that you don’t want to be populating, then you can also just remove those lines entirely.

For the body of the email, you may want to adjust how much of it gets pulled into the file. Too much text can force your column to get spread out. And if there are line breaks, the row can also get expanded. In my code, I’ve set the limit to the first 300 characters. However, you can change that by adjusting the following line of code:

emailbody = activethread[j].getPlainBody().substring(0,300);

One last note before moving on from this section — remember to save any changes before trying to run the macro again. If you don’t save, then the changes won’t be applied when you run the macro.

Adding a button to trigger the macro

The one thing that you may want to do after adding the code is to create a button on your spreadsheet to trigger it. Otherwise, you’ll need to go to the Apps Script tab and click the run button each time, which isn’t practical.

Instead of doing that, select the Insert button on the Google Sheets file and select Drawing. You’ll have a blank canvas where you can create a button. Here, you can select an option to create a shape and enter text within it. You can apply different colors to also make it stand out. One you’re done designing it, click on Save and close and the button will be on your spreadsheet.

Once it’s within your spreadsheet, you’ll see that there will be three dots off to the right of the button. This is where you can assign your button to the macro that you’ve created. In my example, my function is called getEmails and that’s what I’ll enter when I’m assigning the button to a script;

If you’ve used a different function name, you will need to enter it above, and then click OK. Don’t enter the parentheses, (), which come after the function in Apps Script. Once you’ve assigned the script to the button, you can now click on the button and run the function.

This will only run on the email account you’re logged in on

If you’re like me and you have multiple Gmail accounts, the one thing you need to know is that this will macro will run on the account you’re logged in on; it won’t be able to toggle between different accounts for you.

Download the file

You can set up this file yourself but if you prefer to just use the version I’ve created, you can download a copy of my template here.

If you liked this post on How to Get Emails Into 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.

How to Find Duplicates and Unique Values in Google Sheets

December 6, 2022

by howtoexcel conditional formatting countif formatting google sheets

Duplicate and unique values can be difficult to find in a large data set. In this post, I’ll show you how you can find and highlight duplicate values, as well as how to extract unique values, in Google Sheets. In this example, I’m going to use a list that shows historical World Cup results, including the winners of past years.

Highlighting and finding duplicate values

There is a built-in function in Google Sheets that allows you to filter out unique values. Under the Data menu, there is a section for Data cleanup where you can select the option to Remove duplicates.

Removing duplicate values in Google Sheets.

However, by doing this, you will actually remove duplicates. And if you don’t want to remove data, this could lead to unintended results. If you simply want to find and highlight duplicate values, you’re better off using conditional formatting.

In this data set, I’m going to highlight the duplicate values in the champion field to identify repeat winners. To do this, I can create a conditional formatting rule in Google Sheets to apply formatting when criteria is met. My criteria will be to look at whether a value shows up more than once within a list. The formula utilizes the COUNTIF function:

=COUNTIF(B:B,B1)>1

This formula needs to be added when creating a conditional formatting rule. To set that up, I’ll select the entire column and under that Format menu, click on the option for Conditional formatting. In that section, there will be an option to Add another rule. And under the drop down for Format cells if…, I select the option that says Custom formula is. And in that box, I’ll enter in the above formula:

Creating a conditional formatting rule in Google Sheets.

I’ll leave the default highlighting options, and now it will highlight all the values that show up more than once in column B:

Table with conditional formatting rules applied.

As you can see, there are many repeat winners in this list. If I only wanted to see the winners that only won once, then I would adjust the formula so that it looks for a value of equal to one, as opposed to more than one.

=COUNTIF(B:B,B1)=1

By altering the formula, it will highlight only the values that show up once:

Conditional formatting showing only values that show up once.

You could also go further and make even more specific conditional rules, such as highlighting countries that have won two or more times. Through conditional formatting, you can make your highlight rules as specific as you need them to be.

Extracting and counting unique values

If instead of getting the duplicates you wanted to just get a list of unique values, that’s an even easier process in Google Sheets. Using the UNIQUE function, all you need to do is select your range, and Google Sheets will give you a list of the unique values:

=UNIQUE(B2:B22)

This formula results in the following list:

Using the Unique function in Google Sheets to extract a list of unique values.

There have only been eight countries that have won the World Cup heading into 2022. But suppose you only wanted to count the number of unique winners. For this, you can use the COUNTUNIQUE function, which takes the same range as the argument:

=COUNTUNIQUE(B2:B22)

The above formula returns a value of 8, which is the same if I were to count the number of values from the Unique formula. There’s also the COUNTUNIQUEIFS function that you can deploy which allows you to also apply an IF statement to the CountUnique function. Suppose I wanted to count the number of unique winners after 1980, that formula would be as follows:

=COUNTUNIQUEIFS(B2:B22,A2:A22,">1980")

Column A contains the year and this returns a value of 6, excluding the two countries that only won prior to 1980: England and Uruguay. Using this function, you can apply multiple criteria if you need to.

If you liked this post on How to Find Duplicates and Unique Values 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 us on Twitter and YouTube.

How to Make a Waterfall Chart in Google Sheets

June 28, 2022

by howtoexcel with No Comment charts google sheets waterfall

Waterfall charts are an effective way to display data visually. They are particularly useful if you’re analyzing an income statement and want to see which parts accounted for the bulk of the change in profitability from one period to the next. In this example, I’m going to use Amazon’s first-quarter earnings of 2022, which saw the company’s bottom line fall into the red for the first time since 2015. Using a waterfall chart, we can quickly analyze what were the big drivers behind the drop in profitability — and the results may surprise you.

Step 1: Preparing the data for a waterfall chart

In a waterfall chart, you want to calculate the change in values. To start with, I’ve entered all the main income statement line items from Amazon’s Q1 earnings for 2022 and 2021, side by side:

Amazon's earnings for Q1 2022 and Q1 2021.

I’ve grouped some expenses together for the sake of not having too many items. With waterfall charts, there are a couple of dangers. The first is that your descriptions run too long and it’s hard to display the line items. The second is that you have too many items and your chart needs to become excessively wide to accommodate all the changes.

One thing you’ll notice here is that at the bottom I have the net income (loss) line. This is a summation of the above items to ensure that it correctly ties out to the profit or loss that the company reported. This is an important step to make sure that you’ve entered your data correctly. Expenses should be negative (outflows) while income should be positive (inflows).

The next step is to now calculate the difference between the two periods, which can be done in a change column that takes the current value and subtracts from it the prior period’s value:

Amazon's change in quarterly net income from Q1 2021 to Q1 2022.

At the bottom, I’ve summed up all the changes. These figures are in millions, and so this is a significant $11.951 billion change in net income from a profit of $8.1 billion in the prior-year period to a loss of $3.8 billion.

Now that the data looks correct, the next step is to plot these values on a waterfall chart.

Step 2: Plotting the waterfall chart

To create the chart, I’ll select the data in the change column along with the related headers. From there I can either click on the image of a chart in the menu bar or I can go to the Insert menu and select Chart. If it doesn’t detect which chart I want to use, then I can select the image of waterfall chart from the Chart type drop-down option in the Setup tab:

Selecting a waterfall chart in Google Sheets.

Now it will show this:

The chart looks correct, however there are multiple changes we can make to help this look better.

Step 3: Modifying the waterfall chart

To start with, I’m going to modify the colors. While red makes sense for negatives, I’m going to change the blue to green, to better reflect a positive inflow of cash. This can be done by double-clicking on the chart and in the Chart Editor, going to the Series section, and scrolling to the Positive label. There, I can change the fill color:

Changing the fill color of a waterfall chart in Google Sheets.

This also gives me the option to change the line color and transparency using the opacity percentages. At this point, I’ll remove the legend since the green and red values are sufficient to tell you whether it was a positive or negative change.

The next thing I’ll change is the grey subtotal bar at the end. Ideally, you would have a starting and ending point on the chart to better show where one period started and where the other ended. But by default, the subtotal just adds up the sum of the change. To adjust this, I’m going to add a row to my table above Net Sales, called Q1 2021 Net Income. In the change column, I will simply put the amount, no change. This is what my updated table looks like:

Amazon's change in quarterly net income from Q1 2021 to Q1 2022, starting with the prior-period net income.

If the chart doesn’t automatically update, you may need to update the range. This can be done by double-clicking on the chart and in the Setup section, modifying the range for the Series and/or the X-axis. But the bar charts for the totals still need adjusting. The first one shows green. To fix this, I’ll double-click on the chart to edit it and under the Series section, select the box to Use first value as a subtotal. Now the first bar chart will turn grey.

Changing the subtotals in a Google Sheets chart.

In the same section, I’ll also uncheck the box that says Add subtotal after last value in series. That will remove the last bar chart. Then, I’ll click on the option to Add new subtotal. Select to add it after the last item. By doing this, I can now specify the name of that total, as opposed to just showing ‘Subtotal.’ In this space, I’ll enter Q1 2022 Net Loss.

The only thing left now is to adjust the chart and stretch it out sufficiently so that the labels display horizontally. And I’ll also add a title — this can be done in the Customize section and under the Chart & Axis Titles area. Here is my completed waterfall chart in Google Sheets:

Now, from looking at this, you can see that Amazon was still at a profit until it reached the other income and expenses line. This would still require additional digging to see the reason for the loss, but it would point us in the right direction. And Amazon’s breakdown of these other expense items tells us that it incured a $7.6 billion loss on its investment in Rivian Automotive — the key reason its net profit from a year ago turned into a loss. While other expenses increased, they alone weren’t enough to pull the company into a net loss position.

If you liked this post on How to Make a Waterfall Chart 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 us on Twitter and YouTube.

How to Use Checkboxes in Google Sheets

June 8, 2022

by howtoexcel with No Comment appscript google sheets

Did you know that you can easily add checkboxes to Google Sheets? In this post, I’ll show you how you can do that. Plus, I’ll share a google sheets script that can automatically update other cells when you tick and untick checkboxes in Google Sheets.

Adding checkboxes to Google Sheets

In Google Sheets, all you need to to do add a checkbox to your sheet is to go to the Insert tab and click on the Checkbox button:

Clicking the button will add a checkbox to the active cell. By default it is unchecked, and selecting the cell will show a value of FALSE in the formula bar. When the checkbox is ticked, then the value changes to TRUE.

Using checkboxes to trigger other calculations

Ticking a checkbox or unticking it doesn’t on its own accomplish anything. However, it could trigger another calculation, with the value being used in a formula. For example, suppose you have a checkbox in cell A1. You could create another formula that looks at if the value is TRUE or FALSE (checked vs unchecked):

=if(A1=TRUE,1,0)

In the above formula, if the checkbox is selected, the formula will return a value of 1. Otherwise, it will be 0. This formula could be modified to do a summation or other something more complex.

Using Google Scripts with checkboxes

Another way you can use checkboxes is with a script that runs when they are checked. Suppose for example you had an inventory sheet and wanted to check off when an item was shipped or received. Clicking the checkbox could populate the date when you checked off the box. With a formula, you wouldn’t have that capability since it would always recalculate. But with a script, it could lock in that value every time the checkbox is ticked or unticked.

To create a script in Google Sheets, you need to go to the Extensions menu and select App Script. The following script will look for changes in the 2nd column (Column B) and if a value is set to TRUE, it will populate the date in the 1st column (Column A). If it’s set to FALSE, then it will clear the value in column A:

function onEdit(e) {
  let range=e.range;
  let activeRow = range.getRow();
  let activeColumn = range.getColumn();
  let cellValue = range.getValue();
  let sheet = SpreadsheetApp.getActiveSheet();


    if (activeColumn == 2) {
      if (cellValue == false) {
          sheet.getRange(activeRow,1).clearContent();
      } else {
          sheet.getRange(activeRow,1).setValue(new Date());
      }
    }
}

Copy that code in its entirety as a new function in the app script. Then, click on the Save button. Now you can go into the spreadsheet and try it out. If you want to change any of the columns, you can change either the active column from B (replace the number 2 in the code above) or where the date value gets populated (see the lines of code that reference activeRow,1, which corresponds to the first column, column A).

If you liked this post on How to Use Checkboxes 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 us on Twitter and YouTube.

Here’s How the S&P 500 Has Historically Performed After a Bad January

February 9, 2022

by howtoexcel with No Comment google sheets investing stocks

The stock market is off to a rough start to 2022, with the S&P 500 falling more than 5% in just one month. Using a spreadsheet, we can analyze historical trends and patterns to identify what normally happens after such bad starts. Below, I’ll use data from Google Sheets to pull in historical values and analyze how the index has performed afterward and whether this year is doomed to be a bad year, or if a recovery is likely and if now is a good time to invest in stocks.

Start with downloading the historical data

The first step is to get the S&P 500’s historical values in Google Sheets. This can be done using the GOOGLEFINANCE function. Using the .INX symbol, I can calculate the S&P 500 values going back to the 70s. Here’s a matrix showing the returns over the past 50 years, after applying some conditional formatting to the values:

Historical S&P 500 values in Google Sheets.

Filtering the data

To zero on in just the largest January declines, I can use the Filter by condition option to specify January values where the percent change is less than negative 5%:

That leaves me with the years when the S&P 500 dropped by 5% or more in the first month:

Now that I have a list of the years I’m looking to analyze, I can start creating some charts.

Using charts to summarize the performances

The first visual I’m going to create will look at how the index has performed after January, after those bad starts. To do that, I need to take the year-end values and divide them by the values at the end of January. This tells me how much the index rose or declined in the remaining months. And when grouping those variances, this is what the data shows:

S&P 500 returns after January in years where it declined by more than 5% in the first month.

Of the 7 previous times when the S&P 500 dropped 5% in January, 3 times it would continue to drop in the following months and finish even lower. Only two times would the index rise by more than 10%. I can also average the results, comparing the down years versus the overall average:

Average S&P 500 returns versus those returns in down years.

This tells me that in a year where the S&P 500 typically tanks in the first month, the overall returns from the index are likely to be negative. However, to add a bit more context to this, I’ll look at the individual returns by year and compare them against the 50-year average, which is summarized in this table:

Table showing the S&P 500 returns in years after a bad January versus the overall 50-year average.

By keeping the average column constant, it creates a straight line for the chart and makes it easy to visualize the individual years’ returns and how they compare against it:

S&P 500 annual returns in years where the index dropped by 5% in January versus the 50-year average.

A few of the things that stand out from the data is that in three of the years (2000, 2008, 2009), the markets were either in the midst of a significant crash or recovering from it. It helps put into context some of these returns, suggesting that the other years might indicate more typical returns in a non-crash year. And if that’s the case, investors may expect fairly modest returns this year, possibly negative ones overall. Although it isn’t a large data set, it certainly suggests that the stock market may be facing a down year in 2022.

You can check my calculations in the Google Sheets file I used to create this data.

If you liked this post on the S&P 500’s Historical Returns, 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 us on Twitter and YouTube.

Historical Stock Returns by Month

February 2, 2022

by howtoexcel with No Comment conditional formatting google sheets investing stocks

Have you ever wondered how a stock has typically performed month over month? Using a spreadsheet, you can calculate monthly returns and identify patterns of which months are traditionally strong for a stock, and which ones aren’t. In this example, I’m going to use Google Sheets to pull in stock prices and calculate historical stock returns by month.

Start with pulling in historical stock prices

To get started, I’ll need to extract a stock’s price history. This can be done using Google Sheets’ GOOGLEFINANCE function. The key is in setting a start date that goes far enough back to ensure you get enough historical data to use in your calculations. A good function to use within that is the TODAY() function which ensures you will always be counting backward from today’s date and don’t need to hardcode a date. If I want to go back to 2008, for example, I can set my formula to deduct about 5,000 days.

To pull Amazon’s stock price going back that far, this is what my formula would look like in Google Sheets:

=GOOGLEFINANCE("AMZN","price",TODAY()-5000,TODAY())

Now I have the following values:

Amazon's historical stock price in Google Sheets.

The one problem here is that the date values contain the time, 16:00:00, which represents the 4 pm closing time of the stock market. I only want the actual date since I’m going to be doing a lookup and don’t want to include time. To extract just the date, I can use the DATE() function and use the YEAR(), MONTH() and DAY() functions to refer back to the values in column A. For example:

=DATE(YEAR(A2),MONTH(A2),DAY(A2))

The above formula would give me the date in column A without the time. I’ll add an IF statement at the start so that in case the value in column A is blank, my formula simply won’t compute anything:

=if(A2="","",DATE(YEAR(A2),MONTH(A2),DAY(A2)))

Now I have a table that has just date values without any time:

Google Sheets table with dates showing no time next to share price.

Creating a date matrix

Next, what I’m going to do is create a matrix that has years going vertically and months going across:

A matrix in Google Sheets with months going across and years going down vertically.

I’m going to fill in these values with the stock’s returns in each of those months. The key to making this work is by using the DATEVALUE() function which allows me to enter a date. For example, if I entered the following formula:

=DATEVALUE("Jan 1, 2022")

It would result in the following output:

1/1/2022

In the first cell of my matrix, for the JAN 2021, I’ll combine the month abbreviation (JAN) with the year (2021) and the first day (1). Here’s how that would work if the month name is in cell F1 and the year is in E2:

=DATEVALUE(F$1&" 1, "&$E2)

However, let’s assume I don’t want to pull the first day of the month and instead want to pull the ending month’s value. For that, I can use the EOMONTH() function. And then I would enclose the current formula within that:

=EOMONTH(DATEVALUE(F$1&" 1, "&$E2),0)

The 0 value at the end indicates how many months I want to jump by. And since I just want the end of the current month, I don’t need to jump by any months, which is why I set it to 0. At this point, I have a date, and now I can use this inside of a MATCH() function to find the row that matches this date. Assuming the date values in are column C, here is the formula:

=MATCH(EOMONTH(DATEVALUE(F$1&" 1, "&$E2),0),$C:$C,1)

And lastly, inside of an INDEX function that will return the corresponding price from column B:

=INDEX($B:$B,MATCH(EOMONTH(DATEVALUE(F$1&" 1, "&$E2),0),$C:$C,1),1)

Now the formula will pull in the price for a given month. But if I want the month-over-month return, I need to take the month-end price and divide it by the previous month’s ending value. To get the previous month’s price, I use the same formula except instead of a 0, I’ll enter -1 for the number of months:

=INDEX($B:$B,MATCH(EOMONTH(DATEVALUE(F$1&" 1, "&$E2),-1),$C:$C,1),1)

I’ll combine the two formulas now, taking the current month-end price and dividing it by the previous month’s value and also deduct -1 at the end to adjust for it being a percent-change calculation:

=INDEX($B:$B,MATCH(EOMONTH(DATEVALUE(F$1&" 1, "&$E2),0),$C:$C,1),1)/INDEX($B:$B,MATCH(EOMONTH(DATEVALUE(F$1&" 1, "&$E2),-1),$C:$C,1),1)-1

Now, copying this formula across the entire matrix, I can see the stock’s historical returns by month. I’ve added some conditional formatting to contrast the good months from the bad ones:

Matrix showing monthly returns while also utilizing conditional formatting.

Besides relying on colors, I can also add a win rate % where I can count the times where the return was more than 0% (i.e. a ‘win’) and divide this by the total number of values. In column F, for January, the formula looks like this:

=COUNTIF(F2:F12,">0")/COUNTA(F2:F12)

I’ll also average the returns so that it’s easier to see the best and worst-performing months:

Matrix showing monthly returns, summarized by win rates and averages.

Judging from this, April looks to be the best time to own Amazon’s stock. It normally returns a positive return and its average over the past 11 years has been a gain of just under 8.5%. To re-create this analysis for other stocks, simply change the ticker symbol.

If you liked this post on How to Calculate Historical Stock Returns by Month, 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 us on Twitter and YouTube.

How to Calculate Stock Returns

October 13, 2021

by howtoexcel with No Comment google sheets intermediate investing stocks

In this post, I’ll show you how you can calculate stock returns using Google Sheets. However, you can use a similar approach in Excel by using the STOCKHISTORYFUNCTION.

First thing’s first — let’s pull in the historical data

For this example, I’m going to pull in the S&P 500’s historical values to see how the index has performed both in the past 12 months and over the course of several years.

To do that in Google Sheets, I’m going to use the GOOGLEFINANCE function which allows me to pull in historical prices. To get the values from the S&P 500, the ticker symbol I’m going to use is ‘.INX’ and to get the last year of data, I’m going to set my start date equal to TODAY()-365 and my end date will be TODAY(). Here’s the full formula:

=GOOGLEFINANCE(“.INX”,”price”,today()-365,today())

If you want to go back years, you can go as far back as 1970. For that, it’s easier to just manually enter that using the DATE function:

=GOOGLEFINANCE(“.INX”,”price”,date(1970,1,1),today())

If you don’t want to return 13,000 rows, you can add an argument at the end to set it to ‘Weekly’ prices (the default is daily):

=GOOGLEFINANCE(“.INX”,”price”,date(1970,1,1),today(),”weekly”)

Looking up the correct values

Once you’ve got the data loaded, then what you’ll want to do is enter the dates that you need values for. In this example, I’m going to use the last day of every month. For this, I can use the EOMONTH function. It takes two arguments: the start_date and the number of months. If I want the current month-end date, then I just set the second argument (months) to zero. As for start date, that can just be any date that falls within the month, which I can enclose within a DATE function. Here’s how the formula would look if I want the last day of September 2021:

=EOMONTH(date(2021,9,1),0)

But since I need to adjust this so that I can copy the formula down and have it automatically adjust, I am going to use the ROW function, which will return the current row number. Since I want the values to be increasingly negative as I copy down the formula (e.g. the current month should be 0, the following one -1, then -2, and so on), I will multiply this by a factor of negative 1 and add 1 to the total (to ensure the first value start at zero):

ROW(A1)*-1+1

That replaces the zero value from the earlier formula:

=EOMONTH(date(2021,9,1),ROW(A1)*-1+1)

And now, I can easily copy this formula down and my month-end dates will populate without requiring me to make any manual adjustments along the way:

Next, I’ll do a lookup to get the values. And that’s as simple as a VLOOKUP on my dates, which are in column A with the corresponding values in column B. If you use weekly dates, then be careful not to set the last argument in the VLOOKUP function to false because you’ll end up with errors as the weekly values won’t always fall neatly on the end of the month. Instead, leave the last argument blank or set it to TRUE so that it finds the closest match. Here’s what that looks like:

VLOOKUP formula to pull in prices based on date, in Google Sheets.

All that’s left at this point is to now just calculate the change in value. I can take the new value, divide it by the previous period’s value, and subtract one from it. This will give me a percent change:

Month-over-month percent change in the S&P 500 in Google Sheets.

If I wanted to determine the cumulative % change since my first month-end date, then the old value would always remain the same — it would be the first date in the series. By freezing that cell, I can calculate the cumulative % change:

Cumulative percent change in the S&P 500 in Google Sheets.

If you wanted to pull in the returns by year, you can do the same thing. All that changes is that instead of pulling in the month-end dates you will use the year-end dates. The main difference here is in calculating the different dates. Rather than multiplying by a factor of -1, you’ll need to use -12. And the starting date should be Dec. 1. Here’s how my formula looks like:

=EOMONTH(date(2020,12,1),ROW(A1)*-12+12)

And when I copy that down, it will automatically adjust for each previous year:

Annual percent change in the S&P 500 in Google Sheets.

The one thing you may notice in Google Sheets is that the GOOGLEFINANCE function returns a timestamp for the date. Each day ends at 16:00:00. This can create some unintended results. For example, using the VLOOKUP function, if I use the date 12/31/2020, because it looks for an approximate match, it will actually return the value from 12/30/2020. Unless you add the timestamp, an exact match won’t work. And since a date with no time will by default by 0:00, the lookup of 12/31/2020 16:00:00 won’t be a match. One way to get around this is just to use a different date. Rather than using the EMONTH function, I can just adjust the date by reducing the year by 1. This is the formula I can use if instead I want to get the first day of the year:

=DATE(2021-ROW(A1)+1,1,1)

Using the ROW function again can allow me to automatically adjust the year. Here is the updated table:

If you liked this post on Using Tags in Excel, 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 us on Twitter and YouTube.