Many companies depend on up-to-date data to make informed decisions regarding their businesses. Millions of small businesses around the world use Microsoft Excel to input, analyze and evaluate business data of all kinds. Consequently, company staff members often spend a considerable amount of time inputting and formatting data for use in Excel spreadsheets. If your company has investments or accounts and uses the Internet to track data for them, your employees probably copy and paste data from the Web into Excel workbooks to perform calculations or prepare reports. However, using the Excel macro recorder, you can program your spreadsheets to download data and insert it into cells automatically.

Online Data Source Required

Before you can download and use real-time data in Excel, you must have an online data source capable of exporting or outputting records in XML format. Most commercial websites that display stock quote listings, currency exchange rates or other financial data usually offer a link for exporting or viewing real-time data in XML format. Many sites will have an “Export to Excel” option as well, but this option will provide you with static data only current when you view it on the site. Click the “Export to XML” or “View in XML Format” link on the page to open a new browser tab or window. When the new window appears, use the “Ctrl-C” keys to copy the URL address to the Windows clipboard.

Excel Web Queries

Once you have the URL that contains the XML format for the data you want to retrieve and view in Excel, you can use the Web Queries function to download it and format it as a nice, clean table inside your spreadsheet. If you click the “Data” tab on the Excel ribbon bar, then click “From Web,” a new window appears where you can paste the URL address from the financial data website. Click the “Import” button in the New Web Query window and a table containing the current live data from the site appears in your spreadsheet in the cell range you select. You can then use your mouse to resize the table or even move it to another part of your worksheet. Additionally, you can change the font, font size or font and table color by right-clicking the table, then selecting “Format Table” or “Format Cells” on the pop-up list.

Recording the Download Macro

Excel Web Queries makes entering XML data feeds from websites relatively easy. Still, you must copy and paste the XML URL address every time you want to create a new table in your spreadsheet – unless you create a macro to do it automatically. Before you can create a macro to download real-time data from the website, you must first enable the “Developer” tab in Excel. To do this, click “File,” then “Options.” In the Excel Options window, you will see a link labeled “Customize Ribbon.” Click that link and enable the “Developer” option in Main Tabs. Clicking the “OK” button returns you to the main Excel window and displays the Developer tab on the ribbon. Click the “Record Macro” button on the Developer tab before creating the new Web query in Excel and assign it a hot key. After you import the data into the new table, click “Stop Recording” on the Developer tab. From that point on, if you press the assigned hot keys for the new macro, Excel downloads the real-time data and creates the table automatically.

Keeping the Data Fresh

An Excel Web Query allows you to retrieve the latest real-time data and insert it in your spreadsheet. However, Web Query does not refresh data in the table automatically. To refresh the data, you must click the “Refresh All” icon in the Connections sections of the Data tab. Of course, you can record a macro for that as well, so that you can simply use the hot key combination to refresh the real-time data any time you wish. If you click the button control icon in the “Insert” section of the Developer tab, you can create control buttons you can place on your spreadsheet and use to run the macros quickly with a single mouse click. Click one button to retrieve the data and create the table in your spreadsheet, then later click the other to refresh the displayed data.

About the Author

Jeff Grundy has been writing computer-related articles and tutorials since 1995. Since that time, Grundy has written many guides to using various applications that are published on numerous how-to and tutorial sites. Born and raised in South Georgia, Grundy holds a Master of Science degree in mathematics from the Georgia Institute of Technology.