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

Question

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.

Answer 1

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 
)

See your sample spreadsheet.

To learn scripting, go to the Beginner's Guide, the New Apps Script Editor guide, the Fundamentals of Apps Script with Google Sheets codelab, the Extending Google Sheets page, javascript.info, Mozilla Developer Network and Apps Script at Stack Overflow.