How to get peak price of a stock after a particular date in Google Spreadsheet?

1
votes

I'm trying to create an automated Google Spreadsheet to track my positions. There's a column which needs to display peak price of a stock after I have added it in my portfolio. Say I added a stock on 3rd November, I need to know what's the highest price stock reached after 3rd November. And that column keeps updating itself as and when that stock touches new highs.

EDIT: I am including a screenshot.

In the screenshot attached above you can see column F2 will update itself by taking live stock price from Google Finance.

For F2 I used =GOOGLEFINANCE(B2,"price")

Now in column E2, I want highest price displayed in F2 in since the date of addition of stock. Suppose on November 20th stock touched high of Rs. 200 and then came down to 180. F2 would display price as 180 but E2 should display price as 200(which is the peak price).

I tried using few codes but none of them worked. I am listing the few ones I tried.

=GOOGLEFINANCE(B2,"high","11/03/2017")
=(GOOGLEFINANCE(B2,"high","11/03/2017","12/31/2018"))
=max(GOOGLEFINANCE(B2,"price","11/03/2017")) 
=max(GOOGLEFINANCE(B2,"price","11/03/2017","12/31/2018"))

I even tried referencing date with column A2. But that also didn't work. Referencing Date with Column A2 is a priority, since that will enable me to drag and add another stocks without the need to edit the formula everytime.

2
google-sheetsgoogle-spreadsheet-apigoogle-financegoogle-finance-api
Please also tell us what have you tried. What documentation did you read. What was it that you expected, and what did not work. If you get stuck, you may ask where you got stuck. We shall be happy to help you. – Ramesh Pareek
I have added further details. Please let me know if there is need for any more clarification. – Utkarsh Pandey

2 Answers

2
votes

For the maximum closing price for GOOG after or on November 3, 2016 to the end of last year, please try:

=query(googlefinance("GOOG","price",DATE(2016,11,3),DATE(2017,1,1),"DAILY"),"select max(Col2)")
2
votes

This is what I use to get the highest high price for a given range:

=MAX(INDEX(GoogleFinance($B3,"all",WORKDAY(TODAY(),-D$1),TODAY()),,3))

Note:

  • The ticker symbol is in Column B
  • The range I want to look back is in row 1
  • My formula in this case is in column D
  • The 3 at the end gives you data from the daily highs
  • To get the lowest low for a range, change the 3 to a 4 and max to min