GoogleSheetsHeatMap

How to Create a Stock Heat Map in Google Sheets

by google sheetsinvestingstocks

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

Step 1: Populating your list of stocks

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

List of the 20 most valuable stocks in Google Sheets.

Step 2: Calculate the percentage change

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

=GOOGLEFINANCE(A2,”changepct”)

This will return a value to display the percent change.

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

=GOOGLEFINANCE(A2,”price”)

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

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

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

Apple's stock price a year ago.

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

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

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

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

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

Google sheets chart showing stock performance.

Step 3: Ranking the values

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

=RANK(B2,B:B)

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

Google sheets showing stock performance and ranking.

Step 4: Populate the stocks in order of their performances

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

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

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

Top-performing stocks listed in Google Sheets.

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

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

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

Top-performing stocks listed in Google Sheets along with their changes in value from a year ago.

Step 5: Creating conditional formatting rules

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

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

Table of conditional formatting thresholds in Google Sheets.

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

Setting up a conditional formatting rule in Google Sheets.

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

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

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

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

Heat map conditional formatting rules setup in Google Sheets for multiple stocks.

Step 6: Create borders and setup additional formatting

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

Heat map conditional formatting rules setup in Google Sheets for multiple stocks.

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

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

Heat map conditional formatting rules setup in Google Sheets for 20 stocks.

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

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

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

Heat map conditional formatting rules setup in Google Sheets for 10 stocks.

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

Heat map conditional formatting rules setup in Google Sheets for 20 stocks.

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

Heat map conditional formatting rules setup in Google Sheets for 20 stocks, showing red highlights for the poorest-performing stocks.

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

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

TTMCalculation

Calculate Trailing 12 Month (TTM) Values in Excel

by financial analysisinvestingUncategorized

Calculating trailing-12 month values, also known as TTM, can be a powerful way to analyze financial or business data over the most recent year. This method focuses on the latest 12-month period, providing a rolling snapshot of performance or trends. Unlike calendar-based analysis, which adheres strictly to yearly or quarterly periods, TTM values are dynamic and update with each new data point. This makes them ideal for ongoing monitoring, where understanding recent trends is more relevant than sticking to predefined reporting periods.

The concept of a TTM value is straightforward: it involves summing, averaging, or otherwise aggregating data from the most recent 12 months. For instance, if you’re tracking monthly revenue, a TTM sum would include the total revenue for the latest 12 months, updating automatically as new months’ data becomes available. This ensures that you’re always working with the most current information, offering a more flexible and actionable view of performance.

Why Are 12-Month Trailing Values Useful?

Calculating TTM values is valuable in contexts where trends or seasonality play a significant role. For example, retail businesses may experience seasonal spikes during the holiday season, while other industries might have cyclical patterns tied to the economy. A TTM analysis smooths out these seasonal fluctuations, offering a clearer picture of overall performance without being skewed by short-term anomalies.

For investors, financial analysts, and business owners, this calculation can be a crucial metric. It helps in understanding key financial ratios (including price-to-earnings ratios), by providing a consistent timeframe for comparison. It also allows for benchmarking against competitors or industry averages, as trailing metrics are widely used in reporting and valuation. Moreover, it can aid in spotting trends early, such as declining sales or increasing costs, enabling proactive decision-making.

Calculating TTM Values in Excel

In the following example, I have sales data by quarter. Since they are quarters, I only need to pull the last four values to get the last 12 months worth of values.

Revenue by quarter.

In the simplest approach, you can just use the SUM function and grab the last four values from the top:

Using the SUM function in Excel to calculate trailing-12 month sales values.

By not freezing any cells and copying the formula down, it will automatically adjust so that it’s always getting the most recent four values.

You can make the formula more dynamic by using the OFFSET function. You can change the number of values you want to add up. And it can be useful if your data is at the bottom, and you want to start from the last value you input. Here’s how you can use a variable to determine how many trailing values you want to calculate.

Using a variable along with the OFFSET function to calculate TTM values in Excel.

Using the OFFSET function, you can specify the height and width of the range. In the above example, I’m using cell F1 to specify the number of periods I want to sum up.

If your most recent values are at the bottom of your range, the OFFSET function can help you with this as well. Here’s how the formula would look like:

=SUM(OFFSET(B2,COUNTA(B:B)-2,0,-4,1))

B2 is the starting reference point.

The COUNTA function counts the number of nonblank cells in column B. It is reduced by 2 since the reference point, B2, is in row 2. It needs to be reduced by 2 to get back to 0.

There are 0 columns to offset, hence the next argument is 0.

The -4 tells the formula that you want to go back 4 rows.

The 1 at the end tells the formula that it is just 1 column wide.

This produces an array, which is then summed up through the SUM function.

If you like this post on How to Calculate Trailing 12 Month (TTM) Values 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 me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.

TrackStocksinGoogleSheets v1

How to Track Hundreds of Stocks in Google Sheets

by google sheetsinvestingstocks

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:

Amazon's stock price for Jan. 2, 2024.

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.

GetFinancials2

How to Download Financial Statements Into Excel and Google Sheets

by add-insinvestingstocks

In a previous post, I went over how to download a company’s financial statements from the Wall Street Journal’s website. However, that connection appears to now be closed. One of the risks when using Power Query to download data from a website that the connection will always be there. But there is another way to get financial statement data, and it can allow you to download much more than what was available through the Wall Street Journal.

You’ll need to setup an account with Alpha Vantage

The website that you can use is Alpha Vantage. It provides API access which you can use to download financial data. There is a free account but there is a limit to the number of requests you can make every day — up to 25. But with the wealth of information you can get with just a single query, there’s a lot of data you can accumulate.

Once you sign up for an account with Alpha Vantage, you’ll have an API key that you can use to connect to its database. You’ll need to save that key to download the data.

Use the site’s custom Excel add-in

Once you have the API key, you can start downloading data. But rather than creating your own template or even using Power Query, what you can do is download the sample Excel files that are available on the site on the spreadsheets page. Here you can select to download the Office 365 add-on, which also includes sample Excel files that can get you started in seconds.

There is a template called FundamentalData.xlsx which contains a file that’s ready to go to import the financials. When you first open it, you’ll need to select the AlphaVantage(Web) tab and click on the Open Taskpane command.

The alphavantage tab in Excel.

From there, you’ll see an option to input your API Key.

The alphavantage API key section.

Then, on the filings tab, you’ll see an area where you can specify the ticker symbol you want, the type of filing (cash flow, income statement, or balance sheet) and the reporting frequency (quarterly versus annual). Then, as you make your selections, the data on your spreadsheet will update with various financial metrics.

Downloading financial statement data in Excel.

Using Alpha Vantage is one of the better options for investors today who want to download financial data into Excel.

Importing financial statement data in Google Sheets

The company also has a Google Sheets add-on available from the Google Workspace Marketplace, just go to Extensions ->Add-ons -> Get add-ons. Then search for ‘Alpha Vantage’ and download the add-on:

The Alpha Vantage add-on in Google Sheets.

Once installed, go back to the Extensions menu, select Alpha Vantage Market Data and select Enter API Key, where you can paste your API key into. Once that’s done, you can use formulas to pull in financials. The following pulls in the quarterly income statement data for MSFT stock:

=AVGetCompanyFilings(“MSFT”,”IncomeStatement”,”Quarterly”)

For a full breakdown of what you can download on Google Sheets, refer to the documentation on the Alpha Vantage website.

Download the data using Power Query

If you prefer not to install an add-in, then you can still download the data from Alpha Vantage using Power Query. You can refer to the documentation for the various links to pull financial data. For the income statement, for example, this is the following url:

https://www.alphavantage.co/query?function=INCOME_STATEMENT&symbol=TICKER&apikey=DEMO

Where Ticker is the stock symbol and Demo is your API Key. To generate the data in Power Query, use the Get Data option and select From Web and paste the URL into there:

Using Power Query to download financial statement data.

Then, once Power Query is loaded up you have the option to specify whether you want the list for the annual reports or the quarterly reports.

Selecting the type of financial report to download from Power Query.

If I select the quarterlyReports list, I’ll have another list of records. I can expand this by clicking on the Convert To Table button in Power Query:

Converting a list of records in Power Query into table.

This will put everything back into a single column. This time, however, I can expand all the fields out by clicking the two arrows going in opposing directions.

Expanding a column in Power Query to list all the fields.

Now my data looks complete:

Income statement data loaded in Power Query.

And this is what it looks like once it’s loaded back into Excel:

Income statement data loaded into Excel.

If you like this post on How to Import Financial Statements Into Excel and 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.

MovingAveragesinExcel

What Is the Difference Between a Moving Average and an Exponential Moving Average

by averageinvestingstocks

Moving averages can be useful in data analysis, when looking at trends both in finance and in the stock market. You can look at 30, 60, 90 day trends, and even longer or shorter durations. There’s also a difference between whether you are looking at a simple moving average and an exponential moving average. In this article, I’ll go over the differences between the two, and show you how you can calculate them in Excel.

How to Calculate a Moving Average (MA) in Excel

A moving average is a simple tool used by investors and traders to smooth out price data over a specified period. It is called “moving” because it is continually recalculated based on the latest data, providing a dynamic view of an asset’s average price over time. The advantage of an MA is its simplicity as it can easily be calculated.

A moving average is calculated by simply taking the average of the trailing periods. In the case of a 60-day MA, you would look at the average over the past 60 days. If it’s a 90-day MA, then you average the past 90 days. In the following example, I have the price of Bitcoin over the past few years. Ideally, when setting up moving averages, you want your dates in ascending order, going from oldest to newest.

Price of Bitcoin in an Excel spreadsheet.

Here are the steps to calculate the moving average:

  1. Determine the number of periods you want to go back. For 5 days, it will be 5, for 10 days it will be 10, and so on.
  2. Calculate the average in the adjacent column. Make sure you do not freeze cells.
  3. Copy the formula down so that the average moves (hence why you do not want to freeze cells).

Here is what the values look like, along with the formula for each cell:

20-day moving average of Bitcoin in an Excel spreadsheet.

The average is continuously moving with each cell, but it always contains a range of 20 values since the 20-day MA contains 20 days. Oftentimes, people using multiple moving averages as a way to identify crossovers, such as when stocks cross 20-day MAs and 50-day MAs. Depending on the direction of the crossover, it can be a very bullish indicator (20-day MA crosses from underneath) or a very bearish indicator (20-day MA crosses from above). This is what those moving averages look like for Bitcoin and how they appear on a chart:

A bullish crossover involving Bitcoin's 20-day moving average and its 50-day moving average.

In this example, the 20-day MA made a bullish crossover recently, going higher than the 50-day MA. This is a very bullish trend. However, with simple moving averages, these trends can take a while to develop, and that is one of the drawbacks of using them — they are slower to react to recent price movements.

How to Calculate an Exponential Moving Average (EMA) in Excel

The exponential moving average (EMA) gives more weight to recent prices, making it more responsive to new information, and thus, there’s less of a lag effect; changes and crossovers can occur much more rapidly. This characteristic makes the EMA a preferred choice for many traders, especially those looking to capitalize on short-term trends.

Here’s how to calculate an exponential moving average in Excel:

  1. Determine the number of periods, as you did with the simple moving average.
  2. Calculate a multiplier, using the formula 2 / (period +1). In the case of a 20-day MA, the multiplier would be 0.095, which is 2/(20+1).
  3. Calculate the moving average for the first period. The very first period needs to be a simple moving average.
  4. For every value afterwards, you’ll use the following formula: =Multiplier x (Current Price – Previous EMA) + Previous EMA.

Here’s how this would be calculated with the price of Bitcoin, as in the previous example:

Calculating Bitcoin's exponential moving average in Excel.

After the initial moving average, the subsequent averages are calculated using the weighting. Here’s a side-by-side comparison of how the 50-day EMA compares with the 50-day MA. I’m using 50-day averages here since they are normally slower to see movements in. But by using an EMA, that can help expedite trends.

Comparing Bitcoin's 50-day exponential moving average against its 50-day simple moving average.

The 50-day EMA makes quicker, more rapid movements and is changing more frequently while the 50-day MA is smoother and more gradual in its changes. With Bitcoin’s price rising rapidly in recent weeks, that uptrend is observed more immediately with the EMA than with the simple MA.

Which Should You Use: MA or EMA?

While both MAs and EMAs provide valuable insights into market trends, the choice between them depends on the specific needs of the trader or analyst. MAs are best suited for identifying long-term trends, as they smooth out price fluctuations evenly. In contrast, EMAs are ideal for those looking to react quickly to recent price changes due to their emphasis on newer data.

By understanding the differences between these two types of averages and knowing how to calculate them in Excel, investors and analysts can better tailor their strategies to suit their goals. Whether it’s the simplicity and broad trend identification of the MA or the responsiveness of the EMA to new information, both tools can be useful.

If you liked this post on What Is the Difference Between a Moving Average and an Exponential Moving Average, 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.

StockTradingTemplate2024

2024 Stock Trading Template

by investingstockstemplate

Do you buy stocks? Help track your transactions and your returns with this stock trading template. This is an update over the previous stock trading template, offering more reports and analysis, and the ability to more easily track your portfolio’s performance. In this post, I’ll go over how the template works, and how to use it.

Using the stock trading template for the first time

When you first use the 2024 stock trading template, there are a few items you may wish to modify on the Settings tab. These are the end date (by default set to today) and the short-term trading days cutoff. The end date is used because the reporting tab looks at the trailing 12 months. So if you want a cutoff as of Dec. 31, 2023 and see the full year, you can modify the cutoff there. But if you want to look at your returns up until today, you can leave that as is, up to today’s date.

The short-term trading days cutoff is used for a report which pulls your gains and losses over this number of days. If you’re a very active trader, you my want to lessen this interval to 30 days or less. If, however, you don’t trade that often, you may prefer a longer cutoff. The default is set to 60.

Enter transactions in the file

To make the data-entry process simple, there is a userform which will allow you to enter your buy and sell transactions. You can also enter starting balances and dividend payments. You can access the form from the Trading Journal section on the Home tab, right within the Excel ribbon.

The trading journal buttons for the stock trading template.

By clicking the Enter Transaction button, you will see the following form pop up:

The userform to enter transactions in the stock trading template.

To start, you can use the Date Picker button to select the transaction date. With the date picker, you can use the button on the left and right side to move one month at a time. You can also select the month from the drop-down list. You can change the year by just typing in the year of the transaction. Once you have the right date and month, use the tab key so that the calendar updates. Then, click on the date the transaction relates to.

The date picker userform in the stock trading template.

Next, select the action type. This will determine which fields you need to enter. There are multiple options to choose from.

Selecting a transaction type for the stock trading template.
  • If you are buying a new stock you don’t already own, select Open New Position.
  • If you already own a stock and are adding more shares, select Add to Existing Position.
  • If you are selling shares of a stock you own, select Sell Shares.
  • If you are adding cash to your portfolio, select Enter Contribution.
  • If you are withdrawing cash from your portfolio, select Enter Withdrawal.
  • If you are recording dividend payments, please select Enter Dividends.
  • If you are already own stocks and need to enter in your initial positions, select Starting Balance. For starting cash positions, use the Enter Contribution option.

You will then see a different form based on your selection, showing you which fields you need to enter.

Using different investing strategies

By default, there are three investing strategies you can select from when buying a stock: Buy and Hold, Contrarian, and Speculative. This can be helpful if you want to track how your different strategies work. If you don’t use different strategies, you can just leave everything as Buy and Hold. If, however, you want to add or modify strategies, click on the Update Strategies button when entering a new position:

Selecting a strategy on the stock trading template.

That will then populate a new userform where you can manage the different strategies.

Modifying strategies on the stock trading template.

How the template tracks transactions

When you enter a transaction, it goes into two places. One is the Activity tab, and this acts as a log for everything you’ve entered. There is also the Transactions tab, which is designed for traders to track their opened and closed positions.

The data also flows through to the Summary tab, where you will be able to see your running portfolio balance, track gains and losses, and also see reports highlighting your overall trading performance. To make sure that all the charts update correctly, click on the Refresh button from the Trading Journal section on the Home tab.

Chart showing portfolio balance by month.

Download the template

Want to try out the template? You can download the trial version here. It is limited to 25 transactions. If you like it, please consider purchasing the full version.

If you run into any issues with the template or have feedback or suggestions for improvements, please feel free to contact me. Please include the name of the template when drafting your message.

If you like the 2024 Stock Trading Template, 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.

PEGRatio

How to Calculate the PEG Ratio in Excel

by conditional formattingformattinginvestingstocks

What is the PEG Ratio?

The Price/Earnings to Growth (PEG) Ratio is a metric that enhances the traditional price-to-earnings (P/E) ratio by incorporating the company’s earnings growth rate into the calculation. This ratio is calculated by dividing the P/E ratio by the annual earnings per share (EPS) growth rate.

This calculation provides a more nuanced view of a stock’s valuation by factoring in future earnings growth, offering a more comprehensive perspective compared to the P/E ratio alone, which only considers the current price relative to earnings.

Why Investors Find the PEG Ratio Useful

Investors use the PEG ratio for several reasons. It allows for a more balanced comparison between companies with differing growth rates. A high P/E ratio might suggest a stock is overvalued, but when accounting for strong anticipated growth (as the PEG ratio does), the stock might actually be undervalued. This makes the PEG ratio a favored tool for identifying stocks that might offer a better return on investment, particularly when looking for good growth stocks.

The PEG ratio also aids in evaluating the potential overvaluation or undervaluation of a stock in relation to its growth prospects. A PEG ratio below 1 is often interpreted as a stock being undervalued given its earnings growth, whereas a ratio above 1 might indicate overvaluation. This simple benchmark can guide investors in making more informed decisions.

What is the Formula to Calculate the PEG Ratio?

The PEG ratio includes two components: the stock’s P/E ratio and the annual EPS growth rate. This is what the formula looks like:

PEG ratio formula.

Creating a template in Excel to calculate the PEG Ratio

Calculating the PEG ratio in Excel is straightforward, allowing investors to efficiently assess multiple stocks’ growth prospects against their valuations. Here’s a step-by-step guide to setup a worksheet to help you do this:

  1. Input Data: Begin by entering the necessary data into Excel. You’ll need the current stock price, EPS, and the annual EPS growth rate. Ideally, you’ll want to setup the inputs first, followed by the formulas at the bottom. This will make it easier to enter the data in logical steps: first the ticker, the stock price, the EPS, and then the annual EPS growth.
  2. Calculate P/E Ratio: In the first calculation cell, I’ll calculate the P/E ratio by dividing the stock price by the EPS.
  3. Calculate PEG Ratio: The next calculation cell is the PEG ratio. This is calculated by taking the P/E ratio and dividing it by the annual EPS growth rate. If a stock is expected to grow at a 50% growth rate, the value should be 50, not 0.5 (i.e. don’t enter it as a percentage). Otherwise, this won’t calculate correctly.
  4. Conditional Formatting. This is an optional step, but one which can help with your analysis. Use conditional formatting rules to highlight the PEG ratio based on its value. If it is less than 1, I’ll apply a green highlighting, a red highlight if it is more than 3, and yellow for anything in-between. Here is how you might set that up with an icon set
Creating conditional formatting rules using icon sets.

Here is how the template looks based on their stock prices and data as of Feb. 1, 2024:

In the above example, we have a fast-growing stock in NVDA, a moderate-growing stock in AAPL, and a slower-growing one in KO. Essentially what we are doing here is looking if the EPS growth rate is higher than the P/E ratio. If it is, that suggests it is not an expensive buy. NVDA, for example, is expected to more than double each year for the next five years, as is evident by its 102% EPS growth rate. While that would make it look like a cheap buy, you’re also assuming that it really can achieve that kind of a growth rate, which would be no easy feat. That leads us to an important part section: the limitations of this calculation.

Limitations of the PEG Ratio

While the PEG ratio offers valuable insights into a stock’s potential value by incorporating growth into the valuation equation, it’s important to recognize its limitations. Understanding these constraints can help investors use the PEG ratio more effectively alongside other analysis tools.

  1. Growth Rate Estimations: The PEG ratio is heavily dependent on the accuracy of the earnings growth rate projections. These forecasts can be highly speculative and vary widely among analysts. Overly optimistic or pessimistic growth estimates can skew the PEG ratio, leading to potentially misleading conclusions about a stock’s valuation.
  2. Historical Growth vs. Future Potential: The PEG ratio typically uses historical data to predict future growth, but past performance is not always a reliable indicator of future results. Companies in rapidly changing industries or facing new competitors may not sustain their previous growth rates.
  3. One-Size-Fits-All Approach: The simplicity of the PEG ratio, while a strength, can also be a drawback. It does not account for the nuances of different industries or the specific risks and opportunities facing individual companies. A low PEG ratio does not guarantee success, nor does a high PEG ratio always indicate a bad investment.
  4. Dividend Exclusion: The PEG ratio does not consider dividend payments. For income-focused investors, a company’s dividend yield and the stability of its dividend payments can be as important as growth. Companies with high dividend yields might be undervalued by the PEG ratio, which only focuses on earnings growth.
  5. Market Conditions: The effectiveness of the PEG ratio can also be influenced by the overall market conditions. During bull markets, growth stocks tend to perform well, and their high PEG ratios may be justified by the market’s momentum. Conversely, in bear markets, value stocks with lower PEG ratios might be more favorable, regardless of growth projections.
  6. Quantitative Focus: The PEG ratio is a purely quantitative tool and does not take qualitative factors into account. Elements such as management quality, brand strength, market position, and industry trends can significantly impact a company’s future performance but are not reflected in the PEG ratio.

If you liked this post on How to Calculate the PEG Ratio 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.

LoopStocksGoogleSheets

Loop Through Stocks in Google Sheets With a Macro

by appscriptgoogle sheetsinvestingmacrosstocks

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:

A list of stocks on Google Sheets.

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:

Google Sheets warning message.

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.

SP500Returns

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

by google sheetsinvestingstocks

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.

DollarCostAveraging

How to Do Dollar Cost Averaging in Excel

by investingstocks

A popular investing strategy is dollar-cost averaging. With dollar-cost averaging, people invest a fixed amount of money into an investment on a recurring basis, regardless of whether the price has gone up or down. In this post, I’ll go over how it works, the benefits and disadvantages of dollar-cost averaging, along with a step-by-step guide on how to do it in Excel, using the stock market as an example.

What is dollar-cost averaging?

Dollar-cost averaging is an investment technique that takes the emotional aspect out of investing by spreading purchases over regular intervals, typically on a monthly basis. This means that regardless of market conditions or how you feel about an investment, you invest the same fixed-dollar amount on a recurring basis. When a stock’s price is low, that means you can buy more shares since they are cheaper. And when the price is higher, you buy fewer shares. But the end result is that you’re investing the same amount of money each time.

Why investors might use dollar-cost averaging

Below are the main reasons investors may want to consider using dollar-cost averaging:

Risk Mitigation

Dollar-cost averaging reduces the impact of market volatility on investment returns. By investing consistently over time, investors avoid the risk of investing a lump sum at the market peak and potentially suffering significant losses if the market subsequently declines. If the stock goes up in value, then that means your earlier buy-ins are generating profits and you’re buying into the rally. If the stock is going down, then you’re buying more of it and are averaging down. The benefit here is that as long as the business and investment remains sound, there’s a good chance that the stock will recover from a drop. Buying low could end up setting you up for some great returns.

Disciplined Approach

By dollar-cost averaging, investors establish habits that can help them resist the temptation to make impulsive decisions based on short-term market conditions. The investor remains focused on the long term and that can help lead to more rational decisions.

Ease of Implementation

Dollar-cost averaging is simple to execute, whether you’re an experienced investor or a novice one. You don’t need to do any complex analysis and instead just need to do the same thing every month or every period you plan to buy stock. By making the process easy, it makes it easier to adhere to.

Benefits of dollar-cost averaging

There are several advantages to using dollar-cost averaging:

Emotional Discipline

Emotions can often drive investment decisions, leading to irrational actions such as panic selling during market downturns or chasing after hot stocks during bull markets. Dollar-cost averaging can ensure you aren’t being reactive or making emotional decisions, which can lead to losses and risky behavior.

Lower Average Cost

Provided that you’re investing in a quality business that will grow over time, dollar-cost averaging can keep your average cost down. That’s because you’re buying more shares when prices are low, and thus, are able to average down. And if the investment grows over time and its value increases, so do your profits. By making incremental purchases along the way, you don’t need to worry about buying at the peak.

Reduced Timing Risk

Timing the market is notoriously challenging, even for seasoned investors. Dollar-cost averaging mitigates timing risk by spreading investments over time, reducing the impact of market fluctuations on the overall portfolio. Since you’re buying stock at regular intervals, there’s no temptation to time the markets and you get a more balanced investing strategy.

Flexibility and Scalability

Dollar-cost averaging makes it easy for anyone to build up their position in a stock. Whether you can afford to invest $5,000 or $500, you can spread the amount you plan to invest over the course of a full year into 12 monthly payments. Brokerages nowadays offer low or no-cost commissions, making it easy to justify investing even a modest amount; there’s no need to make a big buy-in.

Disadvantages of Dollar-Cost Averaging

These are the biggest drawbacks of using dollar-cost averaging:

Potential Missed Opportunities

The biggest downside of dollar-cost averaging is that if the price of a stock has dropped significantly, you are not investing more than your recurring amount. Even if the stock becomes a steal of a deal, with dollar-cost averaging you could potentially miss out on that opportunity since you aren’t making a big purchase at the time a stock becomes oversold or is trading at a big discount.

Increased Transaction Costs

In the event you aren’t using a low-cost brokerage and where you are incurring transaction fees, you could be incurring high expenses relative to your investment amount. This can be particularly troublesome when you’re making small investment amounts and fees will end up representing a big chunk of your overall investment. In these situations you may either want to increase your recurring investment amount, or simply not deploy dollar-cost averaging.

Diversification Limitations

Dollar-cost averaging is more effective when investing in a few stocks. It wouldn’t be practical or efficient if every month you had to invest the same amount in 10 or more different stocks. It can quickly become a time-consuming process, one that might not be worth sticking to. That’s why when investors talk of dollar-cost averaging, it usually relates to a small number of stocks, or perhaps even just one.

Market Trend Irrelevance

Dollar-cost averaging may not provide good returns in a bear market. When the market keeps going down, buying more simply ends up increasing your losses since you’re investing more during a downtrend. If you are going to use dollar-cost averaging, you need to have confidence in the business you’re investing in and be willing to be patient enough to hang on in the event of a bear market. If you need to sell your investment within a few weeks or months, dollar-cost averaging may not be a suitable strategy for you.

Step-by-Step Guide to Dollar-Cost Averaging

Here are the steps to take if you want to get started with dollar-cost averaging:

1. Set Investment Period

Determine the time interval for your investments. Monthly investments are common, but you can choose any frequency that suits your financial situation.

2. Allocate Investment Amount

Decide on the fixed amount you want to invest during each interval. This can be any amount that fits your budget and investment goals.

3. Choose Investment(s)

Select the asset or assets you want to invest in regularly. This can be individual stocks, exchange-traded funds (ETFs), mutual funds, or any other investment vehicle.

4. Start Investing

Begin investing the fixed amount at the chosen intervals, regardless of the asset’s price. Maintain consistency over the set investment period.

5. Monitor and Adjust

Regularly review your investment strategy and portfolio performance. Adjust the investment amount or asset allocation if your financial situation or investment goals change.

    How to Calculate Dollar-Cost Averages in Excel

    If you’ve begun dollar-cost averaging and want to know what the average cost of your investment is, you can do this easily in Excel. Create a table with the following headers: Date, Stock Price, Investment, Shares, Cumulative Investment, Cumulative Shares, and Running Average.

    The Date relates to when the stock was purchased.

    The Stock Price is what the stock price was when it was purchased.

    The Investment is the total investment amount. This should be the same amount each period.

    The Shares field is the number of shares purchased. This is the Investment total divided by the Stock Price.

    The Cumulative Investment field is the sum of the Investment field up until the current date.

    The Cumulative Shares field is the same thing, except it calculates the number of shares purchased up until the current date.

    The Running Average takes the Cumulative Investment and divides it by the Cumulative Shares. Here’s an example of how dollar-cost averaging would have worked if you used to approach with Novavax, beginning in December 2021:

    As you can see, with the very first row and very first purchase, the running average is the same as the stock price. But as the stock declines in value over the year, the running average becomes lower.

    If you liked this post on How to Do Dollar Cost Averaging 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 me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.