9 votes

How can I get GoogleFinance to see 1 year and 5 year return of a stock?

If the cell is formatted as a NUMBER: =ROUND( ( GOOGLEFINANCE("GOOG","price") / index(GOOGLEFINANCE("GOOG","price", datevalue(today()-365) ) , 2,2) - 1 ) * 100 ,2) Alternative: =ROUND( ( ...
Alan's user avatar
  • 91
9 votes
Accepted

Remove Header from GOOGLEFINANCE query

Please try: =query(GoogleFinance(""&G1&":"&H1; "price"; Stocks!A28; TODAY());"select * label Col1 '', Col2''") QUERY
pnuts's user avatar
  • 17.6k
7 votes

How to get close price for last date market was open?

=GOOGLEFINANCE("GOOG","CloseYest")
RDM's user avatar
  • 81
5 votes

Automatically store updated GOOGLEFINANCE values with a script

Functions like GOOGLEFINANCE are only updated when the spreadsheet is open by a user, there isn't a Google Apps Script method that is able to do this. The closest is SpreadsheetApp.flush() but this ...
Rubén - Volunteer Moderator -'s user avatar
5 votes
Accepted

How to get close price for last date market was open?

A workaround that solves the above is getting the Close price for the stock for a range of days before and after the date in question, and then querying the result for the most recent price on or ...
twalbaum's user avatar
  • 451
4 votes

Google Finance API call for stock dividend (Yield)?

Try this: =INDEX(SPLIT(INDEX(IMPORTHTML(CONCATENATE("https://finance.google.com/finance?q=", "JNJ"),"table",3),1,2), "/"),1, 2) * 0.01
tb11's user avatar
  • 141
3 votes

Google Finance Historical Data with Multiple Attributes?

=ARRAY_CONSTRAIN(GOOGLEFINANCE("NSE:"&A2, "all", TODAY()-($N$2), TODAY(), "DAILY"), 2, 4) =QUERY(GOOGLEFINANCE("NSE:"&A2, "all", TODAY()-($N$2), TODAY(), "DAILY"), "select Col2,Col3,Col4", -...
user0's user avatar
  • 26.5k
3 votes
Accepted

Google Exchange Rates build

=IFERROR($C5*GOOGLEFINANCE("currency:"&$C4&D6), $C5)
user0's user avatar
  • 26.5k
3 votes

Google Finance API call for stock dividend (Yield)?

To further help with this answer. I tried tb11's answer and did not work for me June 2019. It was the URL that was the issue. I picked another site with a simpler URL and it worked for me. Sample ...
CJ17's user avatar
  • 31
3 votes
Accepted

Append Current Stock Stock Price to Historical Prices

The formula is failing because you are giving arrayformula() two expressions when it only expects one. One of the { array expressions } is also superfluous. It is probably easier to do this with ...
doubleunary's user avatar
  • 14.4k
2 votes

How to add refreshed data to new column in Google Sheets

Whenever it refreshes, the data is copied as opposed to new data being added. It seems like it is adding the formula itself to the spreadsheet as opposed to putting in the value itself. If I ...
Christopher Su's user avatar
2 votes

How can I get GOOGLEFINANCE to return only the historical price of a stock and not an array?

=INDEX(GoogleFinance("USDIDR", "PRICE", F530),2,2) F530 = cell with date (formatted to date)
MurderByNumbers's user avatar
2 votes

How can I get ''OHLC'' data rather the Close only?

to get the whole set of Open | High | Low | Close | Volume you need to use one currency per the formula: =GOOGLEFINANCE("USD", "ALL", TODAY()-1) =GOOGLEFINANCE("EUR", "ALL", TODAY()-1) another ...
user0's user avatar
  • 26.5k
2 votes

I want to get rid of google finance disclaimer

you can use Block element of Adblock Plus: docs.google.com###waffle-disclaimer-content
user0's user avatar
  • 26.5k
2 votes

How can I use GOOGLEFINANCE to return a share price in a certain currency?

Welcome, Eric! Google Finance tracks exchange rates. The syntax is: =GOOGLEFINANCE("CURRENCY:BRLCAD") where BRLCAD is the two currencies, in this case Brazilian Real to Canadian Dollar, in ISO 4217 ...
Joel Reid's user avatar
  • 700
2 votes
Accepted

GOOGLEFINANCE in Google Sheets to get high & low and timestamp of high/low from the day I took trade

You can use the following formula =QUERY(GOOGLEFINANCE("NSE:"&C1&"", "ALL",C2 ,C3),"select Col1, Col3, Col4",1) Functions used: GOOGLEFINANCE QUERY
marikamitsos's user avatar
  • 12.8k
2 votes

How to get a YTD dynamically in Google Sheets (Google Finance)?

I got it: just leave the year out. Solution: =(GOOGLEFINANCE(C5,"price")/INDEX(GOOGLEFINANCE(C5,"close","01/01", today()),2,2))-1
Cold Avenue's user avatar
1 vote
Accepted

GOOGLEFINANCE in Google Sheets to get high & low and timestamp of high/low

={"TSLA", MIN(INDEX(GOOGLEFINANCE("TSLA", "low", "7/23/2018", TODAY(), 1),, 2)), VLOOKUP(MIN(INDEX(GOOGLEFINANCE("TSLA", "low", "7/23/2018", TODAY(), 1),, 2)), {INDEX(GOOGLEFINANCE("...
user0's user avatar
  • 26.5k
1 vote

Google Sheets formula to find previous day high of a share price

I think this will help you =INDEX( IF(ISNA(GOOGLEFINANCE("NSE:"&A2,"high",TODAY()-1))=TRUE, IF(ISNA(GOOGLEFINANCE("NSE:"&A2,"high",TODAY()-2))=TRUE, ...
dgnk's user avatar
  • 11
1 vote

Google Sheets formula to find previous day high of a share price

use INDEX to filter it out like: =INDEX(GOOGLEFINANCE("nse:"&O18, "high", TODAY()-2.1), 2, 2)
user0's user avatar
  • 26.5k
1 vote

Automatic currency conversion in Google Sheets

Other option is using the CurrencyConverter function from this Google Sheets add-on. It is fast and and has simple syntax. For example, =CurrencyConverter(100, "USD", "EUR", "...
Serghei Gorodetki's user avatar
1 vote

How can I have a 2nd function start in the cell after the output of the 1st function?

delete your A column and paste this into A3 cell: ={GOOGLEFINANCE("ASX:EXL", "close", TODAY()-470, TODAY()); QUERY(GOOGLEFINANCE("ASX:EXL", "close", TODAY()-470, TODAY()), "select Col1 offset "&...
user0's user avatar
  • 26.5k
1 vote
Accepted

How to import historical open price in Google Sheets with GOOGLEFINANCE?

This actually kind of makes sense. If the 2nd parameter of GOOGLEFINANCE is "priceopen" it tries to fetch the open price but by adding a historical date to it DATE(2019,4,12) the 2nd parameter is ...
user0's user avatar
  • 26.5k
1 vote

googleFinance calculating the standard deviation of a stock over a time period

I liked iggy's answer but holidays at the beginning or end of the time periods can cause errors. Here's an updated version, again to calculate historical volatility over the last 50 trading days ...
ikseoj's user avatar
  • 11
1 vote

googleFinance calculating the standard deviation of a stock over a time period

Here is a formula for computing historical stock volatility using log price changes (returns) over the last 50 days (B is the ticker column): =STDEV(arrayformula(LN( query(GoogleFinance($B2,"...
iggy's user avatar
  • 111
1 vote

googleFinance calculating the standard deviation of a stock over a time period

I believe I figured it out. It's: =STDEV(INDEX(GoogleFinance("GOOGL","all",WORKDAY(TODAY(),-50),TODAY()),,3))
user2946746's user avatar
1 vote
Accepted

How to get Google Finance Intraday data into Google Spreadsheet?

=GOOGLEFINANCE() is appropriate. But you can also use IMPORTDATA()
TheMaster's user avatar
  • 3,352
1 vote

How to get close price for last date market was open?

INDEX(SORT(GOOGLEFINANCE("GOOG", "close", TODAY()-5, 6,1), 1, FALSE), 2, 2) This will show the list of results in reverse, allowing one to pick off the top of the list (last valid ...
Mark Peteritas's user avatar
1 vote
Accepted

Automatically store updated GOOGLEFINANCE values with a script

As the yahoo and google finance API's to use them from google apps script are deprecated, I think the only way to work around it is to generate googlefinance queries from the script as strings and ...
nck's user avatar
  • 267

Only top scored, non community-wiki answers of a minimum length are eligible