6

Please try: =query(GoogleFinance(""&G1&":"&H1; "price"; Stocks!A28; TODAY());"select * label Col1 '', Col2''") QUERY


5

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 only makes that the changes made by the script be pushed to the spreadsheet. One alternative is to rethink your model and take advantage that ... GOOGLEFINANCE ...


5

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( ( GOOGLEFINANCE("GOOG","price") / index(GOOGLEFINANCE("GOOG","price", date(year(today())-1,month(today()),day(today()))) , 2,2) - 1 ) * 100 ,2) If the cell formatted as ...


4

Try this: =INDEX(SPLIT(INDEX(IMPORTHTML(CONCATENATE("https://finance.google.com/finance?q=", "JNJ"),"table",3),1,2), "/"),1, 2) * 0.01


4

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 before that date, like this: =QUERY(GOOGLEFINANCE("GOOG","Close",DATEVALUE("1/1/15")-5,6),"select Col2 where Col1 < date '2015-01-02' order by Col1 desc limit 1"...


3

=GOOGLEFINANCE("GOOG","CloseYest")


3

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 below. =SPLIT(INDEX(SPLIT(INDEX(IMPORTHTML("https://finance.yahoo.com/quote/MITT?p=MITT&.tsrc=fin-srch","table",2),6,2),"("),1,2),")") Google, if you ...


2

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 understand correctly, you want the columns to keep a "snapshot" of the prices at the time the column was added, rather than have the values continue updating. In ...


2

=IFERROR($C5*GOOGLEFINANCE("currency:"&$C4&D6), $C5)


2

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 drawback is historical data limitation: for USD it's 2/1/2007 16:00:00 for EUR it's 2/4/2014 16:00:00 that can be checked with these formulas: =GOOGLEFINANCE("USD"...


1

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


1

=GOOGLEFINANCE() is appropriate. But you can also use IMPORTDATA()


1

You can use the following formula =QUERY(GOOGLEFINANCE("NSE:"&C1&"", "ALL",C2 ,C3),"select Col1, Col3, Col4",1) Functions used: GOOGLEFINANCE QUERY


1

I took a stab at it here: Column I has a formula like: =QUERY({$F$2:$G$1000},"SELECT Col1 WHERE Col1 <= date'"& text(C3,"yyyy-mm-dd") &"' ORDER BY Col1 DESC LIMIT 1", -1) This means that it pulls in the closest currency price date that's less than or equal to the stock price date. Column J has a formula like: =D3/...


1

Use string concatenation with &: =googlefinance("HKG:"&A2,"price") Note that I had to put '0700 in A2, or change the cell format to 'plain text'; just typing in 0700 will result in Google Sheets converting it automatically to 700.


1

The key word in your formula is not Query but GOOGLEFINANCE. Your query is correct. But please take into account the following. As mentioned under GOOGLEFINANCE documentation page: attribute is one of the following for historical data: "open" - The opening price for the specified date(s). "close" - The closing price for the specified ...


1

={"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("TSLA", "low", "7/23/2018", TODAY(), 1),, 2), GOOGLEFINANCE("TSLA", "low", "7/23/2018", TODAY(), 1)}, 2, 0), MAX(INDEX(...


1

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, GOOGLEFINANCE("NSE:"&A2,"high",TODAY()-3),GOOGLEFINANCE("NSE:"&A2,"high",TODAY()-2)),GOOGLEFINANCE("NSE:"&...


1

=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", -1)


1

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 "&(COUNTA( GOOGLEFINANCE("ASX:EXL", "close", TODAY()-470, TODAY()))/2)-1, 0)+IF(WEEKDAY(QUERY( GOOGLEFINANCE("ASX:EXL", "close", TODAY()-470, TODAY()),"select ...


1

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 confused because on that date there is no open price. On that date is only closed price at this moment. In other words: "the historical open price" is called "open"....


1

you can use Block element of Adblock Plus: docs.google.com###waffle-disclaimer-content


1

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", "2/28/2020") returns 91.09957183 Disclaimer: I am author of this add-on.


1

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 write them in the sheet. Then, once we enter in the sheet those queries are sent. I don't think this is the most elegant way of do this, and it's also pretty ...


1

I used to be able to pull it from google finance, but that stopped working for me a little while back. I found another one. It's not quite as clean, but it is working for me as of March 2019. In column B, put your ticker symbol In column C: =IF(NOT(ISBLANK(B12)),GoogleFinance(B12,"Price"),"") This will populate the current price, which is used in the next ...


1

=BDMIN(GOOGLEFINANCE("Goog"; "PRICE"; "YourStarDate";"YourEndDate");"CLOSE"; GOOGLEFINANCE("Goog"; "PRICE"; "YourStarDate";"YourEndDate")) It works for me. Example for the english locale: =DMIN(GOOGLEFINANCE("Goog", "PRICE", "10/10/2019","10/11/2019"),"CLOSE", GOOGLEFINANCE("Goog", "PRICE", "10/10/2019","10/11/2019"))


1

=INDEX(GoogleFinance("USDIDR", "PRICE", F530),2,2) F530 = cell with date (formatted to date)


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