Use R to Get Yahoo Finance Options Data (to use in Google Sheets or Excel)

This video is an update to the Yahoo Finance Options video which used the GoogleSheets function IMPORTXML to get data.

Hi guys so this video is in response to a lot of the comments i’ve been seeing about this yahoo options tracker not working anymore and we can clearly see that with this tesla output that it’s returning n a saying that the web page is empty that initially made me think that the yahoo options page was being loaded with the javascript similar to what we saw with

The nasdaq dividend data where they updated their website and the xml function at least in google sheets can’t access any tags when it’s created by javascript but when i tried other contracts i was able to get back some information but as uh nick sparrow here was saying that it returns back zero or just some other data and yeah for two different contracts that

Are returning back it returns back uh the bid and ask a zero and then n a for the open interest but if you go to the contract itself uh you can see that there is open interest and different bid and s from zero so that makes me think that yahoo is doing some other kind of processing on the data and that this xml import xml function isn’t going to work anymore

In google sheets so the method i have for it is to use our code to do the import function and that’s what’s here i have the our code shared with you guys so you can just click on that and copy it if you have our studio running or on your machine you can download it it’s free and i’ll just step through the code with you guys so you can see what’s going on let

Me just clear out some of this stuff from before so this is the r uh studio and the code that i have in that file for you and i’ll just describe what it’s doing here i have comments throughout the code with the double hash and this piece right here is code that you can uncomment out if you need to run it this is just to install the packages if you don’t have

Them installed already so what it does it takes these two libraries to pretty much do that import xml function this first variable is this contract list and what it is it’s essentially this portion of the original tracker just as a variable so you’ll list all the contracts that you want to get information for here and then these two variables are just to create

And aggregate the data later on when we run the loop and here’s a loop so this takes those contracts in that contract list and one by one it just creates the same sort of structure that we’re doing in the school sheets so that first piece is going to create this dynamic url with the contract names and that’s what this is this yurl variable is just creating that

String with the contract names inputted in the string and then this variable right here is using the the string we just created and this curl function to make the request uh or to manage the request for us and then the read html function is just pulling all the information from that page so all the html from that page and then saving it in this page variable

And that’s essentially what this import xml function was doing it was making requests to the page and then taking everything from there what we do after that is again similar what to the html function or what the xml function is doing by targeting the table rows and table data that’s what this piece was doing in the xpath and we’re able to do it here as well so

Once we get all that that’s all the table data information i did notice some of the comments people were asking for the close price which isn’t in the table it’s actually above the table so that’s what this piece is for it targets that specific xpath and gets that piece of information and then after it gets those two pieces of information the stuff in the table

Here and then this piece right here uh it then joins all that together in in this y underscore data variable and then adds it to that data points list that i mentioned earlier and that’s just for one contract after it’s finished with one contract it iterates again and then goes to the next contract so once that’s done and it’s gone through all the contracts and

Then just starts to create the data frame which is very similar to a spreadsheet and then after it does that it i define some headers here that i know are going to be numbers so these are all the numbers in that data and i format them as numbers and then for where the where the header is open interest we have to just remove the k and then format that specifically

To thousands so in that instance you can see here where there is a k it just takes it and formats it to the appropriate number or otherwise it turns it into a regular number and after it does that uh it then saves this file to your local drive as this specific name uh you could change this to whatever you want it to be but if you already have a file with this

Name that’s a csv file uh it’s going to overwrite it so just make sure that you change that if you need to you could also uh have it put to a specific folder with a specific name with this code here i’ll leave a link in the description for this piece as well and i think it’s very helpful uh if you wanted to make it dynamic say if you want to save it to a different

Location maybe like a dropbox that you have linked you can do that as well but essentially that’s what it does it outputs that csv file so let me just run it so you can see okay so it just ran and the output again is this csv file which is based off of this data table so if i go up here into this object explorer and click on data table i can see what the output

Would will be and already we can see that it’s working so we’re getting information for tesla for that tesla put whereas the original one was giving back n a so this seems to this shows that there’s limitations to the import xml function and we can also see that we’re getting bid and ask information as well as open interest information which this wasn’t giving

Us the correct bid and ask in the open interest and we could also see that this portion here for this specific contract where it was uh giving back in a different format this 1.6 representation there it’s being formatted appropriately so that is it where you can get now a csv file instead of doing the request by itself so if you wanted to work with this in

In excel you could totally now just do it and just stop there you don’t have to throw this in google sheets anymore but if you wanted to i will show you an option but i just want to make sure that say some things about it because it is limited based on how google updates these requests for data so if you go to this tab that i have here this r code and next

To that you can still see here if you still want it in google sheets what we need to do is one have a google drive so if you have google drive that’s where you want to upload the csv file without that was outputted by this code so i’m going to upload the file that we just outputted from that r code let’s go to new upload file and this this is the file that we

Just produced okay so after it’s loaded you want to right click on it and go to get link and you want to change it from restricted to anyone with link then copy that click done make sure to click done and then go back to the folder and here you’re going to input that google link with which is copied but you want to remove everything before this d and after the

View so that gives you the document id so this is the document id and what this does it just creates a downloadable link for that for that document id so now once we have that downloadable link we can use this function called import data that google has and just reference the cell that has the link in this case it was g3 and click enter and it seems to work so

Pretty much we’re creating a link to that file that’s in our google drive and this is a way we can still work with it in google drive if we wanted to there are some limitations here though with this file or with this function that are outlined here all these functions import data import feed import xml take an hour to refresh so if you just made a change so you

Just uploaded that file and you wanted to add several more uh and you run the code re-upload this file it might take an hour for this to reload even if you uh take out this and you know refresh it you won’t you might get an error saying that it can’t fetch the file or that you’ll just still see the same old data and you won’t see like your updated data so for

Example let me just add a couple contracts the one we just ran so here let’s add a couple more and one for baba remember to put the comma if you’re adding in the close it in quotes i’m just going to get rid of these variables here that were just created and run it again so again this saves over the file i just created and you can see that the data table has the

Two that we just added now if i go back to my google drive and i click new upload file and then upload the same file it’s just going to overwrite yep got it and if i go back here you would think it would load but it’s not uh if i if you think that clicking refresh is going to make it load we’ll see okay you can still see that it’s the old information looks

Like it’s loading something one way i thought you can get around it was by breaking the link and then adding it back again and okay that seems to work this time around but i haven’t encountered instances where that doesn’t work so if you still want to do it in google sheets it seems like you can use the import data function and do that method of just taking out

The equal signs and breaking the link and just have it reload again since you’re you know since the id doesn’t seem to change when you upload multiple versions of this but i have experienced instances where this doesn’t give back the information and it’s probably due to the limitations of it being having an hour to load so just keep that in mind if you’re going

To be working with it in google sheets with this method otherwise like i was saying before once you run this code uh since it’s outputting out as a csv file you can work directly with it in excel if you wanted to so that’s it i know it’s not the best solution and it i know it’s not what is easiest to manage and something like this but it is a solution that i

Hope helps with you guys thanks for watching and thanks for all your support and for subscribing it’s really helpful and thanks for all the comments guys talk to you later

Transcribed from video
Use R to Get Yahoo Finance Options Data (to use in Google Sheets or Excel) By danesh j