Fetch And Update Yahoo Finance Data In Excel

Table of contents

• Step 1: Get the data URL
• Step 2: Connect to the Data Source
• Step 3: Selecting the data table
• Step 4: Keeping your data up to data
• Fetching more data

In this tutorial I will walk through a step-by-step guide to connecting an Excel sheet to Yahoo Finance [↗] in other to fetch up-to-date historical prices of a stock.

Step 1: Get the data URL

Let's assume you are interested in the historical prices for the ticker SPY. First, locate the page on Yahoo Finance that displays SPY's historical data [↗].

Once on that page, copy the URL from your browser's URL bar:

✅ Wrap📋
$ https://finance.yahoo.com/quote/SPY/history

Step 2: Connect to the Data Source

With the URL ready, open Excel and connect to the data source.

1. Go to the Data tab in the header menu.
2. Click on From Web in the ribbon beneath the header.

A pop-up will appear. Fill it in as follows:

• URL path: https://finance.yahoo.com/quote/SPY/history (the Yahoo Finance historical data page)

• HTTP request Header: [User-Agent] [Mozilla/5.0] (this ensures Yahoo treats the request as if it's coming from a web browser)

You may also want to fill out the command timeout section to 140 to give Excel enough time to fetch the data.

Once done, click Ok.

Step 3: Selecting the data table

Excel will now try to retrieve the data from the webpage. This might take a moment, or fail in which case repeat step 2.

Once the data loads, Excel will present a list of available tables if multiple are found on the page.

Select the first table, this usually contains the historical stock data.

Step 4: Keeping your data up to data

Once the data is imported, you can refresh it anytime by clicking the Refresh button under the Table Design ribbon. This will pull in the most recent data from Yahoo Finance.

Fetching more data

By default, the data fetched typically spans one year. To fetch a longer historical range:

1. Go back to Yahoo Finance.
2. Use the filters on the page to choose a custom time range
3. After applying the filters, copy the resulting URL in the browser's URL bar.
4. Use this URL in Excel to fetch the expanded dataset.

Note: Fetching large datasets may cause Excel to slow down or become unresponsive temporarily.

Here is another article you might like 😊 Putting Stock Market Returns Into Perspective: Geometric Mean And More