Lyte Byte

Though Microsoft Excel is a widely used Office application, some of the features like accessing live data from the Internet in a spreadsheet and doing further calculations is not known to many. At work you may be required to follow live data like a stock market or currency rate etc and then do further calculations based on need and show them on a pie chart. Or at home, you may be following your favorite sports live data and you may want to do your own calculations.

Whatever the case, Office Excel, allows you to get live data from the web and do all the other calculations as per the formula you have mentioned in a fraction of a second and that too without any unnecessary distractions from the web. The best of all features is that at regular intervals it updates all the data and the calculations you have done.

Hint: If you are a compulsive person to watch your portfolios and other stocks performance while you are doing work and you don’t want your boss to catch you every time in front of those web pages, then you can use this technique too

So how to get the live date from the web in Excel 2007?

Go to Data tab and click From Web under Get External Data. (There is an advanced web query plugin for Excel 2007 which makes this feature much easier) In the new Web Query box, enter the website from where you want to get the data. In our example, we are looking at the top 20 companies from the Technology sector.

Click the yellow arrow near the table that you want to select, it will turn green with a check mark and click Import at the bottom.

Choose in which cell you want to place the table or choose a new worksheet.

Now, you will have the live data in your spreadsheet.

You can set as many new data you want in the same worksheet and if you want you can even create custom charts. Here I have created a column chart showing the company name and %change.

You can also do other calculations using formula and charts. The best of all you can set it to update in a regular frequency.

Option 1: You can manually refresh or update the data using the Refresh button or using the keyboard shortcut CTRL + ALT + F5.

Option 2: You can set the time at which the data should be automatically updated. Click any of the cell which is retrieved from the web and click Properties from Data tab under Connections. Under Refresh Control, you can mention how frequently you want to update the live data.