how to import stock data into google sheets
IN SUMMARY
You can easily import stock data from Yahoo Finance into Google Sheets using the IMPORTXML function. This allows you to pull various data points like stock price, earnings date, ex-dividend date, industry, and company description directly into your spreadsheet.
Setting up the IMPORTXML Function
Copy the URL of the Yahoo Finance page. In your Google Sheet, start the IMPORTXML function with the URL enclosed in double quotes: =IMPORTXML("https://finance.yahoo.com/quote/MSFT?p=MSFT",...
Right-click on the data element (e.g., stock price) and select 'Inspect' or 'Inspect Element'. In the code view, right-click on the highlighted code and select 'Copy > Copy full XPath'.
After the URL in the IMPORTXML function, add a comma and paste the XPath enclosed in double quotes: =IMPORTXML("https://finance.yahoo.com/quote/MSFT?p=MSFT","//*[@id='quote-header-info']/div[3]/div[1]/div/fin-streamer[1]")
Automating the Process
In the IMPORTXML function's URL, remove the stock ticker portion (e.g., "MSFT") so that the URL is generic: =IMPORTXML("https://finance.yahoo.com/quote/?p=",...
Outside the IMPORTXML function, add an ampersand (&) and reference the cell containing the stock ticker you want to import data for: =IMPORTXML("https://finance.yahoo.com/quote/?p="&A1,...
Follow the same process for importing other data points like earnings date, ex-dividend date, industry, and company description. This way, changing the stock ticker in the referenced cell will update all imported data.
Troubleshooting and Tips
Ensure that the URL and XPath are enclosed in double quotation marks correctly. Missing or misplaced quotation marks can cause errors.
If the website structure changes, the XPaths may need to be updated. Re-inspect the elements and copy the new XPaths.
While Yahoo Finance is a popular source, you can also import stock data from other financial websites by adjusting the URL and XPaths accordingly.