0

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.

2
  • 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
    – Tedinoz
    Aug 1 at 1:55
  • Hey thanks for the feedback i have added a few sentences to the original post and hopefully that helps.
    – Dan
    Aug 1 at 4:02
0

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 pagejavascript.infoMozilla Developer Network and Apps Script at Stack Overflow.

1
  • Comments are not for extended discussion; this conversation has been moved to chat.
    – Rubén
    Aug 6 at 23:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.