How to use the GOOGLEFINANCE function in Google Sheets (WITH 5 EXAMPLES)
Ғылым және технология
The GOOGLEFINANCE function allows you to import real-time financial and currency market data straight into Google Sheets.
► Read more about GOOGLEFINANCE's different usages: blog.sheetgo.com/google-sheet...
This function imports data from the Google Finance web application, which provides daily stock prices, news from the currency and financial markets, and other information on market trends. Google Finance can be accessed from the Google menu like all other Google applications, or simply by searching for a stock on Google, which will bring up the Google Finance information relating to that stock. As well as enabling you to track current stocks and shares information, it can also be used to retrieve historical securities data.
____________________________________________
Video breakdown:
00:00 - 00:34: Introduction & syntax
00:35 - 1:40: Example 1: GOOGLEFINANCE listed attributes for AAPL ticker
1:41 - 2:20: Hardcode each attribute into the formula
2:21 - 3:24: Example 2: Obtain historical stock information of a single day
3:25 - 4:31: Example 3: GOOGLEFINANCE output in 1 cell (combine GOOGLEFINANCE with INDEX)
4:32 - 5:11: Example 4: Obtain historical stock information over a period of time
5:12 - 5:27: Analyze stock data with charts
5:28 - 5:59: Example 5: Live currency conversion rates
6:00 - 6:13: Links & tips
____________________________________________
► Visit us: www.sheetgo.com/
► Subscribe to Sheetgo's KZread channel: kzread.info?sub_con...
Any questions? Feel free to comment below!
#Sheetgo #Google #Sheets #functions #GOOGLEFINANCE
Пікірлер: 211
Watched a ton of videos on sheets and stocks and can’t find formula for Week to Date, Month to Date and Qt to date Performance as a percentage….Is this possible? Thanks
Thank you so much .. I would like to get all the trading data from a specific exchange in one sheet . Is that possible ? For example .. The total trading data on nse ( india) on 1 st july 2022
What attributes do you use to return historical income statement and balance sheet information (NI, A, L, E)?
Perfect Video, great examples and extremely useful! Thank you!
@Sheetgo
3 жыл бұрын
Ah that's great to hear, Chad! Thanks!
This is a great function!
Hi, How to get maximum stock high price for the last financial year ? When we run query with dates interval, it gives many rows of data. I just need max for that fin year. Can you please help
Hi , I would like to add African stocks to google search or google finance ,like american stocks on google first page when somebody looks for example for "apple stock ", wait your return!
Is it possible to extract market capitalization and outstanding shares for a historical specific date and not real time?
How can I find the price of a stock on a specific date mentioned in a specific cell? Thanks in advance.
Very informative video. I want to know, if I want latest historical data,which is change regularly, what formula should I use in Google sheet. For example, I have a data from Monday to Friday, I want data of next Monday. Means from Tuesday to Monday, what to do.?
The index function is what I needed to make this work! I'm charting out my whole folio (3 traces at a time) on 10 charts so I needed that
@Sheetgo
2 жыл бұрын
Ah that's great to hear, Nick! Best of luck.
@MarketFund2k
2 жыл бұрын
@@Sheetgo Thanks for the well wishes. I just found out that the historical call won't return today's data. Once I adjusted for that I got my 30 traces for the past 30 days. Now, to automate the trace placement then on to candlesticks!
is there a way to use GoogleFinance to track Commodities like Minneapolis Red Spring Wheat MGEX, Corn CBOT, Soybeans CBOT, Canola off ICE, etc etc?
Very useful, particularly the index tip!
@Sheetgo
2 жыл бұрын
Thank you Walter!
Hi 👋 Can you tell me about specific time currency formulation Ex. 2022.2.17 usdtry
how to do the formula for company start date, that is the date on which data included in the exchange market? Please provide this it may help me to develop my portfolio.
Thanks , can we get stock future price , option chain data with help of google finance
Thank you so much for providing such a simple way. Thanks a lot. I appreciate the way you had did it for use for all.
@Sheetgo
2 жыл бұрын
You're very welcome!! :-)
Hello, is this function available for Malaysian stocks?
is there any attribute available to get stock industry ?
I followed your instructions. However, when I use the "Drag down option", the sequencing is off and I get back mostly "N/A" as the output. Is there a way to switch the sequencing so that it references the ticker symbol for all of the attributes"?
Appreciate your effort. Your third example was the one I ve been searching over the internet to stock price change. But couldn't get it. You just made it that simple and nearly my problem is over. Thanks a lot. Keep up the good work. Even I've purchased a python book to do this 😂😂😂.
@Sheetgo
2 жыл бұрын
Hi! Ah that's awesome to hear! Glad I could be of help :-D have a great day!
Not having any luck getting the yield to work into my sheets what so ever. Any help?
How to get multiple column with index formula? Example: I wanna use [attribute] "all" of =INDEX(GOOGLEFINANCE but I need only high&low column only
Thank you! You're helping me out with my economics thesis project big time
@Sheetgo
2 жыл бұрын
Great to hear Marc! You're welcome. Good luck with the thesis :-)
Hi, Thank you for your wonderful tutorial on how to automate with Google Finance. I was wondering if you know a way to easily extract the 200 day Moving Average into a Google Sheets? Thanks
Is there a formula to get the time the low of day hit? I can't figure out if Google can even pull that time
VERY INFORMATIVE
Excellent! Best regards from Alicante (Spain)
@Sheetgo
2 жыл бұрын
Best regards to you too!
Great video! I it possible to do the same thing but using CUSIP Number instead of the Ticker Symbol?
how to do new dates first. and old date second in google finance can please tell
For some reason googlefinance function does not appear to work for me at all it retrieves no data.
Could you please help me I've got in Cell B6 the ticker BHG Now I wrote down =Googlefinance("JSE:B6") I know this is wrong can you help me fix it please, I do need to specify the exchange JSE unfortunately. Thank you very much and great video.
Thanks for this clear and quick overview 👍
@Sheetgo
Жыл бұрын
You're welcome! 🙂
This is so helpful! thank you for sharing!
@Sheetgo
Жыл бұрын
Awesome to hear! You're welcome 😀
Thank you! Very helpful explanation. I know this is just the tip of the iceberg.
@Sheetgo
8 ай бұрын
Glad it was helpful! For sure, spreadsheets are infinite
Thanks your video, how about the stock index ?
how to get 3 and 6 months price in google sheets for share prices
This video was really cool. I have a question however; is there a way to input other financial information such as revenu? I couldnt find it in the google sheets financial fonction
@Sheetgo
2 жыл бұрын
Hi there! Thanks. You can do that using Wisesheets and Googlefinance together. Wishesheets allows you to bring in other important financial information such as revenue, expenses, gross profit,... It's more elaborately explained in our blog here: blog.sheetgo.com/google-sheets-formulas/googlefinance-formula-google-sheets/
Thank you so much!
@Sheetgo
2 жыл бұрын
You're welcome!
how to get "change %" for n number of day.?
Hi it looks like that you have a lot of expierence with googlefinance. I'm looking for a formula which calculates a 200 day value. Is such a function available. Thank in advance for your support
@Sheetgo
Жыл бұрын
Hi there! Have you seen the example 4 in this video? Maybe that use case solves your task
Amazing. Thank you!
@Sheetgo
2 жыл бұрын
You're welcome, Will!
Thanks for the video. One question is to ask is that how to import "Singapore" and "Japanese" stock data?
@Sheetgo
3 жыл бұрын
You're welcome! It's supposed to work but I've found this thread in the Docs editor help community and seems like not all tickers work for Japanese and/or Singapore stock: support.google.com/docs/thread/50089557/google-sheets-function-googlefinance-tokyo-tyo?hl=en
Very informative and easy to understand.
@Sheetgo
Ай бұрын
I am glad it was informative!
Thanks a lot for your video. My Question is : Can i get minute price or hour price of the stock using these function ?
@Sheetgo
2 жыл бұрын
Hi there! I believe the "latest" price you can get with the Googlefinance function is with a 20 min delay. Here you can find a list of the many different attributes of the function: support.google.com/docs/answer/3093281?hl=en
love you & thankyou for the info
@Sheetgo
2 жыл бұрын
You're welcome!
Hi, how to get book values
Thx for video. Great explanations. Question - can google finance return historical “marketcap” data for a specific date?
@Sheetgo
2 жыл бұрын
Thank you! Yes, there's a "marketcap" attribute. Here's more information: support.google.com/docs/answer/3093281?hl=en
How can I get 2 day's high and low in Google sheet.
Hello, how I can make the price “today “ for a stock that is $0.0012. It I loading $0.0000 example ticker XSPT thank you
@Sheetgo
3 жыл бұрын
Hi there! Perhaps you should change the number formatting of the cell to increase decimal places. You can find that option in Google Sheets' menu.
@Sheetgo
3 жыл бұрын
It's the .00 with an arrow underneath!
Hi, Can you help us sharing the formula of taking out "ALL TIME HIGH PRICE" of any stock..? Please..?
@Sheetgo
3 жыл бұрын
Hi there! You can use this formula to get the highest price over a specified period of time, daily or weekly: =GOOGLEFINANCE("GOOG","high","01/01/2021","07/01/2021","weekly"). Find more details here: blog.sheetgo.com/google-sheets-formulas/googlefinance-formula-google-sheets/
awesome … thank you 👍
@Sheetgo
2 жыл бұрын
You're welcome!
hello, i wanna ask about that formula. Is that can use in WPS and Microsoft Excel ? or in google spreadsheet only ? thx
@Sheetgo
2 жыл бұрын
Hi Denni! This is a specific Google Sheets function.
Is it possible to use the function to find historical values of currency?
@Sheetgo
3 жыл бұрын
Hi Ben! Yes, you simply have to add the start and end date in your parameters. You can find how in our blog post: blog.sheetgo.com/google-sheets-formulas/googlefinance-formula-google-sheets/
great video, just one more about dividend. what is the funtion to track dividend ? thanks
Thanks for ur nice explaination.how to get buy and sell syntax.
Great video, well done
@Sheetgo
2 жыл бұрын
Thank you, John!
thanks, great job.
@Sheetgo
2 жыл бұрын
Thank you, Antonio!
Thx for this video. Need to know how to add Dividend yiled from google finance to a spreedsheet
Very nice.
Hello ...I'm using office 365 version ...I'm not able to find Googlegfinance function my in spreadsheet...what could be the problem...plese help... PS: i know you have shown it on Google sheets..but is it possible to use Googlegfinance function in the spreadsheet...???
@Sheetgo
Жыл бұрын
Hi Suneel! That's indeed the issue. You can only find Googlefinance on Google Sheets, not on Excel spreadsheets.
Nice job!
@Sheetgo
2 жыл бұрын
Thank you!
I saw my dad using this and he had small charts for each stock. How does this work?
@Sheetgo
3 жыл бұрын
Hi Josh! Your dad could have used the sparkline formula to get a visual look at how the stocks are evolving.
Hi, how can I insert the price of an entire index, for example the NASDAQ?
@Sheetgo
3 жыл бұрын
Hi Luca! You can use =GOOGLEFINANCE("NASDAQ:GOOG","price") as the formula. But as you can tell, this is specific per security in the index.
Hey can you make this same video for crypro on Google finance? Thx =)
I was looking at the high (or low) of a stock for the last three months. How do I do it?
@Sheetgo
2 жыл бұрын
Hi there! This is the formula you can use to get the high (or low) prices per day of the last 3 months: =googlefinance("aapl","high","2021/07/19","2021/10/19"). Note that this gives you the high price per day. You can add "weekly" to the end of the formula to get the highest price per week from the last 3 months: =googlefinance("aapl","high","2021/07/19","2021/10/19","weekly"). Read more about how to obtain historical stock information here: blog.sheetgo.com/google-sheets-formulas/googlefinance-formula-google-sheets/
HEY THANK YOU FOR YOUR VIDEO, HOW TO FIND YESTERDAY HIGH PRICE WITHOUT USING DATE?
When I type google finance.. They dont have this function. So do i have to add it separately???
@Sheetgo
3 жыл бұрын
Hi there! Type =GOOGLEFINANCE directly into a Google Sheets cell and it should show up! :-)
@snigdhajain8351
3 жыл бұрын
@@Sheetgo yes i did that idk why but it didn’t
Thanks a lot mam ❤️
@Sheetgo
2 жыл бұрын
You're welcome!
HI SHEET GO. THANK YOU SO MUCH FOR SUCH A SIMPLE AND EFFECTIVE EXPLANATION. IS THERE ANY POSSIBLE WAY TO GET ONLY THE CLOSE PRICE OF THE LAST DAY OF THE MONTH?
@Sheetgo
2 жыл бұрын
Hi there! Great to hear the explanation serves you well🙂yes, use the "close" attribute to get the closing price for the specified date(s). Check out all attributes in our post here: blog.sheetgo.com/google-sheets-formulas/sort-formula-google-sheets/
How to calculate last 5 days return in Percentage please
Nice explanation
@Sheetgo
3 жыл бұрын
Thank you!
Thanks for this video. But what if I want hourly data not daily or weekly or monthly; How do I go about that?
@Sheetgo
12 күн бұрын
Hi there, thanks for your query. Here is more information about automation triggers: support.sheetgo.com/en/articles/8529741-what-are-automation-triggers-in-your-workflows
Very helpful video. Thank you. How to get the YTD, 5 YTD and 10YTD please
@Sheetgo
2 жыл бұрын
Thank you! I've found something online that might help you: itectec.com/webapp/google-sheets-how-to-get-a-ytd-dynamically-in-google-sheets-google-finance/
Is the historical price data accurate? Other than the weekends and holidays, it seems as if some of data is omitted. Why is that so? Thx
@Sheetgo
3 жыл бұрын
Hi Jabari! You can choose to have the data daily, weekly, or monthly over a historical period of time. If there's some data missing, I don't know why exactly. You can read more about the function and its attributes and syntax here: blog.sheetgo.com/google-sheets-formulas/googlefinance-formula-google-sheets/
@jackthebeanstalkdelohio5146
3 жыл бұрын
@@Sheetgo Thx for responding, my friend. I was attempting to pull up the entire month of January 2015 of SPX Flow Inc. I saw the opening price which began on the 2nd. What I didn't see was the closing of that month which should have been on the 30th, the last day of that month that the market was opened for that month of January 2015. I assume it is one or the other: I am looking at it wrong or such data is omitted. What I do not know. Totally confused. Please help if u can. Thx
@jackthebeanstalkdelohio5146
3 жыл бұрын
Read the link u gave me. Very insightful and helpful. Thanks, again. Will add to my to do list to remember and use it. Thx
@Sheetgo
3 жыл бұрын
@@jackthebeanstalkdelohio5146 Perfect!
If i want to get monthly highest high data for specific time period
Hello, trying to get a quote in Canadian funds, have been trying diffrent methods but no luck.
@Sheetgo
3 жыл бұрын
Hi Eric! Which attribute are you trying to find? If you want yesterday's closing price of the Canadian stock market you simply need to use this formula: =GOOGLEFINANCE("TSX","closeyest") for example. Let me know if that helps!
@richardg8135
3 жыл бұрын
Just put TSE:Ticker AS a example TSE:TD for TD bank
@erickeeble1401
3 жыл бұрын
@@Sheetgo Thanks Sheetgo will try it today
@erickeeble1401
3 жыл бұрын
@@richardg8135 Ok, Thanks
awesome
Very usefull....Im from India.......
@Sheetgo
Жыл бұрын
Thank you!
This was great .I have one doubt .Is there a way to get highest price in last 6 months . I am trying to arrange all the stocks in rows and wanted to compare 52 week high and last 6 months high .
@Sheetgo
3 жыл бұрын
Hi there! Thanks :-) yes, that's possible. You can use the historical attribute "high", and define your start and end date to get to the highest price during a period of 6 months. You can find more information about how to compose the function with the right attributes here: blog.sheetgo.com/google-sheets-formulas/googlefinance-formula-google-sheets/
@kisalayjoshi3027
3 жыл бұрын
@@Sheetgo Thanks a lot for replying. Awsum blog there . The way you suggested is resulting in more than one value .That is all the historic high data in that period .I was trying to get a single value for 6 months .This is what I tried =GOOGLEFINANCE("NSE:"&C2,"High",TODAY(),TODAY()-180)
@Sheetgo
3 жыл бұрын
@@kisalayjoshi3027 Ok, I got you Joshi. I think in this case, what you could do is let the GOOGLEFINANCE function return all those values, and then simply use the MAX function to get the highest price out of that list.
@kisalayjoshi3027
3 жыл бұрын
@@Sheetgo thanks for the reply . I tried that as well but with Max I also had to use Index and whoosh that saved me ☺️
@Sheetgo
3 жыл бұрын
@@kisalayjoshi3027 Ah perfect! Good to hear :-D
I am unable to fetch beta value
Madam you are so beautiful and amazing content. Love from India 🇮🇳
Do google have ma?
How to get previous month open high low close of any stock ma'am ?
@Sheetgo
Жыл бұрын
Hi Rajesh! Under title "2. Obtain historical stock data..." in our article here blog.sheetgo.com/google-sheets-formulas/googlefinance-formula-google-sheets/ you can find how to use attribute "start_date" and see last month's open, high, or close rates of any given stock. Hope this helps.
Lovely
=GOOGLEFINANCE("GAIL","High",DATE(2021,7,15)) I DID FOR THIS BUT NOT GETTING THE VALUE WHICH I REQUIRED
how to change it to red color if the number is negative?
@Sheetgo
3 жыл бұрын
Hi Holly! You can use Google Sheets' conditional formatting options for that. Simply select the cells/column where your numbers are and right-click > go to Conditional formatting and select "Format cells if.." > Less than > 0.
Is there a way to bring in ATR?
@Sheetgo
11 ай бұрын
Hey there, thanks for your question. The GOOGLE FINANCE formula brings raw data into you spreadsheet but you can use this data to calculate ATR with other formulas. You can use Sheetgo to append the daily data provided by Google finance and keep a track of historical data.
Great video, Is it also possible to add more information in the sheet. For example Short % of float or Book Value per share (mrg). Greetings from the Netherlands
@Sheetgo
2 жыл бұрын
Hi Michael! Thanks a lot! I'm not sure you can add that specific data via Googlefinance, but take a look at all the attributes here: blog.sheetgo.com/google-sheets-formulas/googlefinance-formula-google-sheets/ to see if you can find what you're looking for! Have a great day!
@michaeldhont3805
2 жыл бұрын
@@Sheetgo many thanks for your help
I can't find GoogleFinance in my excel, how can I add this?
@Sheetgo
Жыл бұрын
Hey there! This formula works in Google Sheets, have you tried it using this file type?
Nice Video. Can we import Stock prices based on time of the day?
@Sheetgo
2 жыл бұрын
Hi Manoj! There are different attributes to specify closing price, highest/lowest price per specific day etc but not time-specific. Check out the attributes here: support.google.com/docs/answer/3093281?hl=en
So where is the googlefinance function? should i build it on my own?
@Sheetgo
Жыл бұрын
Hey there, the GOOGLEFINANCE function can be used in Google Sheets. You would have to open a new or existing spreadsheet and test it there.
MAM, how we can get all time high price, can you please share the formula.
@Sheetgo
2 жыл бұрын
Hi Avinash! The formula would look a bit like this for the highest price of the last year: =INDEX(GOOGLEFINANCE("TICKER,"High",TODAY(),TODAY()-365))
Hi, is there attribute for Dividend Yield?
@Sheetgo
2 жыл бұрын
Hi there! There is the "yieldpct" attribute. Here's more information on it: support.google.com/docs/answer/3093281?hl=en
@mountvision58
2 жыл бұрын
@@Sheetgo @Sheetgo Hi! Thanks for the reply. I have tried the attibute but it's only for mutual bonds. Not applicable for stocks.
and where can i find the exact ticker?
@Sheetgo
3 жыл бұрын
Hi again Luca :-) there isn't a comprehensive list of all tickers somewhere, but you can simply Google the ones you need and you should find the ticker symbol!
Can i upload the index, i.e. NYSE?
@Sheetgo
3 жыл бұрын
Hi Luca! Yes, it's best to specify the exchange symbol for accurate results. So you could type =GOOGLEFINANCE("NYSE:DIS","price") for example.
@marekhamsik17l
3 жыл бұрын
@@Sheetgo So good, but where i find the ticker of other index? i.e. Dax 30?
doesn't work for me, requires to add NYSE or NASDAQ in front of the ticker
I have MS Office 2016. This function is not available here!!
@Sheetgo
Жыл бұрын
Hey Leon, this formula works for Google Sheets! You can bring the data to a Google Sheet and with Sheetgo transfer it to your excel file if its stored online. Go to www.sheetgo.com to learn more.
THANK YOU! i nearly gave up when It kept giving me multiple cells
@Sheetgo
2 жыл бұрын
You're welcome Joshua!! :-)
How to get the price at a certain 'time' of the day
@Sheetgo
3 жыл бұрын
Hi Richard! The function will always return real-time stock data, but with a 20min delay. Here you have more information: blog.sheetgo.com/google-sheets-formulas/googlefinance-formula-google-sheets/