Use the Google Finance Function in Google Sheets

With the Google Finance function, you can easily extract stock ticker information, display it on the worksheet, and then chart as necessary.

[@sureshkumar-gy1ye]

Thanks a lot for your step by step instruction.

[@rawnsleyquilapio9688]

This is GOLD. Thank you Ralph for the clear instructions

[@larazachusca4577]

¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de estructura del sistema nervioso??? estructura del sistema nervioso??? estructura del sistema nervioso???estructura del sistema nervioso???estructura del sistema nervioso??? estructura del sistema nervioso??? ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de estructura del sistema nervioso???estructura del sistema nervioso???estructura del sistema nervioso??? ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de estructura del sistema nervioso??? estructura del sistema nervioso???¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de estructura del sistema nervioso??? ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de ¿Porque no hay finance de estructura del sistema nervioso??? estructura del sistema nervioso??? estructura d estructura del sistema nervioso???

[@sonukaushik]

Fantastic stuff... Thanks Ralph! Very to the point guides... Loved it

[@DaveMarnier]

Very helpful. Thank you Ralph for always empowering and helping us to be better. Much appreciated!

[@David-jv7ot]

OMG you have changed my finacial world

[@sonukaushik]

Use of headers as attributes... Is genius work ツ

[@sastindxa7]

Damn..google are the best. No doubt why the company is keep growing👌👌 Thank brother for this beautiful info 👍👍

[@chrisstef2097]

This was very helpful! I am going to use this to monitor my portfolio and upload a video showing my open positions on my channel to get your feedback :) !

[@georgefenwick5884]

Great info. How can I sort the historical data descending instead of ascending? I've tried and tried...

[@amoghpatki5596]

hey ralph, is there any way i can restrict the formula from giving me the dates (i just want the prices)

[@plopezmartin]

You should wrap it in an INDEX call, i.e. INDEX(GOOGLEFINANCE(arguments;..;...);2;2)

[@marcosfuerte8739]

Yes!! I love this video, thanks for the upload! 👍🏼

[@luen05]

That's gold, Jerry! Gold!

[@jamestran4438]

very nice and clear instruction, thank you so much!

[@amzadhossain4297]

Is it working for another country?Like Bangladesh...

[@KSivakumar27]

Very helpful. However, how to get on monthly interval data as it supports only daily or weekly. For SIP investment, monthly is quiet normal. kindly

[@discoverfloridatoday]

Thank you Ralph. I like videos that get right to the point. I just subbed!!!!

[@myk2004]

Very helpful! Thank you!

[@brenton9492]

"making sure its absolute" @2:36. What does this mean, and more importantly what is the key to preform this?

[@zaydevans2077]

Also couldn’t get this

[@brenton9492]

@@zaydevans2077 I think it’s F3 or F4 for the absolute key.

[@user-kt8nj8mv2e]

Ralph what is the formula for just one historical day? I just want my google sheet to record the HIGH,LOW,PRICEOPEN,VOLUME for a stock, say FCEL JAN 1 2020. When I enter the date and ticker in my cell I want it to fetch it automatically for me. I dont need all the daily or weekly or is it best to just goto a chart and record them manually.

[@0069yj]

Thanks for sharing, but do you have an idea to connect google sheet ticker with other country stock exchanges?... I

[@steveo44]

Thanks so much. Really helpful 👍

[@johanneshvitfeldt1090]

it does not work... just says error

[@Vincent-ds3kc]

Do you know if the "price" updates every closing?

[@sharkman__adventures773]

Hello, I am trying to find a way to pull minute to minute stock pricing on. For example, the minute by minute stock pricing of Apple on May 13th. Is that possible? Thanks

[@GilCuriously]

Thanks Ralph. Do you know how I can fetch the previous 12 weeks high instead of the 52 weeks high?

[@waleednashed]

Thanks for sharing this, I wonder if there is any recognized symbol for gold. I used XAU but didn't work.

[@Anonimo-kh7jg]

I could not use it

[@jackthebeanstalkdelohio5146]

Is the historical price data accurate? Other than the weekends and holidays, it seems as if such data is omitted

[@JimTheProducer]

Do you know how to call expense ratio data? Whenever I do the formula exactly like it says in the HELP section with their format it returns #N/A. Im wondering if googlefinance is even able to draw expense ratios.

[@djl8710]

Thanks man this is great!

[@wokeclub1844]

Thanks. But does the price vary in real time? If not, can it be done so any other way?

[@brandonlane2355]

Is there a way to add option contracts? Have a list of the contracts for a stock?

[@familydinner1]

OMG thank you so much!

[@VishalGupta-gt7ws]

How to get close price of a share

[@antoniorioscortes2766]

for BTCUSD (Bitcoin)?, for example?

[@SixMinutesSmarter]

I coudn't get bitcoin on google finance, however BTCUSD does work for Excel stock data and the =stockhistory function in excel. These prices aren't real time though, so if you're doing a lot of trading, best to go with your exchange interface.

[@carsonsugden9807]

Not sure why it didn't work for me

[@arjunoberoi7838]

Hi, thanks for the vid. How do I get the price of previous 2 sessions or day before yesterday? I know it doesn't make sense but it's quite useful for Mutual Fund analysis. Basically tracking the change (yesterday vs day before yesterday). Example: MUTF_IN:MIRA_ASSE_EMER_1T9TCQO (This mutual fund only update change one day later, so it's hard to see the change in today vs yesterday in Google Sheet). I hope you get the query.

[@SixMinutesSmarter]

check out kzbin.info/www/bejne/aKPcqKiaar56btE when you get a moment.

[@arjunoberoi7838]

@@SixMinutesSmarter it worked. Thanks for the video.

[@paulchristensen6270]

Ralph, this is terrific, but what's the secret to getting preferred shares in the spreadsheet? I've been using a hyphen as the preferred symbol, ie. CEQP-R, which Google can't find.

[@JDLearningTube]

How to find INDIAN NSE stock beta? beta formula not work.... any solution....?? Thanks.

[@SixMinutesSmarter]

Not with google finance. However Excel seems to work. I used XNSE:ACC for both importing stock data on Excel 365 and with the =stockhistory function on Excel 2019. I was able to get the beta (1.13) for ACC limited on Excel 365.

[@gabrielecearini1871]

Nice video, How I can get for have a close quotation forex market each candle for 1H thanks

[@BobbyWealth]

🤚🏽 Wssup!! Awesome content you put out! Lets stay in touch! 🔂

[@bcassano]

Thanks for such an informative and succinct video!! Would you by chance know if I could create a Google Sheet with a column that gives a Y/N for whether options are available for trading? I see you can add options prices but I would rather just know if options are available or not.

[@SixMinutesSmarter]

I don't see option prices as being available in Google Finance. There are a lot of optional attributes you can put in the googlefinance function though. I saw an reddit post from a couple years ago that seemed to indicate option info may have been available, but it isn't now. If you do see results for that, I'd likely go with an IF function with an OR function inside. IF OR(put>0,call>0), "Y", "N" So if the put or the call price is > 0 then there must be options available and you could display a "Y".

[@bcassano]

@@SixMinutesSmarter hey man, just seeing this a couple of weeks later. thank you for the thorough response. I like the IF OR idea but i would have to be able to pull up a price (or potential price) of a specific contract. I was thinking I could choose a popular expiration and a relatively low strike price and somehow construct the option ticker, but I still need to find a place to pull that data, then I could use your IF OR idea. That said, a work around i have found so far involves a list of stocks i check against all new options listed by the OCC. It's clunky but it's a few minutes 5 times a week. I'll keep you updated if I find out anything new. thanks again.

[@plopezmartin]

Hi Ralph, I noticed that when using GOOGLEFINANCE historical mode (weekly), dates are not always aligned to end of week (fridays). For example, in your video I see you have data for 4/13/2017, however that falls on a Thursday, not Friday. Any idea if this is a bug?

[@plopezmartin]

I just realized the markets would have been closed on 4/14/2007 (good friday)

[@santoshprasad7395]

very nyc file , i have a query that , how to find delevery percentage of shares?

[@farzadedulkaka922]

Wonderful

[@kevinwinarya1111]

nice man!!

[@rogjerr]

Anybody know how to get yearly price?

[@acerchromebook3003]

how do you get the price for a certain date in another cell? =GOOGLEFINANCE(A1,"price",A2) where A1 is the symbol and A2 in the date

[@SixMinutesSmarter]

Hi Acer, I made a short video at kzbin.info/www/bejne/iZO8d6xpja2YetE to address this, but realize now that I didn't target your question exactly. Good news is you're on the right track. With the function written as =GOOGLEFINANCE($A$3,"close",B5,B6) I'll get the historical data for trading days for the stock (in cell A3) between the starting date in B5 up to the end date in B6. If B6 is in the future? No problem, only historical (trading dates before today) will be shown.

[@acerchromebook3003]

@@SixMinutesSmarter I tried that but it doesn't work when B5 and B6 (in your example) are the same date. I was trying to get Sheets to display the price on a specific date from a different cell. Thanks anyway.

[@SixMinutesSmarter]

So for a single date, you won't have two dates in the function. If you just provide 1 date, you'll get the price for that 1 date. If the 1 date is in a cell, then use a cell reference for that that 1 date.

[@acerchromebook3003]

@@SixMinutesSmarter I tried =GOOGLEFINANCE(A1, "price", C1) and =GOOGLEFINANCE(A1, "close", C1) where A1 is the stock and C1 is the date, but I get the #REF! error

[@SixMinutesSmarter]

I just tried =GOOGLEFINANCE(A1,"price",C1) and it was working. So, make sure the stock you have in A1 is legit. Ticker symbols for exchange traded funds may not work, or for markets other than NYSE and NASDAQ. Also make sure the date is a legit trading day, although I just entered 1/3/21 as my date in C1 and it still gave me the price for 1/4/21. Make sure the date is in a standard date format. Out of curiosity, I used the syntax where you specify the exchange market. So I used =GOOGLEFINANCE(concat("NYSE:",A1),"price",C1) and it worked well for any New York Stock Exchange stock that I put into A1. But did NOT work, understandably, when I put GOOG, a NASDAQ stock. Wait a sec...I think I know your problem. You're putting the googlefinance function in cell B1, between the ticker A1 and the date C1, right? There's not enough room for it to build the historical data. I just tried that and got the REF error. So, try this but put your googlefinance function off to the side, in D1 or something so that's is now surrounded by filled in cells. Good luck.

[@AxiomPinstripe]

How to get dividend per share info from googlefinance...i tryed 3 different things it doesnt work and when i follow the instructions on how to use the attributes i get error wrong parameter for the attribute name...help anyone

[@memypencil1]

I have the same question, e.g. =GOOGLEFINANCE(B4,"incomedividend")

[@DefinitionArt]

awesome video thanks! is there a reason why this wouldn't work on certain stocks like vtsax, fskax?

[@SixMinutesSmarter]

five characters like that so those are likely mutual funds or exchange traded funds.

[@DefinitionArt]

@@SixMinutesSmarter ah that makes sense, thank you!

[@mehwhyausername1]

get rid of date column?

[@mehwhyausername1]

I figured it out: =INDEX(GOOGLEFINANCE(A1, "price","2/20/2020",today(), "daily"),,2)

[@rishikeshmishra5953]

Hey Ralph, is it auto-updated or we have to refresh to update

[@arjunoberoi7838]

Auto-updated, with an interval of few mins.

[@robertbarry1792]

How can we get Ex dividend data?

[@paulmartins5750]

What do you mean - "press f4" ?

[@SixMinutesSmarter]

On a PC, F4, function 4, is the shortcut key for relative/absolute references in Excel (and Google Sheets). I think it's Command + T on the Mac.

[@Urban_Elegant]

Pe not working

[@ebadrinath7623]

Formula for today's closing price

[@md.kamalhossain332]

Thank you, Ralph Phillips, for your nice video, to retrieve the data on a google sheet. But I'm facing problems to generate data. I tried for "pe", "eps", "high52" etc for a time period of e.g. 5 years, but I could not do that. I put the formula as you suggested for these variables. Please sir give me the way to solve the problems.

[@samasaishiva8878]

Bakwas hey