Excel – Data from Web

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.

URL Link Header

URL Link Excel Data

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

Excel Web Data Link

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.

Excel Web Data Live Score Shares

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.

Advertisements

About Paripurnachari

Finance Professional, Coder, Engineer
This entry was posted in Technology and tagged , , , , , , , . Bookmark the permalink.

3 Responses to Excel – Data from Web

  1. Pingback: NSE Live Stock (Share Price) Data into Excel | Thinking Through Excel A1

  2. Pingback: NSE Equity Stock Watch in Excel | Thinking Through Excel A1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s