Many a times in office hours, there will be some interesting data over the internet to follow in live like stock prices, election counting results, and importantly live cricket score cards. Though my manager do not minds me checking the score regularly through browser, opening a browser draws unwanted attention on to another related news article / tweets / photo …etc. thus losing track on the work I am doing in my Excel sheet. (there are lot of psychological / behavioral studies performed on how web browsing changes individual working styles which are beyond my writing skills to put them here)
To avoid this navigating across web browser and Excel, I decided to link the score card to one of working Excel-sheet or some corner working Excel-sheet. One of the easiest way to do this is link the web connection data to Excel. Though there are many other ways (defining connections, VBA), I will narrate my own of doing it. Here, I am taking the example of IPO subscription live data instead of interesting cricket score. My friends who is cricket enthusiasts, will do it easily once they follow this example.
Regarding the IPO subscription, it is “MONTE CARLO FASHIONS LIMITED” subscription which is live as of today (issue period: 3rd to 5th Dec 2014) through book running. Though I am not a vivid investor, I do keep a track on the equity markets of India so I am just curious on how the subscription of retail investors changes over the issue period. The data is available at the link http://www.bseindia.com/markets/publicIssues/CummDemandSchedule.aspx?ID=890.
Now, to get this data into excel sheet, 1>click on “From Web” in the data, 2>enter the URL from where data needs to be captured as shown in the below image
Here the Excel automatically highlights table with data as shown highlighted yellow boxes with black arrow. So select the table from where the target data is available. Once click on import, Excel prompts where to import the data. Here Excel gives many options, we can just select any of the cell / new worksheet. Once the data is fetched into the sheet, we can format to our liking. By default, Excel keeps the auto update option to true. But care has to be taken to not to overwrite the format upon update.
That’s it. All now we have to do is for our favorite cricket score card… Easy. Now, you see the cricket scores in Excel.