Track highest and lowest number reached in a specific cell over a period of time in google sheets

I have a cell that references the total value of a stock portfolio. I want to figure out how I can let Google Sheets (over a period of time, say a month) automatically tell me the highest number and also separately the lowest number reached in that specific cell. In other words, I want to find the monthly high of my stock portfolio as a whole, and the monthly low as well.

I assume that this won't work when I'm not online on google sheets but as long as I can find the high/low and have it update for whenever I log onto sheets (if a new high/low is reached) that's good enough. If it updates without me online that's a plus, but I'm not sure if that's even possible.

Edit: As requested, the total value of the main cell is calculated from the market value of all stocks in my portfolio combined. The number of stocks changes whenever I buy or sell one but usually there are a few. I am using `googlefinance()` for the stock data, together with an `index()` formula for some other assets (notably crypto where `googlefinance()` doesn't work). The closest I got to resolve the problem was using the `ifs()` function by referencing cells and making them equal if they're greater than the current value, but this info might be irrelevant because I came to the conclusion that it's not really possible.

Please let me know if there's any other info you would like to know about.

• Welcome, Dan. You said you have "a cell that referencing the total value of a stock portfolio" but you don't explain how that "total" value is calculated, how many stocks you have, what data you have in your spreadsheet for those portfolio stocks, and/or whether you are using `GOOGFINANCE`. Would you please edit your question to provide more information about how your spreadsheet is structured, the data and formula. Would you please also give a brief summary of your research on this topic, and your efforts to resolve this problem. Thanks Commented Aug 1, 2021 at 1:55
• Hey thanks for the feedback i have added a few sentences to the original post and hopefully that helps.
– Dan
Commented Aug 1, 2021 at 4:02

To find the highest and the lowest point each month, you will need to save copies of the value in the "Total value" cell periodically, perhaps on a daily or an hourly basis, depending on how accurate you want the result to be.

You can save the value in the "Total value" cell daily to another sheet, each day inserting another row in an ever growing log file, with a script like appendValuesToArchiveSheet. Follow the step-to-step instructions in the script to install it in your spreadsheet and use these parameters:

``````// [START modifiable parameters]
var rangeToLog = 'portfolio!B2';
var sheetToLogTo = 'Archive';
// [END modifiable parameters]
``````

Once installed, the script will run on a daily basis, and you can use the data table it creates in the new 'Archive' sheet to get the statistics you want in cells `C2:D2` like this:

`=max(Archive!B2:B, B2)`

`=min(Archive!B2:B, B2)`

Once you have several months of data logged in the 'Archive' sheet, you can use `query()` formulas and pivot tables to create reports. To show a monthly high/low report, use a `query()` formula like this:

``````=query(
query(
Archive!A1:B,
"select year(A), month(A)+1, max(B), min(B)
where A is not null
group by year(A), month(A)+1",
1
),
"label Col1 'Year', Col2 'Month', Col3 'High', Col4 'Low' ",
1
)
``````