So you have been toiling away on a spreadsheet that must reach the CEO’s desk before the end of the day. Your boss has been after your life to get this done as fast as possible with his frantic phone calls and a rather sharp pitchfork that he uses on special occasions like these. A gazillion formulas, allocations and calculations later, with your body screaming for some coffee, you allow yourself a break. As you return to your desk with a cup-full of the gooey thingy that comes out of the coffee machine, you feel like checking the latest scores from the match your favorite team is playing tonight. “Ok … looks like we might …. ” – you turn around and there’s your boss standing right behind you. Damn #$#%^%&%$.
Excel to the rescue. Did you know that Excel can help you aggregate content from a number of websites in a single spreadsheet? Here’s how.
Getting Data From Other Web Sources in Excel
Click on the ‘Data’ tab on the ribbon and then ‘From Web’ button (or ‘Data‘ -> ‘Import External Data‘ -> ‘New Web Query‘ in Excel 2003). A ‘New Web Query’ box will open up with the homepage that you’ve set for Internet Explorer. In the ‘Address’ box, type in the URL that you would like to get information from (in our case, we choose Willow.tv to get cricking scores). The various tables on the page will be highlighted in as a yellow square. Once the page has fully loaded in the form, select the table which contains the specific portion that you need. The selected portion will get highlighted in a different color.. Click ‘Import’ and wait for the data transfer from the web page to the spreadsheet to complete.
Once you have the ‘feed’ placed in a spreadsheet, you can format it, hide columns that you don’t want and make other aesthetic changes.
Now comes the interesting part. Right click on the range and select ‘Data Range Properties‘. Once the form opens, do the following changes:
1. Turn off ‘Adjust Column Width‘ (so that the columns do not resize on refresh)
2. Select ‘Overwrite Existing Cells with New Data‘ (so that the same cells get reused)
3. Change ‘Refresh Every‘ to a lower value say, once every minute (so that we get the new information as fast as possible)
That’s it. But wait … there’s more to be done. You can scroll down and start over again with an unused cell. This time around let’s get content from our favorite financial website. So let’s head over to reuters.com and get information from the stock exchanges.
Getting Tweets as Well
So now we have both sporting and financial information in a single spreadsheet. Let’s tweet a bit, shall we. Let’s check out what Barrack Obama is saying.
So now we are up-to-the-minute with sports, markets and tweets. Add to this content from your favorite blogs, latest news headlines and throw in a couple of charts n’ pivots and pretty soon we are talking about a pretty serious content aggregator !
So the next time the devil comes calling, tell him that you are still ‘checking’ it out 🙂