Questions tagged [google-finance]
For questions about Google Finance - service focusing on business news and financial information. Can be applied also on Google Sheets' GOOGLEFINANCE function.
65
questions
54
votes
5
answers
91k
views
How can I get GOOGLEFINANCE to return only the historical price of a stock and not an array?
I'm trying to figure out the cost basis of some stock that I sold last year and I can't quite get GOOGLEFINANCE to do what I want. The stock was purchased through a DRIP so there are about five shares ...
- 651
8
votes
6
answers
30k
views
How to get close price for last date market was open?
When using the GOOGLEFINANCE formula to get historical prices and performance for different periods, if the date used in the formula is one where the market was closed the formula returns #N/A. This ...
- 441
8
votes
1
answer
10k
views
Remove Header from GOOGLEFINANCE query [duplicate]
I want to create a graph with different stock values starting and ending at a certain day. To do that I create a table like this:
Where the first column is the market and the second one is the ticker ...
- 267
6
votes
1
answer
11k
views
Stock Sparkline for today/hourly
I am trying to make a sparkline graph in Google Sheets that tracks the stock price of TODAY using google finance as time progresses. For example, at 8am it would show the opening price, and by close ...
- 61
5
votes
3
answers
28k
views
How can I get GoogleFinance to see 1 year and 5 year return of a stock?
You can use GOOGLEFINANCE("name", "return52") for mutual funds, but not for stocks. Is there another way to get the same result?
- 51
5
votes
2
answers
9k
views
Automatically store updated GOOGLEFINANCE values with a script
Hello, I want to keep track of the profit of the combination of many stock values. To do that I have my google Spreadsheet with many formulas calculating the profits or losses ratios taking into ...
- 267
5
votes
1
answer
5k
views
Automatic currency conversion
I would like to add an automatic conversion thing to my spreadsheet.
I have two columns, A1 titled Money in USD and B1 titled Money in BRL.
I want to make it so whenever I enter a value in the USD ...
- 53
3
votes
1
answer
5k
views
Google Finance Historical Data with Multiple Attributes?
=GOOGLEFINANCE("NSE:"&A2, "all",(TODAY()-($N$2)),TODAY(),"DAILY")
The above code returns OHLC data for specified days with the date column.
What I want are only high, low and close columns.
=...
3
votes
2
answers
2k
views
I want to get rid of google finance disclaimer
I want to get rid of Google finance disclaimer.
I used the =GOOGLEFINANCE formula in a sheet. It triggered the display of a disclaimer at the bottom of the sheet that says:
Quotes are not sourced ...
- 31
3
votes
1
answer
1k
views
What ticker symbol to use for the crude oil price?
Which ticker symbol can I use for the crude oil price for the GOOGLEFINANCE function?
Nasdaq Brent oil is "BZ:NMX"
https://www.nasdaq.com/market-activity/commodities/bz:nmx
Nasdaq Crude oil ...
- 271
3
votes
3
answers
4k
views
Automatic currency conversion in Google Sheets
I have a personal accounting sheet that is mainly accounting in Euros, but for every manual Euro (EUR) entry I do, I want to have that entry converted to USD in the previous column next to it ...
- 29
2
votes
1
answer
163
views
Append Current Stock Stock Price to Historical Prices
I'm looking to append the current share price of a stock to the end of a period with changing durations.
My current formula for the historical prices is (where cell B3 is a duration of days to ...
- 227
2
votes
1
answer
45
views
Google Exchange Rates build
I am trying to make a currency exchange rate Google sheet. I want a cell to give me a value of conversion based on the value of another cell. Currency type can be selected from the Dropdown List and ...
- 59
2
votes
1
answer
706
views
GOOGLEFINANCE in Google Sheets to get high & low and timestamp of high/low from the day I took trade
I wanted to keep a track of high/low of a trade from the day I took the trade.
For a day I am able to get high/low but how to get from the date I took my trade which is stored in one of cell.
=IFERROR(...
2
votes
1
answer
5k
views
How can I use GOOGLEFINANCE to return a share price in a certain currency?
I have shares in different currencies that I want to track in only a single currency. I currently have a cell that returns the currency and one that returns the price, I then multiply them. However, I'...
- 21
2
votes
1
answer
4k
views
How can I get ''OHLC'' data rather the Close only?
=GoogleFinance("CURRENCY:EURUSD","all","1/23/2005","50")
The code above just fetch the close rate and leaves the other columns blank. How to fix that?
- 21
2
votes
1
answer
465
views
How to add refreshed data to new column in Google Sheets
I am making a Google Sheets app that reads column A which has a stock symbol and puts the price in the column to the right using =GOOGLEFINANCE
I have this refreshing every day and appending to the ...
2
votes
1
answer
4k
views
How do I pulling in historical daily trade volume data in Google Sheets with Google Finance?
If I try =GOOGLEFINANCE(A1,"volume",A2) where A1 is the stock code and A2 is the date I'm querying, it comes back printing 'date' and 'volume' on one line as headers and then the data on the ...
- 21
2
votes
1
answer
2k
views
How do I get GOOGLEFINANCE to return only the date and not the date and time of stock price history?
=GOOGLEFINANCE("NASDAQ:GOOGL","close",date(2021,1,1),today(),1)
returns closing history in two columns "Date" and "Close" since Jan. 1, 2021. The date column ...
- 121
1
vote
4
answers
53k
views
Google Finance API call for stock dividend (Yield)?
Is there an API call to use in a Google Spreadsheet to get a stock's Dividend Yield (% annual yield)
- 3,051
1
vote
1
answer
274
views
Get historical stock price in another currency
This formula returns the daily stock prices of A1 in the last two years:
=GOOGLEFINANCE(A1,"Price",today()-(365*2),today(),"DAILY")
This formula returns the daily conversion rate ...
- 11
1
vote
1
answer
335
views
How to link cell within googlefinance formula
I'm trying to get the googlefinance formula to work for multiple country exchanges.
e.g. Tencent is listed on Hong Kong stock exchange, the code is 0700.
If I put =googlefinance(HKG:0700,"price"), ...
- 11
1
vote
1
answer
112
views
Google Finance function returns Date not Currency Conversion
I am trying to calculate the USD:AUD conversion from M3 and show the result of the current conversion rate in the next cell, but it is only resolving the date
I am trying to get cell M3 to convert to ...
- 11
1
vote
2
answers
3k
views
Why does close price results have a date shift (one day off) while using GOOGLEFINANCE in a Query
I want to get last 40 days close price of stocks.
I used below formula for the same but the result not showing the last day close price.
=QUERY(
GOOGLEFINANCE("nse:"&B2,"all",$A$2-80,81),...
- 11
1
vote
1
answer
2k
views
GOOGLEFINANCE in Google Sheets to get high & low and timestamp of high/low
I'm using Google Finance calls in Google Sheets for some calculations.
I've got 2 formulas, one to find the low price, and one to find the high price from a certain date:
=MIN(INDEX(GOOGLEFINANCE("...
- 113
1
vote
1
answer
4k
views
How to import historical open price in Google Sheets with GOOGLEFINANCE?
For some reason, I can't get the google finance function to work with any open-price lookups I do.
For example:
=GOOGLEFINANCE("AAPL","priceopen", DATE(2019,4,12))
comes back as #N/A with message:
...
- 113
1
vote
0
answers
673
views
Google Finance in historical mode (WEEKLY): extra rows and not always aligned to Fridays
I am trying to use GOOGLEFINANCE function to query the weekly close price of an instrument (SP500, ticker ".INX"), for the last 52 weeks.
I first tried using the following formula:
...
- 11
1
vote
1
answer
1k
views
Google Finance Historical Data with Multiple Attributes? (Dates, high, low, close,)
I'm using this formula:
=ARRAY_CONSTRAIN(GOOGLEFINANCE("NYSE:"&A4, "all", DATE(2020,4,1), DATE(2020,4,30), "DAILY"), 2, 4)
I'm only getting one column for April 1, 2020.
I would like to get ...
1
vote
0
answers
154
views
Script to send email when a cell value is changed automatically by GOOGLEFINANCE function
I have a column of values that pulls data from GoogleFinance using the =GOOGLEFINANCE function in Google Sheets. When the function is working, the column will return a series of figures. Occasionally, ...
- 11
1
vote
1
answer
183
views
how to get max high of the given days excluding weekly off days
Can any one help how to get maximum high of given days excluding weekly off days (sat/sunday) from google finance.
I am using formula like: =query(GOOGLEFINANCE("NSE:"&Y3,"high",(TODAY()-55),...
- 11
1
vote
1
answer
108
views
Fetch value of a cell based on dropdown menu items results on another sheet
I have 2 Sheets named as Sheet1 and Sheet2...
Sheet1: Contains Drop down list on B4 Column. Based on the selection of an item, it pulls the result of the open price shown on D4 column.
Sheet2: ...
- 11
1
vote
2
answers
4k
views
Import Bitcoin price in EUR using the IMPORTXML fucntion
Hello does anybody know how to import the Bitcoin price in euro into a Google Sheets document by using the IMPORTXML function?
I am as far as I get the Bitcoin USD price -
=IMPORTXML("https://...
- 13
1
vote
0
answers
486
views
How to see Sparkline for Mutual Funds using googlefinance?
In Google sheets when I try to plot the mutual fund price for the last one year using sparkline function, I keep getting an error.
When evaluating GOOGLEFINANCE, the query for the symbol: ...
- 11
1
vote
1
answer
2k
views
Google Spreadsheet Stock Sparkline Trend Conditional Formatting
I am trying to do a Sparkline whose color changes based on the trend and for some reason the if clause which gets its input from google finance isn't working
=sparkline(query(googlefinance($C5, "...
- 11
1
vote
1
answer
560
views
Google Finance / Spreadsheets
I have been trying to get Net Assets and 1 Week Return for SPY on Google Spreadsheets but keep ending up with #N/A.
I have been following the information from this sheet but have yet to figure out ...
- 87
0
votes
4
answers
6k
views
googleFinance calculating the standard deviation of a stock over a time period
Can I calculate the standard deviation of a stock over a time period in GoogleFinance?
https://support.google.com/docs/answer/3093281?hl=en-GB
- 111
0
votes
2
answers
2k
views
How can I include two formulas in a single cell in Google Sheets?
I’m in the process of making a spreadsheet of stocks that I’m watching in Google Sheets. I want to include the daily price and percentage change in a single column. I found a helpful Docs Editors ...
- 101
0
votes
1
answer
2k
views
How to get a YTD dynamically in Google Sheets (Google Finance)?
I want to display the YTD of a stock listed in C.
=(GOOGLEFINANCE(C5,"price")/INDEX(GOOGLEFINANCE(C5,"close","01/01/2021", today()),2,2))-1
I do not want to change "...
- 39
0
votes
4
answers
14k
views
Google Sheets formula to find previous day high of a share price
Need previous day high/low price of a share.
This formula gives answer
=googlefinance("nse:"&columno.,"high",today()-2.1)
This gives an answer with date and time in separate columns.
But I need ...
0
votes
1
answer
74
views
How do I retrieve historical currency exchanges rates (USDEUR) in Google Sheets?
I am not quite sure how to get historical exchange rates from Google Sheets. It is the start date and number of days, daily/weekly that is confusing to me. I was able to get the current exchange rate, ...
- 860
0
votes
1
answer
182
views
To fetch stock price day's high and low for a range of days including today
For shares listed on NSE I am unable to get Day's high and low while using below query if the start day and end date is same or today.
High:
=MAX(QUERY(GOOGLEFINANCE("NSE:INFY", "ALL&...
0
votes
1
answer
2k
views
How can I get GoogleFinance to see the return of a stock from a specified date range?
How can I see the percentage of return from a stock from a specified date range to today? For example: February 19, 2020 to today = -31.93%.
So far I have:
=GOOGLEFINANCE("GOOG","price") / index(...
- 11
0
votes
2
answers
2k
views
Google Sheets - Historical Data not returning yesterday
The formula below is been used to get the historical data until today:
=GOOGLEFINANCE("GOOG","ALL",DATE(YEAR(TODAY())-1,1,1),"12/05/2019","DAILY")
Unfortunately I don't receive today's data (until ...
- 101
0
votes
1
answer
1k
views
Google sheet formula for previous 7 day high
Need a formula for previous 7 working day high for a share
=INDEX(GOOGLEFINANCE("nse:"&A2, "high", TODAY()-3.3), 2, 2)
Used the above formula but I need only workday formula.
0
votes
1
answer
33
views
How can I have a 2nd function start in the cell after the output of the 1st function?
So the problem I am trying to solve is where I have Google finance fetching the price of a stock and outputting it, but the length of cells of its price history is increased by 1 each day. In the ...
- 1
0
votes
1
answer
2k
views
How to get Google Finance Intraday data into Google Spreadsheet?
I want to import data from following link into Google Spreadsheet
https://www.google.com/finance/getprices?q=BANKBARODA&x=NSE&i=900&p=1d&f=v&df=cpct&auto=1&ts=...
- 103
0
votes
0
answers
75
views
Googlefinance historic currency exchange rate wrong
If in google sheets I do
=GOOGLEFINANCE("Currency:EURIDR")*GOOGLEFINANCE("Currency:IDREUR")
I get a value close to 1 as expected. (Product of exchange rate and reverse rate should ...
- 101
0
votes
0
answers
87
views
How can I solve this formula?
=index(importhtml(concatenate("https://finance.yahoo.com/quote/";A6;"/key-statistics?p="A6";"EN_US");1;2)
A6 is tiker: MO
I need Dividend date: Oct 10, 2022
Original ...
- 23
0
votes
0
answers
599
views
Google Finance Sheets: Dividends and Stock Splits
I am building a Google Finance Spread Sheet for my portfolio and curious about a few things.
Generally, I am trying to calculate the monthly returns for each month and essentially outputted the ...
- 1
0
votes
1
answer
157
views
How to make formula based off a withholding table?
I have been attempting to make a formula to easily calculate federal income tax rates. The picture attached is the withholding table that I've transferred onto a sheet. These are the thresholds I am ...
- 1