How to Automatically Integrate Financial Data with Google Sheets

Use a free add on to access data financial data from around the world.

In this video i’m going to be showing you how to automatically download stock price data and other financial information into a google sheet and so then you can perform whatever other analyzes you want to do in google sheets so to get started you’re going to want to log into your google sheets and then you know start a blank worksheet and then i’m going to show you

How to do all this right after a word from one of our sponsors interest in learning how to create trading algorithms with python let looming wealth help with their algorithmic trading course in this course you’re going to learn how to create bots that automatically trade for you implement and use technical and financial metrics and use machine learning to create

Unique trading algorithms you can learn at your own pace or you can join the live classes for collaborative experience learn more about living well by following the link below and get 15 off any of their trading courses okay so to get started what we’re going to do is go to extensions in our google sheet and i’m going to go to add-ons and then get add-ons and then

I’m going to search for eod and then the one i’m looking for is this one end of day historical data and i’m going to select that and install it okay once it’s done we can just close this window okay so if you don’t see this little icon on the right menu here google sheets financial add-in then just start a new sheet and it should show up there okay so we are going

To just click on that and to get started what you need to do is first either register an account here or if you already have an account log in so you can get your api key all right i’ve already gone ahead and done that and so then i’m just gonna go right from there to get data so there’s another option here where you can upgrade your data package right i’m just

Assuming that you’re using the free package and if that doesn’t eventually meet your needs then you can upgrade to one of their paid data feeds i’ll leave a link in the video so you can look at the different packages that are available and actually get you a discount on that data feed if you need one all right so i’m going to click on get data and then what we’re

Going to be presented with is all the different feeds that are available to us all right so we’ll just start off doing something simple and we’ll get some end of day price data and i will get it for apple here and then i can just sort of decide how far back in history i want to go the data feed goes back to 1985. all right and then if you don’t want daily you can

Choose weekly or monthly by changing one of these options okay so once that’s done if you want we can get a chart but the the google charts aren’t all that exciting i will i will get it just so we can see what it looks like and i click get and it creates a new sheet and yep we get the data just that quickly and there’s that like i said sort of unexciting chart

And yeah you could play around with it to adjust the the y-axis here so that at least the we don’t have all this white space all right but i’m just going to leave it at that obviously right we could start adding whatever data transformations we wanted here as columns in this spreadsheet all right i’m going to go back in okay and again click on data here and yeah

They have something for daily data and if you want you can get it down to one minute increments with the free account it’s going to be a day delayed so you’ll get you know whatever yesterday’s intraday trading prices were it works the same way as the daily data does though so i’m not going to demonstrate that we might as well take a quick look at fundamental data

Though so you can see what’s available there and i’m going to again just stick with apple here and we’ll get that and you can see it’s pretty quick the connection comes back right away and then okay they get all sorts of fundamental data points here that you can again start doing whatever analyzes you want on all right one nice thing they have well they have a lot

Of nice things right but we can download the bulk data for a lot of symbols so if you have a list of symbols you can kind of feed those in here right otherwise it works pretty much the same way right we can specify a specific exchange we can get etf data all right and then they have options data all right so a lot of these api providers don’t give you options data

And let’s just take a quick look there and what i’ll do is i’ll stick in apple again all right and then from is essentially what options you want right so if i just want say all the options for october here and i want to go out to the regular expiration i can set in that data slice and then i can look at how these things traded and i’ll just go back to last friday

And stop at today all right so we should see three weekly options here and then the trading data at least from last friday so let’s take a look at that all right so again comes back pretty quickly and then this also puts in the when it’s done it puts in the subtotal so the outline format all right so there’s the three weekly option expirations and then if you

Want to click on the plus it expands it out and you can see okay what the strike is whether it’s a collar it’s a put when the last trade was and and that sort of stuff and then the bid and the ask and you also get some implied volatilities here all right so some high level statistics are available too right the put call ratio and that kind of thing okay and then

Another nice feature they have is this screener and so let’s just take a quick look at that and again you can just use it to download all the symbols in an exchange all right and then they have a screener and there’s lots of options here all right but i’m just going to hit this 50-day low as my screener all right but if you want to get a little bit more involved

We can start adding filters right look for a specific market capitalization look for a specific yield and you can add in as many filters here as you want okay so let’s just see what happens when we take a 50-day low and so yep it goes out and it quickly gets that data okay so we quickly get the data where all these securities should be hitting their 50-day lows

Or are below their 50-day lows and then we can see okay that some of them are not traded in dollars so you can filter out whatever whatever stock exchange you’re interested in and that’s that’s another thing about end-to-day historical data they pretty much have all of the major stock markets from around the world all right over 60 of them and a lot of the api

Providers just give you u.s data okay so hopefully that helps you get started with end of day historical data using their api to download and then manipulate data however you like in a google sheet

Transcribed from video
How to Automatically Integrate Financial Data with Google Sheets By Matt Macarty