Excel Tip of the Week: Linking to Data on the Web

Today’s topic is linking to web data.

There are plenty of examples where one might want to pull information from the web directly into Excel. I use this feature to track stocks. Instead of copying a pasting stock data, I can pull it straight into Excel and have other metrics calculated based on the stock price or other quote info.

Here are the steps to link to web data. In this example we will be grabbing the stock quote for Google:

1. Open a new worksheet or workbook
2. Go to the “Data” menu. Click “Get External Data” (on the far left) then select “From Web” Keyboard shortcut: Alt→A→ZX→FW
3. A mini web browser will pop-up. Using the address bar type in the URL of the website you want to pull info from. In our case it is finance.yahoo.com (Google finance doesn’t work well, more on this later).
4. In the quotes box type in Google’s ticker symbol, “GOOG”
5. You might be asked if you want to keep running scripts on this page. Click “Yes”
6. Your browser screen should look like this:
7. Notice the yellow and black arrow icons. These are showing which tables are available for linking. Click on the arrow right above the words “Last Trade.” A blue box should appear around that table and the icon should change to a green checkmark.
8. Click Import.
9. The browser should go away, now pick a cell to place the table and click OK.
10. Go back to the “Data” menu and in the “Connections” section click “Properties” Keyboard shortcut: Alt→A→P
11. Under the “Refresh Control” heading select how you want your Google quote to update. The default is for Excel to only update the info when you manually refresh (right click →Refresh). You also have the option to set an automatic update with a specific time interval or have the info update when the file is opened.

Keep in mind that linking to web data is tricky. Some websites are not organized into easy-to-use tables (Google finance is an example) and other times there is simply a disconnect between Excel and the Web. Also keep in mind that the web is not the only place you can link. Excel gives you other options such as an Access database or a properly formatted text file.