• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Cricket World Cup 1975 - 2015

A

ashish mehra

Active Member
The story behind creating the Cricket World Cup 1975 - 2015

A Big heeeeeeeeeeeello to all the Cricket fans around the world.

I am one of the craziest fans among our group of friends. I love to play & watch live cricket. It all started in 1992 World Cup when I was only 12 years; we did not have any medium to watch live cricket, so I used to go to my neighbor’s home to enjoy the thrill of world cup cricket matches @ 3:30 am IST.

I always had a desire to do something to showcase my talent & the opportunity arose when I was not employed for 6 months after working in a BPO (Medical Billing Process) for 5 years. I only knew some Medical Billing terminology & that’s it.

My family was not in a position to bear all the expenses without a financial support from me. I have made up my mind to make my career in the World of Excel. I quickly joined Chandoo.org & learnt number of tricks & joined an organization in Sales profile.

Before festival of Holi, I had some time & I started on the project with guidance of Narayan. I started giving some time to this project from home. Within 3-4 days everything was almost ready except some VBA coding in which testing was required to minimize the time taken in refreshing all the data from espncricinfo site.

The beauty of this dashboard is interactivity; you only need to click on Refresh All button & the macro will work in the background update all the data with formatting & lets you know the stats that you are interested in.

I hope you will like it. Please feel free to give your suggestion mail: ashishmehra2010@yahoo.com

Regards,
AM:)
 

Attachments

  • Cricket World Cup (1975 - 2015).xlsm
    426.7 KB · Views: 35
Last edited:
Hi AM...

You did an amazing job...however, I am wondering that the file is a taking alot of time...its been half an hour now i clicked on "Refresh All" button...and it is still saying "Connecting to Web".

Let me also tell you I am using the best internet service as I am sitting in my company and the speed is 14.12 MBPS (Download Speed) and 23.51 MBPS (Upload speed). So the speed can't be matter here..

Can you please look into also please share your experience and how much time it took you.

Thanks,
Ankit
 
Also, just for a suggestion - when we click on more button on Dashboard page, it is taking us to the raw data. It does not seems to be correct as I will not be interested taking user into my raw data. What do you say?
 
Hi Ankit,

Welcome to the forum!

Thanks for your kind words. Initially I was a bit worried if the whole process will take more than a minute to execute but happy to read your comments.

It took a week time to create Dynamic Cricket Dashboard.

Regards,
AM:)
 
Hi Ashish ,

One minor point :

Each of the individual procedures has :

Application.ScreenUpdating = False

at the beginning of the procedure , and :

Application.ScreenUpdating = True

at the end of the procedure.

Since you are calling nearly 40 procedures , the affect is significant ; I suggest you remove these two statements from each procedure , and have it only at the beginning and end of the main procedure.

Narayan
 
NARAYANK991 said:
Hi Ashish ,

One minor point :

Each of the individual procedures has :

Application.ScreenUpdating = False

at the beginning of the procedure , and :

Application.ScreenUpdating = True

at the end of the procedure.

Since you are calling nearly 40 procedures , the affect is significant ; I suggest you remove these two statements from each procedure , and have it only at the beginning and end of the main procedure.

Narayan
Click to expand...
Hi Narayan,

Logically your approach is right but practically it went other way.

I did as you suggested it strangely its taking much time. Initially, I also tried this way but not able to succeed. This was the only reason it took me more than 4 days to test & came up with the final code. I did not figure out why this is happening.

Can you please test & let us know.

Regards,
AM:)
 
Hi Narayan

Please check both the version before & after.

Regards,
AM:)
 

Attachments

  • Cricket World Cup (1975 - 2015) after.xlsm
    392.8 KB · Views: 12
  • Cricket World Cup (1975 - 2015) before.xlsm
    426.6 KB · Views: 8
Hi Ashish ,

I don't think it has anything to do with screen updating being true or false , though logically , turning it off should reduce the time.

I ran the Before file procedure 5 times , and the timings are :

118.78

110.13

108.32

123.17

180.30

You can see there is a range of 72 seconds , with the same macros being executed each time. It is clear that the variation is more due to accessing the website and retrieving the data from there , rather than anything else.

To this end , I suggest that you minimize the web access and data retrieval.

If you see , the code is accessing the site for the following 5 different views of data :

1. Maximum number of wickets taken by bowlers

2. Bowlers ranked in ascending order of average

3. Bowlers ranked in ascending order of economy rate

4. Bowlers ranked in ascending order of strike rate

5. Bowlers ranked in descending order of five fors.

This can easily be reduced to 1 , with the remaining 4 views being derived using the appropriate sort on the original data.

If this same technique is used in the case of batsmen's statistics , the overall time can be reduced to 50 seconds or so.

Narayan
 
Hi Narayan,

If I have correctly understand your point; you want to extract the data once & then use sorting for columns like Econ, SR, etc.

Regards,
AM:)
 
Hi Narayan,

All the macros has different link for each sheet.

I thought of using the approach wherein the data will be extracted once & we can sort it & later on skip it. The problem is if you closely look at the bowling data you will find that the number of rows is varying in each category.

MostWickets - 50
BestBowlingAverages - 28
BestEconomyRates - 27
BestStrikeRates - 28
MostFiveWickets - 50

Adopting this approach will not show the true data to the viewer.

Regards,
AM:)
 
Hi Ashish ,

I don't think so ; the rows have nothing to do with it , as long as the data is the same ; why don't you take the 50 rows of data from any one of the macros , and then sort it 4 different ways , and see whether the top 28 / 27 / 28 / 50 rows of data are the same in both cases. If they are , then what is the problem ?

Narayan
 
Hi Narayan,

I will try your to work on your suggestion tomorrow.

I figure out the number of headers in 3 sheets are same & 2 sheets are same. Same goes with row numbers also.

I believe we can minimize the number of macros to 2 instead of 5.

upload_2015-3-13_18-59-6.png


Regards,
AM:)
 
Hi Ashish ,

Sorry , I didn't think you were asking me for my comments.

I just did a match of the names in the two tabs , and there are a lot of names which do not match from each tab.

So it looks like you will have to do at least 2 macros to get the relevant data for these two tabs. If you want , you can do a match from the other tabs , and see if any names figure in those tabs , which do not appear in these 2 tabs , in which case more macros may need to be added to the list.

Narayan
 
NARAYANK991 said:
Hi Ashish ,

Sorry , I didn't think you were asking me for my comments.

I just did a match of the names in the two tabs , and there are a lot of names which do not match from each tab.

So it looks like you will have to do at least 2 macros to get the relevant data for these two tabs. If you want , you can do a match from the other tabs , and see if any names figure in those tabs , which do not appear in these 2 tabs , in which case more macros may need to be added to the list.

Narayan
Click to expand...
Hi Narayan,

Please don't say Sorry.

After reading your suggestion in post # 11 on reducing number of macros to increase the updation part of the live matches.

I tried to work on this & posted my observation in post # 16.

I asked you before making any changes because you knows better than me; if my observations are correct.

Regards,
AM:)
 
You must log in or register to reply here.
Back
Top