Import data from yahoo finance to Google sheets

Question

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]/div1/div/span1") 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]/div1/div/span1") 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?

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
Hi, find here the google sheet + other options I tried: docs.google.com/spreadsheets/d/… Thanks! — Rubén de la Iglesia
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
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). Therefore, I think the workaround provided below is your best choice. — Iamblichus

JohnA JohnA · Accepted Answer · 2021-04-13T18:44:58

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

Import data from yahoo finance to Google sheets

1 Answers