Connect to the Web from Excel

Did you know that you can connect to the web from Excel? This can vastly improve your life at times, and not do much at all other times. Depending on how easily identifiable the information is, sometimes Excel can handle the challenge while other times it can not. To use this nifty little feature, go to "Data | Import External Data | New Web Query...". Under the import external data menu item, you could also choose to import text files or connect to a database and grab data from there. For this post, we'll stick to the web.

You might wonder, "Why do I need this feature in Excel?". As of now, it's not tremendously useful in my opinion because most web sites are designed for your viewing pleasure - not for data transfer. This is, however, changing rapidly with the growing use of XML (take, for instance, the XBRL initiative sponsored by powerhouses such as PwC and Deloitte). Therefore, it will eventually become very useful I'm sure. As one example of where you might use this technology, let's assume that you keep track of your portfolio in Excel. You're looking to download stock prices for certain tickers every month, so you want to create a macro to do it for you. Don't know about macros? Read here. Now that we have that covered, lets try to create our macro.

To start, we'll begin by simply examining the Yahoo! finance site, which is the easiest site to accomplish this task (as far as I know). Instead of using your browser, however, we'll use Excel. Go to "Data | Import External Data | New Web Query". From here, type in http://finance.yahoo.com and navigate to the input box asking you for a ticker symbol. Enter whatever symbol you like (perhaps PNC) and hit enter. From here, you'll have to click on the link on the left hand side of the page for "Historical Prices" and you can change the dates as you see fit. Excel will then open up the page with the quotes, which you can click on the yellow arrow to tell Excel that you want to download that table. Viola! Excel will import the table for you and you can change and / or manipulate the data! You can repeat this procedure for any website where you typically download information. To automate the task, simply record a macro before you do it and it will be available for next month. If you get clever, you can manipulate the VBA code and get the dates to change to whatever you want as well. For a primer on VBA, check out this post.

Anyway, you can use the previous procedure to create some very sophisticated macros. Alternatively, I've created an Excel add-in to download prices for you that you can get here. Try it out - if it doesn't work, write me an email or post a comment and I'll be sure to try and fix it. It was started by creating a macro as recommended in the previous paragraph, and then some heavy lifting was done to make it more flexible and user friendly. To use the add-in, download the file and save it somewhere on your computer. From Excel, go to "Tools | Add ins..." and click "Browse" ... then navigate to wherever you saved the file, click on it and hit "OK". Now you'll have to get a little creative - if you want the tool available whenever you want, right-click on a toolbar and click "Customize". Scroll down to the "Macros" section on the "Commands" tab and drag the custom button to some toolbar (whatever one you want). It should be a smiley face. Once it's placed on the toolbar, right click on the smiley face and click "Assign Macro". The macro name is "Show_Yahoo_Form" and hit enter. Close all dialog boxes, and then you can click on that button whenever you want to download stock prices! If you only want to try it out once, instead of adding a toolbar button just hit Alt-F8 and type "Show_Yahoo_Form" and the form will pop up. Good luck!