Pull stock prices & stock data with the GOOGLEFINANCE function in Google Sheets (& Crypto too)

Key Takeaways

in this video i'm going to show you how to use the google finance function in google sheets to pull stock data as well as cryptocurrency prices but i have another video that i've linked below that shows you a lot more about how to pull cryptocurrency prices in this video we're mostly going to stick to pulling stock data so let's go over a few examples of how to use this function then i'll teach you more really important things about the criteria that you can enter and then we'll go over the rest of the examples so the easiest way that you can use the google finance function shown here in the formula bar is to simply indicate a ticker and then that's it and in this case i've typed the ticker symbol into cell a2 for the stock tesla and the symbol is tsla and then i've referred to cell a2 in the google finance function to specify the ticker tsla now since we have not entered an attribute and we're pulling real-time data the google finance function has defaulted to showing the current price or the real time price which at the moment is 905 dollars and so if all you want to do is pull the current price of a stock all you need to do is enter the ticker symbol into the formula and that's it now here off to the right you can see alternate versions of this formula so here you can see what it looks like when you enter the stock symbol directly into the formula instead of using a cell reference i have the symbol tsla entered between quotation marks and this formula would do the exact same thing as the formula that's in cell b2 now the same thing goes for this formula here where we've indicated the attribute price again when pulling real-time data the google finance function is going to default to the attribute price if you do not specify it but if you do specify it this formula would do the same thing as the one in cell b2 now speaking of cell references this makes it really handy to be able to change the stock symbol in your spreadsheet without having to modify the formula and so if i wanted to change tesla to google i would simply change the stock symbol and then the price would change and so this is what makes using cell references very handy and you can use cell references for any of the criteria and the formula so here's another example where we've indicated the attribute market cap where the google finance function is showing us the market cap of tesla and if you're not sure what market cap is or any of the other terms that you hear in this video i've linked the article below which goes into even more detail here's another example of pulling a different type of data or a different attribute in this case we've pulled the volume for the stock tesla which at this time was eleven million six hundred and fifty three thousand six hundred and ten so let's go over an example of pulling historical data real quickly then i'll show you the different attributes and then we'll continue with the examples so here in cell d1 i've entered a formula which is pulling historical data with the google finance function we've indicated the ticker in cell b1 which is the stock tesla again then we type a comma and indicate the attribute which in this case is price and then we indicate the start date and the end date and in this case i've typed the start date and the end date into spreadsheet cells and then i'm referring to those cells within the formula and this is the easiest way to indicate start dates and end dates with the google finance function but i'll show you alternate methods here in just a minute so we're telling google sheets to pull the historical prices for tesla starting at 1-1-2021 and ending at the end of the same year on 12-31-2021 and this formula is going to show us daily prices as you can see here in column d because we have not indicated an interval and the formula will automatically default to a daily interval if you do not specify an interval when pulling historical data with the google finance function so here we have the daily historical prices for tesla now you'll see that it does not start on 1-1 and it starts on 1-4 and this is because it can only show data for trading days where the market was open so this does not include weekends and holidays etc and for each data type with historical data it's going to show two columns the date and then the value that you pulled which again in this case is price now notice that in cell e1 it says close and this is because we're using the price attribute for historical data and when we simply indicate price for historical data the formula is going to default to showing the closing price for each day now off to the right here we have some formulas that are written differently but perform the same function so here in this formula in row 2 we've indicated an interval of daily and again this will do the same thing as the formula where we did not indicate an interval at all because this formula is defaulting to a daily interval and so it would do the same thing if we indicated a daily interval now in this formula in row 3 instead of using cell references to enter the dates into a cell and then refer to those cells with the formula we're simply using the date function to indicate a date in the google finance function and so the date function as you can see here first you indicate the year then the month and then the day and as you can see this is a little bit more tedious and so this is why most people prefer to use cell references instead alright so before we get back to the other examples now here's what i really want to show you and this is the different attributes of the different types of data that you can pull with the google finance function and this data that you're seeing right here is directly from google and again i'm not going to go over each one so you can check out the article and learn at your own pace but you can see here that there's different attributes for real-time data and also attributes for historical data all right so let's quickly go over an example of pulling weekly historical data and this is going to be the same as the last example except we're going to indicate an interval of weekly after indicating the start date and the end date and so as you can see here we've entered the interval weekly between quotation marks and now the dates in the formula are incrementing by seven days rather than by one day so another formula that you can use to combine with the google finance function to indicate start dates and end dates is the today function the today function all by itself simply displays today's date but you can also use this with other formulas like right here where we have used the today function in place of an end date so this is going to tell google sheets to display data with the starting date of 4-9 and an ending date of today so it's always going to display data from the start date up until today now an even more useful way of using the today function with the google finance function is by using subtraction to indicate the start date so just like the last example we've indicated today as the end date but the start date we've used today minus nine and so we're telling google sheets show us all of the data from nine days ago up until today and so this is an incredibly useful way to show all of the most recent data without having to constantly change your start dates and your end dates all right so again there's a lot of different attributes that you can pull but a special one that you can use is the attribute called all which pulls the open price the high price the low price the closing price and the volume all in one single formula and that's the cool thing about it is that it only uses one formula and it only shows one column of dates attached to all of this different data and so it uses less resources and takes up less columns than having to do one formula for each of these different data types so here as you can see on top i've told google sheets to pull all data for the stock gme which is gamestop and i've indicated a starting date of 30 days before today and an ending date of today and one of the cool things that you can do when you have the open high low and close prices is you can make a candlestick chart such as here where we're showing a traditional stock chart that shows the open high low and the close for each day alright so let's go over one more quick example of how to pull cryptocurrency prices with the google finance function and so here in cell a2 i've indicated the symbol btc usd btc is the symbol for bitcoin but we have to indicate a currency pair and so we're indicating bitcoin and u.s dollars and so when we enter this stock symbol or this crypto symbol into the google finance function it's showing us the current price for bitcoin which is almost 39 000 at this time but the number of cryptocurrencies that this formula works with is very limited and there's actually multiple ways full cryptocurrency prices in google sheets and i've linked my video that teaches you how to do exactly that in the description so i hope you like this video if you did please like and subscribe and i hope you have a great day