Using Googlesheets and Googlefinance to get Closing Averages for Stocks

Hey, everyone! In this blog post, I'm going to show you how I use Google Sheets to do some cool coding stuff with stock data. Specifically, I'm going to explain how I filter by column and calculate the closing averages over different time periods for different companies. Sounds fun, right?

First, let me tell you how I set up my spreadsheet. I have a sheet called "Indicators" where I store all the data I need. In row 2, I have the ticker symbols of the companies I'm interested in, sorted alphabetically from left to right. In row 13, I have a formula that uses the GOOGLEFINANCE function to get the closing prices of each stock for the past 185 trading days. For example, in cell H13, I have this formula:

=INDEX(GOOGLEFINANCE(H2,"PRICE",TODAY()-185,TODAY()),,2)

This formula gets the closing price of the stock in H2 (which is AAPL) for each day from today minus 185 days to today (Consider Weekends, if altering the number of days), and then returns only the second column of the result, which is the price. The two commas are used to exclude the ROW in the formula, thereby returning all the rows within the date range.

Next, in rows 14 to 17, I have four formulas that use the AVERAGE function to calculate the closing averages over 20, 45, 90, and 120 days for each stock. For example, in cell H14, I have this formula:

=IFERROR(AVERAGE(INDIRECT("H"&140-20):H$139),0)

This formula averages the values in cells H120 to H140, which are the closing prices of AAPL for the past 20 trading days. If there is an error (such as a missing value), it returns zero instead.

I repeat this process for the other time periods and stocks, so I end up with a table that looks like this:

|--------|------|------|------|------|

| 20-day | 150 | 3200 | 2800 | 300 |

| 45-day | 145 | 3100 | 2700 | 290 |

| 90-day | 140 | 3000 | 2600 | 280 |

|120-day | 135 | 2900 | 2500 | 270 |

Now, here comes the fun part. I want to be able to quickly see the data for any stock I choose, without having to scroll through the whole table. So, I use a FILTER function to do that. In cell G1, I enter the ticker symbol of the stock I want to see. Then, in column F, I have this formula:

=FILTER($G2:FV140,$G$2:$FV$2=G1)

This formula filters the data in range G2:FV140 (which is my whole averages table range - Previous Columns have similar code but give me that averages of Indices) by matching the values in row 2 (which are the ticker symbols) with the value in G1 (which is my input). It then returns the matching column in column F. For example, if I enter GOOG in G1, I get this:

| Ticker | GOOG |

| 20-day Closing AVG| 138.91 |

| 45-day Closing AVG | 137.16 |

| 90-day Closing AVG | 135.73 |

|120-day Closing AVG | 135.57 |

This way, I can easily see how the closing price of GOOG has changed over different time periods. This is a useful technical indicator that can help me decide when to buy or sell a stock.

Of course, technical analysis is not everything. I always recommend doing your own research and looking at the fundamental ratios of a company before investing. But this Google Sheets trick can help you get a quick overview of the market trends and make informed decisions.

I hope you enjoyed this blog post and learned something new. If you have any questions or comments, feel free to leave them below. Thanks for reading!

How to Add Beneficiaries on E*TRADE Without Losing Your Mind

“Because your money should go where you want it, not where the probate court thinks it should, I am sharing this information.” Ah, E*TRADE. The place where your money grows, your trades execute (sometimes), and your hopes for financial freedom flutter like a candlestick chart on a volatile Thursday. But what happens if you kick the bucket before you get that Tesla stock to moon? Simple: you assign a beneficiary. Unfortunately, E*TRADE doesn’t make this as intuitive as you might think. This isn’t a “click here and boom, you’re immortal” situation. But fear not, fellow capitalist. I’ve braved the pixelated jungle so you don’t have to. 🛠️ Step-by-Step: Setting a Beneficiary for Your E*TRADE Brokerage Account (aka “How to ensure your money doesn’t end up in your ex’s lap or your neighbor's GoFundMe”) Log in at etrade.com . (Obvious, yes. But worth saying—this isn’t Webkinz, you need the real site.) At the top, click “Accounts” and select your Brokerage Account . (The on...

NJ's Middle-Class Squeeze: Too Much for Help, Not Enough for Comfort

This is a long post — longer than what I usually write — because what I’m talking about here isn’t a small annoyance or a passing frustration. It’s something that has been building for years, and I’m finally putting it all into words. I’m upset, I’m exhausted, and I’m passionate about what follows, because it affects every working person in this state who’s trying to stay afloat. There’s a growing group in New Jersey — people who work full‑time, sometimes more than one job, who earn too much to qualify for assistance but not enough to absorb the constant increases in living costs. These are the people tightening their budgets, lowering their thermostats, cutting back wherever they can, and still watching their bills rise for reasons that have nothing to do with their own usage or behavior. If you’re part of that group, or you know someone who is, then what follows will probably resonate with you. And if you’re not, then I hope this gives you a clearer picture of what the middle class i...

Understanding Treasury Bond Auctions: The Difference Between High Yield and Interest Rate

Treasury bonds are a popular choice for investors looking for a reliable source of income backed by the U.S. government. However, understanding how these bonds are priced at auction can be confusing, especially when comparing the High Yield and the Interest Rate (Coupon Rate) columns. In this post, I'll break it down using a real-world example. A Look at a Recent Treasury Bond Auction Here’s an example of a 20-year Treasury bond that was recently auctioned: Security Term CUSIP Reopening Issue Date Maturity Date High Yield Interest Rate 20-Year 912810UF3 Yes 01/31/2025 11/15/2044 4.900% 4.625% What Do These Numbers Mean? CUSIP : This is a unique identifier for the bond. Reopening : Since it says "Yes," this means the bond was originally issued earlier and is now being reoffered. Issue Date : January 31, 2025—this is when the bond will be offi...

The Signal Amidst the Noise

Search This Blog