2

Is there a method to use GoogleFinance() in Google Sheets to get the Adjusted Close price, which adjusts for Splits and Dividends like Yahoo Finance?

maxhugen
  • 1,870
  • 4
  • 22
  • 44

2 Answers2

2

I believe the historical Price from Google Finance is adjusted

=GOOGLEFINANCE("GOOG", "Price", DATE(2021,6,16))

Matches the adjusted column in Yahoo Finance

enter image description here

enter image description here

JohnA
  • 1,058
  • 6
  • 12
  • Thanks, yes, this was mentioned in another post I found (https://stackoverflow.com/a/66608544/190925) but apparently it does not adjust for Dividends. Not sure about that? – maxhugen Jun 18 '21 at 22:47
  • 1
    I am fairly sure it is also adjusted for dividends. I just compared AAPL in 1985 using the google finance function and looking at the historical Yahoo data and they match. Now, if I only bought AAPL when they went IPO and held on to it. – JohnA Jun 19 '21 at 00:00
  • Thanks John. If only I had a crystal ball... :) Cheers, Max – maxhugen Jun 19 '21 at 23:54
  • 3
    I spent a considerable number of years in the financial industry serving in IT. A few months ago I decided to write this backtesting tool in Sheets just for the heck of it. Feel free to make a copy, the first time you run the script you will need to authorize. https://docs.google.com/spreadsheets/d/1KYhtUTHpYcmJAr56bZSOU-ZKlj_xWwrIckz6F-8VjIM/edit#gid=141819050 – JohnA Jun 20 '21 at 15:41
  • 2
    The reason that the Google Finance 'Price' matches the 'Adj. Close' value in the screenshot is because 'Adj Close' is identical to the 'Close' value. Price does not return the Adjusted Close value. – Andrew Arthur Jan 25 '22 at 05:09
  • 2
    This does not adjust for dividends. For example, look at PDBC which paid out a $1.757 dividend on Dec 20, 2021. On Dec 17 Yahoo finance (correctly) gives Close=15.32 and Adj. Close = 13.56. Trying all the various attributes in Googlefinance only returns 15.32. – Aeryk Apr 05 '22 at 15:59
0

Googlefinance only returns close prices, not adj close. For example, PSA paid $2 dividend on Dec 13, 2022. Googlefinance returns closing price 301.42, not 299.42 after adjustment as shown on Yahoo! Finance.

One method is to calculate adj close from close, which is time-consuming.

2nd method: use python to download adj close with yfinance library

3rd method: excel->get data from web->paste link of the stock's historical prices from web, e.g. https://finance.yahoo.com/quote/AAPL/history/

It looks like the 3rd method is most convenient.

TVC
  • 67
  • 4