This article is a quick primer on using XML data in your workbooks. XML is a simple way to automate some types of data loads in Excel.
What is XML?
XML stands for “extensible markup language”. XML is a structured way of formatting information so that it may be read by different systems. To see an example of XML, open a new browser window and paste http://articles.excelyogi.com/sitemap.xml in the URL bar. once open, right click and choose “view source”. The result is the XML version of excelyogi’s sitemap. We use this to tell search engines where all of our content is contained.
There are many XML feeds available on the internet, a simple Google search for “XML feeds” returns millions of results. Some of the more useful and/or practical ones are those from financial sources like stock market information.
In this XML guide we are going to pull in a simple weather feed that contains current weather information for Los Angeles, California. Our XML feed address is: http://weather.gov/xml/current_obs/KCQT.xml . We will be using Excel 2007 in this guide, please note that versions prior to 2003 do not support XML and only professional versions in 2003 support XML.
Step 1: Open a new Excel workbook and from the ribbon, select Data=>From Other Sources=>From XML Data Import. “From Other Sources” can be found in the “Get External Data” section of the data menu.
Step 2: In the “File Name” bar of the window that opened, enter: http://weather.gov/xml/current_obs/KCQT.xml
Step 3: You may get a schema problem popup, simply select OK to have Excel fix.
Step 4: In the “Import Data” window that is now open, select new worksheet, then OK
If everything went as expected, you should now see a worksheet full of Los Angeles current conditions data. That is essentially it!
Now the fun part:
While the sheet with the weather data is selected, go to data then properties. In the refresh control section of the window that opens you can select when and how often you would like Excel to update the feed. Using this functionality you could potentially have a fully automated workbook, displaying various data (weather in this case), and run calculations against the data as you would in any worksheet.
I think you will find this to be one of the most powerful tools in workbook design. With millions of feeds available online and many corporate systems dumpling XML feeds, the possibilities are unlimited!
- Referencing Data in Another Excel Workbook Q: Joe, I have data in another workbook that I...
- Excel Data Validation Data validation gives the Excel developer a way of dictating...
- User Passwords in Excel – Multiple Passwords Simple multiple user password protection in Excel....
- Data Validation List on Seperate Sheet How to create list for data validation on different sheets...
- Playing with VBA in Excel, Without Knowing VBA! Introduction to user defined functions and VBA in Excel....