1

I want to import the data from options tab in yahoo finance to my google sheet. It is this tables: Picture with the table I want to import First of all, you can see a box with different dates that when you change the date the URL change. The difference between the URLS is that you need to sum to the previous number 604800 and then you get the correct URL. Well if you use Excel, you can download the data (is in the table 3 the ones I want) without any issue, but you need to be changing the website manually every time that the date change. So I was thinking to use the ImportXML or ImportHTML of google sheet. For example if you use in the main page: https://finance.yahoo.com/quote/VZ?p=VZ This formula: =importXML("https://finance.yahoo.com/quote/VZ?p=VZ";"//*[@id='quote-header-info']/div[3]/div[1]/div/span[1]") You will get the value of the stock in that moment but if you change the website url for the one of the options: =importXML("https://finance.yahoo.com/quote/VZ/options?date=1618531200&p=VZ";"//*[@id='quote-header-info']/div[3]/div[1]/div/span[1]") You got a NA value... even if the value is there and the HTML code of the website is the same... and this for me does no make sense.

So I do not know how I should do to can download the data from the tab "options", and is frustrating cause it must be possible as it is really "simple" to get it in Excel.

Some suggestion?

Rubén
  • 34,714
  • 9
  • 70
  • 166
Rubén de la Iglesia
  • 11
  • 1
  • 2
  • Both the formulas you provided are resulting in `#N/A` in my case. Can you provide a sample sheet in which the first formula is used to successfully import the data? – Iamblichus Apr 14 '21 at 07:49
  • Hi, find here the google sheet + other options I tried: https://docs.google.com/spreadsheets/d/1yCY2uIKdsiaVzxNG9KtREsA2I8Wk_uE-dGScmO6_068/edit?usp=sharing Thanks! – Rubén de la Iglesia Apr 14 '21 at 21:37
  • by the way, please notice that in my formula I use the ; to separate between arguments, maybe you have configured with , and thats why is giving some error – Rubén de la Iglesia Apr 15 '21 at 07:41
  • 1
    I think no data can be retrieved from that URL using `IMPORTXML`, since no matter what `xpath_query` is set, even retrieving the root node (`/`), results in `Resource at url not found`. Maybe the website is blocking requests from this origin (see [this related question](https://webapps.stackexchange.com/questions/115664/how-to-know-if-google-sheets-importdata-importfeed-importhtml-or-importxml-fun)). Therefore, I think the workaround provided below is your best choice. – Iamblichus Apr 15 '21 at 09:09
  • This is what I believe lamblichus Thank you – Rubén de la Iglesia Apr 15 '21 at 14:34

2 Answers2

3

I am not sure about that function but I have downloaded historical stock pricing on Yahoo using a script.

function importCSVFromWeb() {
  // Provide the full URL of the CSV file.
  var csvUrl = "https://query1.finance.yahoo.com/v7/finance/download/MSFT?period1=1577806579&period2=1609428979&interval=1d&events=history&includeAdjustedClose=true";
  var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
  var csvData = Utilities.parseCsv(csvContent);

  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}

Here is my sheet after running the script

enter image description here

JohnA
  • 1,058
  • 6
  • 12
  • Hi! Thanks for the support! Unlickily google does not let me use the scripts (it says that the page is not found :/) But just to make you notice you are downlaoding the historical stock value, what I would like to do is download the table in the options tab :( – Rubén de la Iglesia Apr 14 '21 at 21:38
  • So, the google menu has Tools, under that should be "<> Script Editor", do you see that? Also, copy the URL for the download options and substitute in my example. – JohnA Apr 14 '21 at 21:52
  • I see the "<> Script Editor", I was getting an error message Luckily I found the problem... I have 2 google accounts I was using the secondary and then it was not working... if I open incognit tab and login there is working. Your scrips works ok in my PC but only download the info I do nto need as I told. If I change the url for this one: https://finance.yahoo.com/quote/VZ/options?date=1618531200&p=VZ I got this error: Error Exception: Could not parse text. importCSVFromWeb @ Código.gs:5 So for some reason is not working... – Rubén de la Iglesia Apr 15 '21 at 09:20
  • 1
    The reason you get that error is the URL does not return a CSV, just a web page. I took a look and unfortunately Yahoo Finance does not have a download option as it does for historical pricing. There may be other services that offer a free for personal use option download, most I found were subscription based. – JohnA Apr 15 '21 at 11:04
  • Hi JohnA, again thank you very much to take a look on it. I think that the problem is that Yahoo is showing the data after using some Javascrip... just what it makes me confuse is that I can get the data downloaded in Excel! So somehow it must be possible... Other workaround that I will try: download in an excel, put the excel in drive, and read the data from there – Rubén de la Iglesia Apr 15 '21 at 14:41
  • I am curious how you can download the Options in Excel, please elaborate (I also use Excel). – JohnA Apr 15 '21 at 14:49
  • Hi @JohnA, Is quite simple, you just go to Data ribbon, then to from web, and then you put the url and it appears the table to import, no macro or any rocket science needed. Here is the excel I did in 10 seconds: https://drive.google.com/file/d/1pfUXeZqW12b6Hpw4NiVlBillvnuIKvXf/view?usp=sharing – Rubén de la Iglesia Apr 15 '21 at 16:55
  • Just to end this topic I found this: https://www.youtube.com/watch?v=Nx7RUuYr5nI So... this is what happened... Yahoo is using Java so ImportHTML does not work (however it works in Excel) Is possible to download also using R code as he did in the video, thanks for the help! – Rubén de la Iglesia Apr 15 '21 at 19:56
  • I never heard of rstudio but looks promising albeit tedious, seems like modeled after a screen scraper. Problem is Yahoo changes the output and your script stops working. Selenium may be another tool to look into. – JohnA Apr 15 '21 at 20:13
  • I have used a Chrome add-in for scraping successfully https://chrome.google.com/webstore/detail/data-scraper-easy-web-scr/nndknepjnldbdbepjfgmncbggmopgden?hl=en-US – JohnA Apr 15 '21 at 20:16
0

Many thanks, it worked well. I slightly adapted code to work as a formula:

function importCSVFromWeb( url ) {
  // Provide the full URL of the CSV file.
  var csvUrl = url; // url example = "https://query1.finance.yahoo.com/v7/finance/download/%5EGSPC?period1=1495670400&period2=1653436800&interval=1wk&events=history&includeAdjustedClose=true"
  var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
  var csvData = Utilities.parseCsv(csvContent);

  var sheet = SpreadsheetApp.getActiveSheet();
  // sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
  return csvData;
}
user19199865
  • 1