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
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:
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.
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:
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:
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.
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:
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.
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
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:
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.
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.
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.
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:
I’ll leave the default highlighting options, and now it will highlight all the values that show up more than once in column B:
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:
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:
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.
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:
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:
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:
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:
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:
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.
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.
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.
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:
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:
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:
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:
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:
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.
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.
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:
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:
Creating a date matrix
Next, what I’m going to do is create a matrix that has years going vertically and months going across:
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:
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:
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:
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:
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:
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.
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:
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:
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:
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:
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:
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.
Want to create a dashboard to track the stock market and the latest business-related news? Below, I’ll show you how you can create a stock market dashboard using Excel and Google Sheets to pull in all the data you’ll need. If you’d prefer to just download the file, you can do so here.
Step 1: Compiling the data
You can get stock prices into Excel using the STOCKHISTORY function. However, that isn’t available on older versions of Excel and it also doesn’t pull in the current day’s prices. Using Google Sheets can be more effective for this purpose. Plus, on there, I can pull in business-related news as well.
To start, I’m going to pull in values for the Dow Jones, Nasdaq, and S&P 500. I’ll also download the values of a couple of exchange-traded funds (ETFs) that track healthcare and tech stocks. To get the latest price, you can use the built-in GOOGLEFINANCE function that’s only available on Google Sheets. To get the latest value of the Dow Jones, the following formula will work:
=GOOGLEFINANCE(“.DJI”,”price”)
And to calculate the percentage change:
=GOOGLEFINANCE(“.DJI”,”changepct”)/100
For the Nasdaq, you’ll use “.IXIC” and for the S&P 500 the ticker is “.INX”
For the ETFs, since they aren’t indexes, there is no period beforehand and I reference XLK for tech and XLV for healthcare. In my Google Sheets file, I have a simple layout for the values and their changes that I will later pull into Power Query:
Next, I’ll also download the latest business-related news. Google Sheets has another unique function for this: IMPORTFEED. All you need to do is find an rss feed from a website that you want to pull information from. Not every website has an rss feed but what you can do is just do a Google search for the name of a source and ‘rss’ to see if you can find a link. There are three sources I’m going to use for this dashboard:
In Google Sheets, the top articles from each of those rss feeds will show up, including the title, URL, date created, and even a brief summary:
Now, it’s time to pull all this data into Excel.
Step 2: Loading the data into Excel using Power Query
To import data from Google Sheets into Excel, you need to first share the sheet. While in Google Sheets, go into File -> Share -> Publish to web. Then, you’ll be prompted to select what you want to share. I’ll start with the Markets tab I created and then the News tab:
Copy this URL as you’ll need it to load the data into Power Query. While you’re back in Excel, go under the Data tab and click on the From Web button under the Get & Transform Data section. You’ll be prompted to enter a URL. This is where you’ll paste the link that you copied from Google Sheets:
On the next page, select Table 0 as where you want to extract data from. And if you want to do some cleanup (getting rid of extra columns), you can do so by clicking on the Transform Data button:
To remove any unneeded columns in Power Query, just right-click on a column header and click Remove:
Once you’re done, click on the button to Close & Load if you want the data to be loaded on a new sheet. If you want to control where it gets pasted, then use the drop down and select Close & Load To.
Repeat these steps for the other Google Sheets tab.
In addition, I’m also going to load data from a few other sources:
Top 100 Gainers on Yahoo Finance: https://finance.yahoo.com/gainers/?offset=0&count=100
Top 100 Losers on Yahoo Finance: https://finance.yahoo.com/losers?offset=0&count=100
Upcoming IPOs from IPOScoop: https://www.iposcoop.com/ipo-calendar/
The process for importing these links into the dashboard is the same as for Google Sheets. Go through Power Query, import from web, and paste in the URL plus make any formatting changes necessary. The next step involves putting all this data together in a dashboard.
Step 3: Creating the dashboard
In my spreadsheet, I’ve created two tabs: one that hold all my Power Query downloads (the ‘Data’ tab) and a ‘Dashboard’ tab for where all the information will be displayed.
To make the set up of the dashboard easy to manage, I’m going to change the column width to 10 for everything. To do that, press CTRL+A to select all the cells on the Dashboard tab, then right-click on any of the headers, and there you’ll be able to select column width.
First up, I’m going to get the indexes and market indicators as a starting point. To do this, all I need to do is link to the values and the percentages for the S&P 500, Dow Jones, Nasdaq, Tech, and Healthcare tickers I imported from Google Sheets. By default, I’ll set the formatting for all the cells to be green:
To make this more dynamic, I will add some conditional formatting so that if the percentage change is negative, the corresponding cells will highlight in red. For this, I can select all the cells in green above and create a conditional formatting rule the starts with where the first percentage is (in my spreadsheet, it is cell E6):
=E$6<0
This is a simple rule but by not freezing the column (E) and freezing only the row (6), it can be applied to all the cells above. I can apply a red background color so that if any of the percentages are negative, the cells will highlight accordingly:
For the next part of the dashboard, I will copy over the news stories that were also downloaded from Google Sheets. This time, I’m going to use the HYPERLINK function so that I can not just link to the title but also create a clickable link that will allow me to open the story should I want to open it in my default browser. The function itself is simple and involves just two arguments, one for the actual URL and another for what the text should show up. Since it’s shorter, I’m going with the title. After applying some formatting and copying all three sources, this is what my dashboard looks like:
For the last part of the dashboard, I’m going to pull in the tables from the other data sources (top 100 gainers, losers, and upcoming IPOs). If these are on the Data tab, you can just cut and paste them onto the Dashboard tab. And for each one of the tables, I’m going to create a chart based on the symbol and the percent change.
To do this, select the Symbol column and the % Change columns. Then under the Insert tab in Excel, open up the charts and select Treemap. If you selected too many columns or didn’t specify which ones you wanted, you might get a different look. But if you only selected those two, you should see something like this:
Since the chart includes the symbols, the legend can be deleted. Also, I’m going to change the color scheme so that it goes from dark green to light green. This change can be made by clicking the Change Colors button next to the chart:
To add the percentage to each of the boxes, right-click on one of the ticker symbols and click Format Labels. Then, check off the box for value so that the percentages will also show up next to the symbols:
These steps can be repeated for the other charts. However, for the losers table, since the percentage change is negative, it needs to be flipped to positive first. To do that, that query needs to be edited. If you click on Queries & Connections section under the Data tab, you’ll see a list of all your queries. Click on the one that takes you to the top losers query. Right-click edit and Power Query will open up.
Once in Power Query, select the % Change column and under the Transform column at the top, click on the Standard drop down, which will show you all the different calculations you can apply:
Click on Multiply and then for the value in the next box, enter -1. Pressing OK will then flip all the values to negatives.
Now, you can create the same Treemap chart for this table. For the IPOScoop download, the field I’m going to use is Est. $ Volume. This query will also need to be edited in order to use that field since it is text. Although it is a bit more complex since this field contains text and dollar signs, there’s a relatively easy way to parse out what you need.
In Power Query, select the column, and under the Add Column tab, click on the Column From Examples button (choose the option for From Selection):
That will create a new column:
In Column1, I can enter the value that I want Power Query to extract. If I just enter a few values to show what I want (in this case, I only need to enter 300), Power Query fills in the rest, figuring out what I am trying to do. It’s an easy way to parse data in Power Query.
After creating the new column, I can change the format from text to currency by clicking on the ‘abc’ letters in the title:
Now that I have the column created, I can remove the original one and load the data back into Excel and proceed with making a Treemap for this chart using the symbol and the newly created column.
The last thing I’m going to do is create a new column to show the change in volume to determine how much more (or less) trading there was for each stock on the day compared to the average. This will compare the average three-month volume with the current day’s volume. The one complication is that some of the values contain letters:
To convert these values, it’s important to first parse out the letters. If a value doesn’t contain a letter, then it is in thousands. I’m going to set everything to millions. So if the value doesn’t contain a letter, it will be multiplied by 0.000001 to convert it into a fraction of a million. And if it contains a ‘B’, it will multiply by a factor of 1,000. Otherwise, the value will remain as is. Here’s how the first part of the formula will look like, which involves determining the multiplication factor:
Since the letter is always at the end of the string, just using the RIGHT function (which looks at the right-most string) will suffice. This result needs to be multiplied by the remaining value. That value can be extracted by using the SUBSTITUTE function which will replace one value with another:
SUBSTITUTE([@Volume],”B”,””)
In the above formula, the value of B will be replaced with an empty string. This is the same as simply removing the value. To ensure that any ‘M’s are also removed, I will embed this formula within another one that will substitute out those values:
SUBSTITUTE(SUBSTITUTE([@Volume],”B”,””),”M”,””)
I multiply this by the first part of the formula, and my numerator is as follows:
For the denominator, I’m going to use the exact same formula, except instead of the current volume, I’m going to use the field for the three-month average:
The -1 at the end is to put the change in a percentage of less than 100%.
Another step you might consider at this point to help identify these changes is to format these numbers so they are easier to read. You can use conditional formatting (color scales) to easily highlight the highs and lows. And if you want to format the percentages so that they show commas and negative percentages show up red, use the following in the custom number format:
#,##0%;[Red]#,##0%
The semi-colon before the [Red] separates out what the percentages should look like when they are positive (the part before the semi-colon) and what they should like when negative (the part that comes afterward). The [Red] text indicates the value should be in red text.
Here’s how this section looks as part of my dashboard:
And here’s a snapshot of the dashboard as a whole.
One thing to remember: if you want to update the queries and the dashboard, make sure you go under the Data tab and click the Refresh All button. Otherwise, your data may not be up to date.
Also, to prevent your tables from stretching out when updating the queries, select each one of them and under the Table Design tab, click the Properties button (under the External Table Data section), where you should see this:
Make sure the Adjust column width checkbox is unticked. This will prevent your columns from stretching out and disrupting your layout.
If you liked this post on Creating a Stock Market Dashboard 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.
A big advantage of using Google Sheets is that the data is readily accessible online and you don’t need to worry about if people are running different versions of it like you would with Excel. One of the areas where it may be lacking is in creating dashboards. Although you can incorporate slicers, they’re not as user-friendly or nice looking as what you would get in Excel. But in this post, I’ll go over how to make dashboards in Google Sheets quickly and easily.
Here is a sample of what my data set looks like. If you want to view the data plus the dashboard I created here, you can check out the Google Sheets file here.
Step one is to create some pivot tables. Like with Excel, I prefer to create a pivot table for each view that I want. I will set up four pivot tables, categorizing sales by:
Store
Salesperson
Product
Date
To keep things simple, you can put each one of those fields in the ROW section while the sales can be in the VALUES section:
When creating the pivot tables, be sure to un-check the option to Show totals (this is so that they don’t show up in the charts):
What you may want to do is create one pivot table and then copy and paste others, and just change the rows. One additional step you will need to do for the pivot table that contains the dates is to also group them by month. To do that, right-click on any of the dates and select Create Pivot Date Group:
Then, from the following menu, select Year-Month:
This is how your pivot tables might look like once you are done:
Where you put these pivot tables isn’t important. The key is leaving enough space between them so that they don’t potentially overlap should your data get bigger. Otherwise, you will run into errors and have difficulty updating your data. Since my pivot tables won’t get any wider based on the selections I’ll make, there doesn’t need to be any extra columns between any of them.
Now that the pivot tables are set up, the next step is to set up the different charts for each of them. For the sales by store, I will create a pie chart to show the split among the stores:
The one thing you will want to pay attention to for each chart is the range. Since your pivot table could expand, it’s a good idea to make the range bigger than it needs to be, even if it will contain blank values. For example, changing this:
To this:
This will ensure that additional data gets picked up by the chart should your pivot table get bigger. This is also why it is important to ensure you don’t place any other pivot tables below one another. Ideally, you’ll want to keep them side by side rather one on top of the other.
For the pivot table that shows sales by salesperson, I’ll use a bar chart since the names can be long:
For the product sales, I’ll mix it up and have those as column charts:
And for the sales by date, I will set those up as a line chart:
I will also add a scorecard chart, using any of the pivot tables. For this, I just want to pull the total sales:
Now that these charts all set up, it’s just a matter of organizing how you want to see them on your worksheet:
The one thing missing to make this dynamic: slicers. To add slicers to all these pivot tables, click on any of them and click on the Data tab and select the Add a Slicer button:
Then, select the columns you want to filter by:
As long as you are referencing the correct data range, then the slicer will apply to all the pivot tables correctly. And now, if I add a slicer for the stores and only select stores A and B, my dashboard updates as follows:
One thing to remember when you are applying changes: don’t forget to click on the green OK button on the bottom, otherwise your selections won’t be applied:
If you liked this post on How to Make Dashboards 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.
