Crunching Stats Calculator

[Win2Kuser]

Hey Guys, first post, so bear with me

Just doing a quick test to see what this forum is capable of doing using VB code etc...

[size=2][b]
  
  
  [u]Milestones for the day:[/u][color=#f87a17]
  Member1 : 10 thousand results
  [/color]
  
  [u]Today's stats for[/u][color=#59e817]
  Saturday 28 October 2006 [/color]
                                   New      New |      Total      Total   |      Week  Av points    CPU Time
   +/-  #   Member Name         Points  Results |     Points    Results   |     Total    per job   Yr:Day:Hr
   === ==== ===============    =======  ======= |  =========  =========   | =========  =========  ==========
   --  1   [color=#6698ff] Member1          [/color]   16,658      167 |  1,383,240  [color=#f87a17]   10,119  [/color] |    92,131      136.7    2:174:11
   --  2   [color=#6698ff] Member2          [/color]   14,710      144 |  1,329,392     10,344   |    82,865      128.5    2:199:20
   --  3   [color=#6698ff] Member3          [/color]    3,472        8 |    390,750        954   |    25,154      409.6    0:277:15
   --  4   [color=#6698ff] Member4          [/color]        0        0 |    266,411        622   |         0      428.3    0:170:07
   --  5   [color=#6698ff] Member5          [/color]    1,324        3 |    145,718        336   |    11,552      433.7    0:079:04
   --  6   [color=#6698ff] Member6          [/color]        0        0 |     75,100        173   |       370      434.1    0:053:21
   --  7   [color=#6698ff] Member7          [/color]    5,297       51 |     74,622        700   |    28,976      106.6    0:069:02
   --  8   [color=#6698ff] Member8          [/color]        0        0 |     20,212         80   |     1,721      252.7    0:021:05
   --  9   [color=#999795] Member9                  0        0 |      4,453         11   |         0  [/color]    404.8    0:003:03
            ===============    =======  ======= |  =========  =========   | =========  =========  ==========
           [color=#efffff] .               [/color]       New      New |      Total      Total   |      Week    Team Av   Team Time
           [color=#efffff] .               [/color]    Points  Results |     Points    Results   |     Total    per job   Yr:Day:Hr
           [color=#efffff] .               [/color]   -------  ------- |  ---------  ---------   | ---------  ---------  ----------
            Team Total          41,461      373 |  3,689,898     23,339   |   242,769      158.1    6:318:19
           [color=#efffff] .               [/color] ===================== =========  =========   | =========  =========  ==========
           [color=#59e817] Up Rank         [/color] Last weeks 10/22/2006 3,447,129     21,272  
           [color=#f63817] Down Rank       [/color] --------------------- ---------  ---------  
           [color=#6698ff] No Change       [/color] Weekly running total    242,769      2,067  
           [color=#fcce8e] Newbe           [/color] ===================== =========  =========  
           [color=#999795] Inactive        [/color] Uploaded last week      355,779      2,187  
           [color=#efffff] .               [/color] Output +/- of               n/a        n/a  
           [color=#efffff] .               [/color] --------------------- ---------  ---------  
           [color=#efffff] .               [/color]                                             
           [color=#59e817] Team Rank       [/color]       322 (+2)
  
  
  Win2k WCG Stats Rev 1.7.2
  Averages and inactive members calculated over 13 days
  
  [/b][/size]

 

[Win2Kuser]

Okay, so that works, this is using Excel btw, fully automated, just run it once a day and job done, this is the output file it gives, just paste it into a thread.

Okay, it isn't an advanced php script that runs all the time, but it's easy enough to use...

Hey Ian

Any good for ya?

 

[Ian]

Oh Excellent Mark! I'd love it if I could re-format it a bit using the code you've used to generate that (of course with your credits intact ).

I may be able to get this to post to vBulletin automatically once a day if it is saved as a .txt file - I'll have to look into this over the next few weeks but I'll let you know of course what I can find

 

[Win2Kuser]

Oh Excellent Mark! I'd love it if I could re-format it a bit using the code you've used to generate that (of course with your credits intact ).

I may be able to get this to post to vBulletin automatically once a day if it is saved as a .txt file - I'll have to look into this over the next few weeks but I'll let you know of course what I can find

Cool, glad you like it.

Yeah, reformatting is fairly easy, the main page holds all the formatting which you see here, then the other pages just store the data for todays data, yesterdays, last weeks and so on, there are a few loops that count down the main page looking for a key in order to insert milestones as an example, but if you have some Excel experience, it's pretty easy to understand what it's doing

Just a quick question, and it probably sounds a bit daft, but what is your team called in WCG? I've searched for the obvious ones (PCR PC Review etc), but it won't find anything...

Obviously, I need the right team page so that I can get the right data to parse...
TIA

 

[Ian]

The team name is "PCReview Crew II"

This Excel file sounds remarkably clever

 

[Win2Kuser]

Okay, got it
I thought about it just after I posted it, had a look and low and behold...

There is a considerable amount of VBA code at work in the stats which parse through the data, looks for new members, looks for members that have left, checks milestones etc, but the main compare part is just done using the VLookup function.

If actually does quite a bit, but most of it is function calls rather than pure VBA code.
It is self contained though, so you don't need to download stats data or anything, it does all that for you, just set it up as a scheduled task once daily, and thats all you need.

You'd have to see it in action really to get a feel for what it's doing

What I'll do now is load up your teams data and over the next couple of days (very busy with work atm), I'll set it right for this team rather than XPC's which is was written for. Once I get it done, I'll let you know.

The output file can be saved as anything really btw, it's actually a space delimited file which normally defaults to a PRN file, I just call it stats.txt for ease, theres no reason why it can't be saved as the date.txt for example...

Will be in touch anyway...

[edit]
Forgot to say, this actually uses the WCG stats site rather than any others, the WCG stats are flakey at the best of times, not much point relying on others that could be even flakier
[/edit]

 

[muckshifter]

F F Flipping heck ... great work, bloody marvelous.

 

[Ian]

That would be amazing Mark, especially as it uses the WCG stats site

Thankyou so much for the help What an excellent crunching community spirit

I'll be sure to give you a great big shout on the main page as this will be a great help

 

[Win2Kuser]

No problem, I'm not in it for the glory, I just get a kick out of doing things like this. If I can help the DC community by doing this, then all the better. The only way will help find cures for all these nasty things is by pulling together. The whole point of having rival teams is to push each other to do more, this is what drives the global community to do as much as it does! without stats, a DC project is worthless IMO.

I'm all for great big shouts though, I like an ego boost now and again

Okay, I've started inserting your team stats data into the pages etc, now, whilst I'm at this stage, it would be fairly easy for me to put it some kind of order for you.
The stats I posted above are what it's set as now, but I can add/remove columns, show different things such as average results per week, percent of daily/weekly output for each member etc etc, throw some ideas my way as I'm open to many ideas.

You mentioned that you would like the format in the same style as some others you do or something, if you give me some idea of what you are after, I'll see what I can do...
The colours BTW for up/down rank and the milestones etc are customisable, I set it at a red for down, green for up, blue for no change, grey for inactive, and goldish for newbies, milestones are listed in orange, are these okay, or do you have a particular colour scheme you would prefer.

If we can at least get it somewhere handy, it will save you some time...

Let me know your thoughts etc in the next day or so, and I'll do what I can.

Just as a btw, the stats as they are will show no history as such, I'm hoping to get a run on them tonight so that it at least grabs the end of week stats, but other than that, they will be blank other than whats loaded as from now. It will take a couple of weeks before it builds complete history...

 

[Ian]

The only way will help find cures for all these nasty things is by pulling together. The whole point of having rival teams is to push each other to do more, this is what drives the global community to do as much as it does! without stats, a DC project is worthless IMO.

I wouldn't have put it better myself I really like the fact that there is friendly banter between teams as it pushes people and creates a great community

If we can at least get it somewhere handy, it will save you some time...

The main things I'd probably use in the formatting would be similar to what is used in the old manual format we have used - milestones in post 1, total points in post 2 and daily points in post 3. At the moment your milestones are spaced above the rest which is ideal as I can just change the font and copy+paste it in, and if the total points/ daily points could be split that would probably do the trick. The colour scheme sounds great

Muckshifter is the main man for stats, so if he has some better ideas then go with them

edit : ignore my ideas above and see my next post

 

[muckshifter]

Muckshifter is the main man for stats, so if he has some better ideas then go with them

A change is as good as a rest ...

The 'format' I was using was just a convenient way to show the stats. Been wanting to change it for some time, just could not think of anything better.

If we are going to have a permanent prominent page, go for what style you think fits.

 

[Ian]

In that case, how about the layout that is demonstrated just before?

I can make a static page that is read from a text file to display the current stats, but I also think we should keep with the daily postings just to get a bit of natter and interest going - I'll try and automate this (if not I'll do it before I go to uni).

 

[muckshifter]

Sure, best of both worlds ...

At the moment I'm concerned about the colour scheme ... ... I had to highlight all the text in the code box to read it ...

I didn't 'see' the grey, green or orange at all ... ... I'm sure you'll get it right though.

 

[Ian]

Sounds good to me

I'll do the stats this week as Ady is up at 5:30 for work and you need to give your eyes a rest I'll try and do it in the morning before uni but it may be at lunch time before I get a proper chance

 

[Win2Kuser]

Okay, cool, thats saves me some time reformatting it

Couple of things though, there are certain limits on how wide an excel sheet can export as text, but as it is, there is enough room for maybe 2 more columns, these could be pretty much anything you wanted (within reason) such as percent of output for each member against what was uploaded for the team, or average results per day etc etc etc.

I've set it so that it exports multiple txt files, the main file above that lists everything, then there is a seperate file listing newbies for the day, leavers for the day, then there is a daily dump list that shows the top 10 dumpers and any rank movers, then at the end of the week, there is a file listing any newbies that have joined during the week.
Code is coming on quite nicely now, a list of the code is here
Yeah, I know it's very linear, but it's easy to follow that way...

You mention about the colour. Do you have different schemes here such as black background and white text, or is the blue theme standard?

To me, the only colour that looks iffy was the newbie gold colour, a little too harsh perhaps, the key down the left side bottom that lists the colour and what it means shows up fine for me :dontknow:
From what I remember, when I started these at XPC, members using the Emerge theme (dark), couldn't see the colours properly so I had to change them slightly...

If possible, can you list some examples of colour you would like for each as a hex number i.e. ffffff for white or 000000 for black, or 59e817 for green etc etc.

I'm just about done now, data is in the file and it knows what to look for, it will get it's first run in the early hours of tomorrow morning (4:30am GMT) - fingers crossed I haven't ballsed anything up

For now though, I have to be up at 4:30am, so I'll catch you all laterz...

 

[Ian]

OMG, that is one hefty chunk of code! I can't wait to get this up and running

I think the columns seem great as they are, although is it possible to remove the week total and av points per job columns, just so that it can be viewed at lower resolution monitors? If it is too much coding to do so, don't worry about it!

The blue colour scheme is the only one, the following colours seem to go ok on it when bold:

Up Rank​

Down Rank ​

No Change​

Newbe​

​

Thankyou again so much - this will make such a difference to the time taken to post stats!

 

[Win2Kuser]

OMG, that is one hefty chunk of code! I can't wait to get this up and running

I think the columns seem great as they are, although is it possible to remove the week total and av points per job columns, just so that it can be viewed at lower resolution monitors? If it is too much coding to do so, don't worry about it!

The blue colour scheme is the only one, the following colours seem to go ok on it when bold:

Up Rank​

Down Rank ​

No Change​

Newbe​

​

Thankyou again so much - this will make such a difference to the time taken to post stats!

Okay, I'll remove the blue and change it to black, and make the other colour changes...

The colour codes need to be actual hex codes so that any combination of colour takes up the same number of characters, this is to keep it all even down the columns regardless of colour selected.

Just as a test, this is a GREEN - hex 008000
This is a BLACK - hex 000000
This is a RED - hex f63817
This is a DARK ORANGE - hex ff8c00

Presumably then, you want the same orange for newbies and milestones?

It successfully ran in the early hours of this morning, just got a couple of bugs to sort out with the daily dumpers list and a couple of other trivial things, I've already made the changes to the code (and added a load) to fix the bugs, so tomorrow will be it's final test. I've also removed the week total and average points per job columns, they are still there (in case you want them back at a later stage), they are just hidden from final output

This is how things looked this morning, I've had to fill a few things in by hand this time (tomorrow hopefully will be okay), but that will give you the general idea;

[SIZE=2][B]

                   [size=4][color=#6698ff][U]WCG Daily Team Stats - The PC Review Crew II[/U][/color][/size]

[url=http://www.worldcommunitygrid.org/team/viewTeamInfo.do?teamId=3CJW57FQP1]WCG PC Review Team Stats - copy/paste URL may be required due to url length[/url]

[U]Milestones for the day:[/U][color=#f87a17]
Ian Cunningham: 1 Thousand Results
[/color]

[U]Today's stats for[/U][color=#59e817]
Sunday 29 October 2006 [/color]
         [I][/I][I][/I]                        New      New | [I][/I][I][/I]     Total  [I][/I][I][/I]    Total   |   Av points    CPU Time
 +/-  #  [I][/I][I][/I] Member Name         Points  Results | [I][/I][I][/I]    Points  [I][/I][I][/I]  Results   |     per job   Yr:Day:Hr
 === ====[I][/I][I][/I] ===============    =======  ======= | [I][/I][I][/I] =========  [I][/I][I][/I]=========   |   =========  ==========
 --  1   [color=#000000] Adywebb          [/color]    1,675       14 | [I][/I][I][/I] 3,224,195  [I][/I][I][/I]    7,991   |       403.5    5:005:17
 --  2   [color=#000000] Chris_Postill    [/color]    3,440        8 | [I][/I][I][/I] 1,337,228  [I][/I][I][/I]    4,234   |       315.8    3:107:06
(+1) 3   [color=#008000] TriplexDread     [/color]    7,553       16 | [I][/I][I][/I]   976,483  [I][/I][I][/I]    2,667   |       366.1    1:212:09
(-1) 4   [color=#f63817] chelseafc2005    [/color]    1,914        4 | [I][/I][I][/I]   972,252  [I][/I][I][/I]    2,237   |       434.6    1:109:07
 --  5   [color=#000000] muckshifter      [/color]    2,028        8 | [I][/I][I][/I]   812,219  [I][/I][I][/I]    3,359   |       241.8    1:226:01
 --  6   [color=#000000] CITech           [/color]        0        0 | [I][/I][I][/I]   807,878  [I][/I][I][/I]    2,164   |       373.3    1:041:18
 --  7   [color=#000000] V_R              [/color]    6,846       13 | [I][/I][I][/I]   785,923  [I][/I][I][/I]    1,896   |       414.5    1:168:10
 --  8   [color=#000000] Harvey           [/color]    5,466       14 | [I][/I][I][/I]   759,892  [I][/I][I][/I]    2,272   |       334.5    1:092:13
 --  9   [color=#000000] pikakilla        [/color]    3,307        8 | [I][/I][I][/I]   636,537  [I][/I][I][/I]    1,656   |       384.4    0:325:16
 --  10  [color=#000000] cImrie           [/color]    9,885       47 | [I][/I][I][/I]   536,529  [I][/I][I][/I]    1,902   |       282.1    2:286:12
---------[I][/I][I][/I] ---------------  -------------------|-[I][/I][I][/I]------------[I][/I][I][/I]------------|---
 --  11  [color=#000000] itisi            [/color]    1,666        2 | [I][/I][I][/I]   518,735  [I][/I][I][/I]    1,287   |       403.1    1:046:10
 --  12  [color=#000000] Fred B           [/color]        0        0 | [I][/I][I][/I]   410,723  [I][/I][I][/I]    1,104   |       372.0    1:013:06
 --  13  [color=#000000] Ian Cunningham   [/color]    2,214        4 | [I][/I][I][/I]   369,494  [color=#ff8c00]    1,003  [/color] |       368.4    0:267:08
 --  14  [color=#000000] Rush             [/color]      706        5 | [I][/I][I][/I]   363,214  [I][/I][I][/I]    1,424   |       255.1    0:240:23
 --  15  [color=#000000] Michael John We  [/color]    1,229        6 | [I][/I][I][/I]   362,035  [I][/I][I][/I]    1,162   |       311.6    0:325:04
 --  16  [color=#000000] peahouse05       [/color]    1,243        4 | [I][/I][I][/I]   332,491  [I][/I][I][/I]      882   |       377.0    0:248:06
 --  17  [color=#000000] Quadophile       [/color]        0        0 | [I][/I][I][/I]   306,130  [I][/I][I][/I]      750   |       408.2    0:174:02
 --  18  [color=#000000] RustySherrif     [/color]        0        0 | [I][/I][I][/I]   273,060  [I][/I][I][/I]    1,099   |       248.5    1:017:06
 --  19  [color=#000000] AbsoluteKevster  [/color]    6,581       17 | [I][/I][I][/I]   249,724  [I][/I][I][/I]      789   |       316.5    0:265:23
 --  20  [color=#000000] ChrisIMRIE       [/color]        0        0 | [I][/I][I][/I]   196,335  [I][/I][I][/I]    1,115   |       176.1    0:188:21
---------[I][/I][I][/I] ---------------  -------------------|-[I][/I][I][/I]------------[I][/I][I][/I]------------|---
 --  21  [color=#000000] feckit           [/color]        0        0 | [I][/I][I][/I]   183,941  [I][/I][I][/I]      449   |       409.7    0:083:14
 --  22  [color=#000000] Me__2001         [/color]    1,434        3 | [I][/I][I][/I]   170,630  [I][/I][I][/I]      453   |       376.7    0:103:17
 --  23  [color=#000000] MajorMellons     [/color]        0        0 | [I][/I][I][/I]   165,783  [I][/I][I][/I]      400   |       414.5    0:209:05
 --  24  [color=#000000] kingnathski88    [/color]        0        0 | [I][/I][I][/I]    97,343  [I][/I][I][/I]      317   |       307.1    0:138:04
 --  25  [color=#000000] Rudi the Red     [/color]        0        0 | [I][/I][I][/I]    93,940  [I][/I][I][/I]      279   |       336.7    0:061:20
 --  26  [color=#000000] RufusW           [/color]      403        1 | [I][/I][I][/I]    64,473  [I][/I][I][/I]      255   |       252.8    0:078:22
 --  27  [color=#000000] herval307        [/color]        0        0 | [I][/I][I][/I]    37,384  [I][/I][I][/I]       80   |       467.3    0:045:05
 --  28  [color=#000000] VAIO GRX616      [/color]        0        0 | [I][/I][I][/I]    36,325  [I][/I][I][/I]      116   |       313.1    0:032:06
 --  29  [color=#000000] cirianz          [/color]        0        0 | [I][/I][I][/I]    35,313  [I][/I][I][/I]       56   |       630.6    0:065:00
 --  30  [color=#000000] Stuartw101       [/color]        0        0 | [I][/I][I][/I]    32,893  [I][/I][I][/I]      103   |       319.3    0:025:16
---------[I][/I][I][/I] ---------------  -------------------|-[I][/I][I][/I]------------[I][/I][I][/I]------------|---
 --  31  [color=#000000] Sexy Bex         [/color]        0        0 | [I][/I][I][/I]    24,361  [I][/I][I][/I]      125   |       194.9    0:038:10
 --  32  [color=#000000] TLD              [/color]        0        0 | [I][/I][I][/I]    22,257  [I][/I][I][/I]       64   |       347.8    0:014:17
 --  33  [color=#000000] Charles Chan     [/color]        0        0 | [I][/I][I][/I]    15,377  [I][/I][I][/I]        9   |     1,708.6    0:019:11
 --  34  [color=#000000] HAKA ryu         [/color]        0        0 | [I][/I][I][/I]    10,807  [I][/I][I][/I]       57   |       189.6    0:016:21
 --  35  [color=#000000] Tunix            [/color]        0        0 | [I][/I][I][/I]     6,364  [I][/I][I][/I]       49   |       129.9    0:006:02
 --  36  [color=#000000] phicksus         [/color]        0        0 | [I][/I][I][/I]     5,831  [I][/I][I][/I]       26   |       224.3    0:003:21
 --  37  [color=#000000] jaxx83           [/color]        0        0 | [I][/I][I][/I]     5,798  [I][/I][I][/I]       30   |       193.3    0:006:08
 --  38  [color=#000000] ladypcer2        [/color]        0        0 | [I][/I][I][/I]     3,129  [I][/I][I][/I]        8   |       391.1    0:001:09
 --  39  [color=#000000] Cache-man        [/color]        0        0 | [I][/I][I][/I]     2,995  [I][/I][I][/I]        6   |       499.2    0:004:23
 --  40  [color=#000000] midas1           [/color]        0        0 | [I][/I][I][/I]     2,305  [I][/I][I][/I]       10   |       230.5    0:002:23
---------[I][/I][I][/I] ---------------  -------------------|-[I][/I][I][/I]------------[I][/I][I][/I]------------|---
 --  41  [color=#000000] Covenant         [/color]        0        0 | [I][/I][I][/I]     1,139  [I][/I][I][/I]        5   |       227.8    0:001:16
 --  42  [color=#000000] arabicdes        [/color]        0        0 | [I][/I][I][/I]       833  [I][/I][I][/I]        4   |       208.3    0:001:05
 --  43  [color=#000000] chris24ang       [/color]        0        0 | [I][/I][I][/I]         0  [I][/I][I][/I]        0   |         N/A    -:---:--
 --  44  [color=#000000] Crusader325      [/color]        0        0 | [I][/I][I][/I]         0  [I][/I][I][/I]        0   |         N/A    -:---:--
         [I][/I][I][/I] ===============    =======  ======= | [I][/I][I][/I] =========  [I][/I][I][/I]=========   |   =========  ==========
         [color=#00bfff] .               [/color]       New      New | [I][/I][I][/I]     Total  [I][/I][I][/I]    Total   |     Team Av   Team Time
         [color=#00bfff] .               [/color]    Points  Results | [I][/I][I][/I]    Points  [I][/I][I][/I]  Results   |     per job   Yr:Day:Hr
         [color=#00bfff] .               [/color]   -------  ------- | [I][/I][I][/I] ---------  [I][/I][I][/I]---------   |   ---------  ----------
         [I][/I][I][/I] Team Total          57,590      174 | [I][/I][I][/I]15,250,298  [I][/I][I][/I]   43,894   |       347.4   30:310:10
         [color=#00bfff] .               [/color] =====================[I][/I][I][/I] =========  [I][/I][I][/I]=========   |   =========  ==========
         [color=#59e817] Up Rank         [/color] Last weeks 10/22/2006[I][/I][I][/I]15,192,708  [I][/I][I][/I]   43,720  
         [color=#008000] Down Rank       [/color] ---------------------[I][/I][I][/I] ---------  [I][/I][I][/I]---------  
         [color=#000000] No Change       [/color] Weekly running total [I][/I][I][/I]    57,590  [I][/I][I][/I]      174  
         [color=#ff8c00] Newbe/Milestone [/color] =====================[I][/I][I][/I] =========  [I][/I][I][/I]=========  
         [color=#999795] Inactive        [/color] Uploaded last week   [I][/I][I][/I]         0  [I][/I][I][/I]        0  
         [color=#00bfff] .               [/color] Output +/- of        [color=#008000]    57,590 [/color] [color=#008000]      174 [/color]
         [color=#00bfff] .               [/color] ---------------------[I][/I][I][/I] ---------  [I][/I][I][/I]---------  
         [color=#00bfff] .               [/color]                      [I][/I][I][/I]            [I][/I][I][/I]           
         [color=#008000] Team Rank       [/color]        84 (+1)


[U]Top 10 Dumpers for the day:[/U]

|================================|
|   Member Name   |    Points    |
|--------------------------------|
| cImrie          |        9,885 |
| TriplexDread    |        7,553 |
| V_R             |        6,846 |
| AbsoluteKevster |        6,581 |
| Harvey          |        5,466 |
| Chris_Postill   |        3,440 |
| pikakilla       |        3,307 |
| Ian Cunningham  |        2,214 |
| muckshifter     |        2,028 |
| chelseafc2005   |        1,914 |
|================================|
[/color]
Movers of the day:
------------------
|================================|
|   Member Name   |    Points    |
|--------------------------------|
| TriplexDread    |(+1) 7553     |
|================================|

Win2kuser (XPC) WCG Stats Rev 1.7.2
Averages and inactive members calculated over 14 days


[/B][/SIZE]

 

[Win2Kuser]

Ah, right away I see a couple of things to modify already, the average points per job is still there, and the dividers don't stretch far enough across. Both easily fixed though, as is the nasty green date

 

[Ian]

That is great!!! I'm really excited about getting this running. Orange is fine for newbies/milestones - I guess I can switch the hex code over later if it gets confusing.

edit : I see you've just seen the green date - no probs

 

[Ian]

Just out of curiosity - does the code run within Excel as VBScript?