Categorized | fun

The Content Aggregator Called Excel




TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

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

get-data-from-web

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.

feed-from-a-website

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)

external-data-range-properties

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.

get-stock-market-information-in-a-spreadsheet

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.

getting-tweets-from-twitter

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 :-)

TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Comments and Trackbacks

  1. Dave Roberts wrote:

    This is very cool but sports scores page URLs seem to involve dates which means the update won’t happen tomorrow. We’ll go with the assumption that I’m not clever enough to figure out a solution.

  2. Ajay wrote:

    @Dave – I think you are right. Admittedly no sporting event is going to last a long time so most event related URL’s will die out after a few days. The only thing to do probably is to find out a website that tracks that particular sport on a dedicated static URL or has a section where they tend to place the scores everytime a match is on. Or we’ll have to wait for someone cleverer to show us both a solution :-)

  3. JP wrote:

    Yahoo has sports RSS feeds you can grab and keep updated in Excel:

    http://sports.yahoo.com/top/rss

    But those are the news stories only, not scores. Here are some more:

    http://developer.yahoo.com/rss/

  4. UWAISE wrote:

    Big thanks to All the Budding Developers …!!!

    i very interested in all these works. i need some help to make my work easier.i have a table which has Item list in the first column and dates in the first row..i need to design a USERFORM to enter data into the cell where the that particular item and date matches. plz somebody help me

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel