Conquer Club

Player data in Excel (from API)

Discuss developing tools and get details on Conquer Club's API.

Moderator: Tech Team

Forum rules
Please read the Community Guidelines before posting.

Player data in Excel (from API)

Postby GeneralFault on Sun Aug 23, 2015 8:28 am

In my search for statistics, I stumbled upon the API al long time ago. I wanted to use the API to select different things from all my played games for a better insight. I know, we have already a great Map Rank tool (topic here...), but i wanted to select and filter all my own games with my own options. Why Excel? Well.... why not... It's the most used application for statistics and i know a littlebit about programming VBA.

What's the goal?

Collect all player data from API in Excel

Chapter 1: Preparations

Chapter 2: First Execution Loops

Chapter 3: Main loop: First Game Data Loops

Chapter 4: Main loop: Second Game Data Loops

Chapter 5: Player loop in Main loop: the collection of players

Chapter 6: Event loop in Main loop: the collection of events

Chapter 7: The final Macro and the Excel sheet

PS: no worries, i will publish the full excel-file also when i'm finished

show: Wishlist and Roadmap
Last edited by GeneralFault on Fri Sep 18, 2015 7:49 am, edited 13 times in total.
Image
User avatar
Cook GeneralFault
 
Posts: 138
Joined: Fri Jul 11, 2008 4:02 am
Location: Leiden, the Netherlands

Re: Player data in Excel (from API)

Postby GeneralFault on Sun Aug 23, 2015 8:29 am

Chapter 1: Preparations

how to achieve it?

I start with the translation of the following API call:
http://www.conquerclub.com/api.php?mode=gamelist&un=generalfault&names=Y&gs=F&events=Y

where http://www.conquerclub.com/api.php? is the actual API-call
mode=gamelist returns a set of games
&un=generalfault where username is GeneralFault (in this example: mine)
&names=Y where in the player log usernames are used in stead of usernumbers
&gs=F where the game state is (F)inished
&events=Y where the eventlog is loaded aswell (for point extraction and date purposes)

Ok! Now the first tricky part. If you run the API-call in your browser, you will see that it starts with an <api> element and then with a <page> element. So they divided the XML-document in different pages to keep the speed of loading small. In my case it is 25 pages (and counting ... ;) ...) so i have to call 25 pages to collect all my data. Per page there is a limit of 200 games (in the <games> element). I have played to this date 4837 games, makes 24 pages of 200 games and 1 page of 37 games. All details of a game are listed in the <game> element. To make matters worse, sometimes data is not in the element, but in the attribute of an element and sometimes in the text of an element (but let's not get ahead of things).

so this makes the functional build up from the API as follows:
Code: Select all
<api>
     <page></page> :collection of pages
     <games> : collection of games
          <game> :collection of data in game
          </game>
     </games>
</api>


For now.... the basics of the sheet

Starting with.....
The making of a new Excelsheet. After that make create a new sheet en rename it to: PlayerData (important) If you don't so this, Excel will not understand the macro because this sheet will be called explicitly.

The making of a new Macro in Excel. I work with Excel 2013, but for Excel 2010 this should be the same.... (please pm me, if it is not). I always install the developer tools in my ribbon for quick access. Go to options for excel, Choose edit Ribbon and check the Developers box. Now you should have the tab Developers in your ribbon. Click on Visual Basic (or press Alt+F11) and you are in the editor of Visual Basic, where the macro's are stored. I the left of you screen, you should see a VBA-project of your Excel file. If you double click on this Workbook, it should load an empty canvas to make your macro.

I'm not going to explain everything of the VBA-language or my choices (because this is a development environment of CC, not Excel). But the beginning of the macro must look like this
Code: Select all
Sub PlayerData()
End Sub


We need to load every variable we are going to use and make them 'known' to the routine. Don't Worry, i will explain later why we need al these vars.
show: Base of the Routine
'

Hold On!!! to let the XML reference coding work, you have to import the general XML references. This article will help you do this (really, peace of cake)

The first bit of code must be to load the XML file and to make sure that we retreive the number of pages for loading the pages one by one. So we have to select the first child of the XML-file (the element "page") and read its value and store it in the variable nrPlayers. The second child in the XML-file ( the element 'games) has an attribute that will tell us the number of games played. We put this in the variable nrGames and in the excel file on Cell 1,1 which is the same as "A1".
show: Preparation of the macro


I like to close my macro's with a message, so i actually know that it runs succesfully. Therefore i put a messagebox in the macro, at the end.
show: end of the macro


So the macro now looks like
show: Final Macro


Happy coding!! :idea: :idea:
Last edited by GeneralFault on Mon Aug 31, 2015 9:46 am, edited 3 times in total.
Image
User avatar
Cook GeneralFault
 
Posts: 138
Joined: Fri Jul 11, 2008 4:02 am
Location: Leiden, the Netherlands

Re: Player data in Excel (from API)

Postby sempaispellcheck on Sun Aug 23, 2015 8:06 pm

A most excellent endeavor. Best of luck, man. I'm sure CCers by the hundreds will be beating down your door when this is done.
If there's anything I can do to help, please feel free to ask.

sempai
High score: 2200 - July 20, 2015
Game 13890915 - in which I helped clinch the NC4 title for LHDD

Image
User avatar
Lieutenant sempaispellcheck
 
Posts: 2852
Joined: Fri Sep 10, 2010 10:31 pm
Location: Among the clouds and the skyscrapers, saving the world.

Re: Player data in Excel (from API)

Postby GeneralFault on Mon Aug 24, 2015 11:07 am

Thanks sempaispellcheck. Thank god that i'm now still able to get ahead.... otherwise we have a problem!

Next challenge.... polymorphic games......

i'm busy to write the code that can deal with the polymorphic games... and i'm close to cracking that one.....

then a few other roadblocks to crush.....
Image
User avatar
Cook GeneralFault
 
Posts: 138
Joined: Fri Jul 11, 2008 4:02 am
Location: Leiden, the Netherlands

Re: Player data in Excel (from API)

Postby GeneralFault on Mon Aug 24, 2015 11:14 am

Chapter 2: First execution loops

Well, we have the file, the sheet and the macro in Excel :D :D . Bare with me, we are coming to the fun part now..... In our preparation part we have retrieved the number of pages, so with a little counter and the variable of the number of pages we can loop through them. Watch ehat happens in the variable Url_API. Everytime the pagenumber is going up and after 25 times, it stops. In the loop we have to load everytime the new page, to retrieve all games.... That's why the SET-statement is used.

show: First code for looping through pages


Now we have to load the XML from the API into our macro. We store (like in the preparations block) the XML file in the xmlDoc variable. By using a With statement, we can easely make a search. After loading the file the correct child of the XML must be found, so we set a node to populate the first child in the childnote ("games") which should contain one game. The number of nodes is neccesary to loop through all nodes in the element <games>. Or in plain English, give me for this XML-page, every single game in the collection games. The with statement always closes with an End With statement, and as a comment I always put the relevant variable with it.
show: Loading XML in one page


Finally we can loop through every game. Again we have to use a with statement. This time we use the variable nrNodes so we know how many times we have to use the loop. Because we are going to use XML elements, we have to start with counting of the element 0 (the first element of an XML file) this is why we count from 0 to numbernodes -1. The counter h will define on which row in our Excelsheet the data is stored.
show: Looping through the games


So the macro now looks like
show: Final Macro


Happy coding!! :idea: :idea:
Last edited by GeneralFault on Mon Aug 24, 2015 1:04 pm, edited 1 time in total.
Image
User avatar
Cook GeneralFault
 
Posts: 138
Joined: Fri Jul 11, 2008 4:02 am
Location: Leiden, the Netherlands

Re: Player data in Excel (from API)

Postby Army of GOD on Mon Aug 24, 2015 1:03 pm

yuck, VBA is so ugly

so what's the end result of the routine? (ie waht does it look like in Excel)
mrswdk is a ho
User avatar
Lieutenant Army of GOD
 
Posts: 7172
Joined: Tue Feb 24, 2009 4:30 pm

Re: Player data in Excel (from API)

Postby GeneralFault on Mon Aug 24, 2015 1:06 pm

VBA is ugly, but it does the job...

the result is a sheet in excel with all finished games of one player including all details and sortable and filterable in every way you like. Including a graph with your score-progress

after that, who knows?
Image
User avatar
Cook GeneralFault
 
Posts: 138
Joined: Fri Jul 11, 2008 4:02 am
Location: Leiden, the Netherlands

Re: Player data in Excel (from API)

Postby sempaispellcheck on Mon Aug 24, 2015 8:05 pm

FYI - for those reading this later:
Office 2008 for Mac does not have VBA, so it cannot be used to create macros.

sempai
High score: 2200 - July 20, 2015
Game 13890915 - in which I helped clinch the NC4 title for LHDD

Image
User avatar
Lieutenant sempaispellcheck
 
Posts: 2852
Joined: Fri Sep 10, 2010 10:31 pm
Location: Among the clouds and the skyscrapers, saving the world.

Re: Player data in Excel (from API)

Postby GeneralFault on Tue Aug 25, 2015 12:20 pm

sempaispellcheck wrote:FYI - for those reading this later:
Office 2008 for Mac does not have VBA, so it cannot be used to create macros.

sempai


Indeed, you have to have Office 2011 for Mac or higher.....
Image
User avatar
Cook GeneralFault
 
Posts: 138
Joined: Fri Jul 11, 2008 4:02 am
Location: Leiden, the Netherlands

Re: Player data in Excel (from API)

Postby sempaispellcheck on Thu Aug 27, 2015 5:15 am

GeneralFault wrote:
sempaispellcheck wrote:FYI - for those reading this later:
Office 2008 for Mac does not have VBA, so it cannot be used to create macros.

sempai


Indeed, you have to have Office 2011 for Mac or higher.....

Office 2004 will work, too. *scratches head*

Meh, I have my hard drive partitioned, so I can also run Windows (and Office for Windows) on my Mac. :D

sempai
High score: 2200 - July 20, 2015
Game 13890915 - in which I helped clinch the NC4 title for LHDD

Image
User avatar
Lieutenant sempaispellcheck
 
Posts: 2852
Joined: Fri Sep 10, 2010 10:31 pm
Location: Among the clouds and the skyscrapers, saving the world.

Re: Player data in Excel (from API)

Postby GeneralFault on Thu Aug 27, 2015 8:18 am

sempaispellcheck wrote:Meh, I have my hard drive partitioned, so I can also run Windows (and Office for Windows) on my Mac. :D
sempai


Finally, a real OS on your laptop :lol: :lol:
Image
User avatar
Cook GeneralFault
 
Posts: 138
Joined: Fri Jul 11, 2008 4:02 am
Location: Leiden, the Netherlands

Re: Player data in Excel (from API)

Postby sempaispellcheck on Thu Aug 27, 2015 4:32 pm

GeneralFault wrote:
sempaispellcheck wrote:Meh, I have my hard drive partitioned, so I can also run Windows (and Office for Windows) on my Mac. :D
sempai


Finally, a real OS on your laptop :lol: :lol:

Image

sempai
High score: 2200 - July 20, 2015
Game 13890915 - in which I helped clinch the NC4 title for LHDD

Image
User avatar
Lieutenant sempaispellcheck
 
Posts: 2852
Joined: Fri Sep 10, 2010 10:31 pm
Location: Among the clouds and the skyscrapers, saving the world.

Re: Player data in Excel (from API)

Postby GeneralFault on Sun Aug 30, 2015 9:15 am

Chapter 3: Main loop: First Game Data Loops

All the loops are constructed, so now we can finally focus on the real game data. Per game, we are retrieving all the elements and we are going to interpret them one by one. To loop through the data, we must use another With statement, so we can read every childnode that is part of the collection game.
show: With the game


The first Element is the game number, nothing special. We select the correct element, and let it write to the correct column in excel, with the row number retrieved from the previous post.
show: game number


We skip the second and the third row for now, to add the player state and the score later.
show: player state and score


Select the game state: The options are (W)aiting, (A)ctive or (F)inished.
show: game state


Retrieve the tournament name. If the tournament is an official tournament of the CC-community, the tag is populated with the name.
show: tournament name


The private tag is the tag which seperates the N(Public), Y(Private) , T(Tournament) games
show: private or public game


Retrieve the speed game settings. N(Casual), 1(1min Speed), 2(1min Speed), 3(1min Speed), 4(1min Speed), 5(1min Speed). (this is different then the API guide is explaining).
show: speed game


Retrieve the name of the played map.
show: Map name


So the macro now looks like
show: Final Macro


Happy coding!! :idea: :idea:
Image
User avatar
Cook GeneralFault
 
Posts: 138
Joined: Fri Jul 11, 2008 4:02 am
Location: Leiden, the Netherlands

Re: Player data in Excel (from API)

Postby GeneralFault on Sun Aug 30, 2015 12:54 pm

Chapter 4: Main loop: Second Game Data Loops

Progressing with the game data loop.

The next element is the game type. There are various options for a game. S(Standard), C(Terminator), A(Assassin), D(Doubles), T(Triples), Q(Quadruples), P(Polymorphic)
show: game type


The element initial troops holds the information about the manual or automatisch troop deployment.
show: initial troops


The play order is the next element. S(Sequential), F(Freestyle)
show: play order


The next element is the options to play the bonus cards; 2 (Escalating) , 3 (Flat Rate) , 1 (No Spoils), 4(Nuclear), 5(Zombie)
show: Bonus Cards


There are several options for the element Fortifications. C(Chained), O(Adjaecent), M(Unlimited), P(Parachute), N(None)
show: Fortifications


The next element is Fog of War. There's only fog or no fog, so that's fairly simple.
show: Fog of War


The next element is Trench. Als simple, just the options yes or no :-)
show: Trench Warfare


After that comes the element round limit. The options are a bit more varied; round_limit - 0, 20, 30, 50, 100 rounds. 0 rounds is the same ad umlimited.
show: Round limit


Then there are two simple nodes. The element round, which indicates which round the game is in or is finished and the element Poly slots. This returns the number of ploy slots you have played.
show: Round and Poly Slots


Then there is time remaining as an element. For finished games it's obious that there is no more time. Later on (in the future) we might need it to convert. After this element, we skip one column to enter the date of the game.
show: Final Macro


So the macro now looks like
show: Final Macro


Happy coding!! :idea: :idea:
Image
User avatar
Cook GeneralFault
 
Posts: 138
Joined: Fri Jul 11, 2008 4:02 am
Location: Leiden, the Netherlands

Re: Player data in Excel (from API)

Postby GeneralFault on Mon Aug 31, 2015 9:50 am

Chapter 5: Player loop in Main loop: the collection of players

For now, another loop in a loop..... All the players are in the collection of the element players. Each player has its own element. So what we do is we set the cell valua at the and of the last column, so we do not have to worry about how many players there are. It's going to be player and another column. In Cell 21 we store the amount of players. Then we compare the player element with the playername is our query (in my case it's GeneralFault) and when it's found we store the attribute state in the second column. This tells us if we lost or won!

WATCH: If you assign the first time the playernumber, the second time it's goes to the same loop, it skips the playernumber. This is done to get the correct ID for poly games.

There are also playernames starting with an "=" sign. Excel cannot handle those, so we have to put a space before the playername. Then we cycle through all players and store them in the columns. There is a possibility that if you played the Colosseum map, that there are 52 players!


show: Final Macro


Happy coding!! :idea: :idea:
Image
User avatar
Cook GeneralFault
 
Posts: 138
Joined: Fri Jul 11, 2008 4:02 am
Location: Leiden, the Netherlands

Re: Player data in Excel (from API)

Postby GeneralFault on Wed Sep 16, 2015 9:51 am

Chapter 6: Event loop in Main loop: the collection of events

The eventnode is needed to retreive the date of the game played. We have to convert the date from the unix date to a real 'human' date. After that we can retrieve all the strings from the eventstring. We have to split the loop because of the number of digits used by the playernumber. Then we cut the string up to get the number of points lost or gained. We store the them in the correct cells et voilá ... we are done! t the final loop, we can alterr the date to our individual settings. In my case, it's dutch, but feel free to change it to whatever you would like.

show: Final Macro


Happy coding!! :idea: :idea:
Image
User avatar
Cook GeneralFault
 
Posts: 138
Joined: Fri Jul 11, 2008 4:02 am
Location: Leiden, the Netherlands

Re: Player data in Excel (from API)

Postby GeneralFault on Fri Sep 18, 2015 7:46 am

Chapter 7: Final Macro

Here is the final macro to extract all player data from the API. I'm going to give my energy to the next fases in the project, namely the GUI and the scoring graph!

if you would like to be smart and you do not want to code it yourself, you can download the excelsheet here.

show: The Whole Final Macro


Happy coding!! :idea: :idea:
Image
User avatar
Cook GeneralFault
 
Posts: 138
Joined: Fri Jul 11, 2008 4:02 am
Location: Leiden, the Netherlands

Re: Player data in Excel (from API)

Postby Robespierre__ on Sun Sep 20, 2015 1:52 pm

You are the man .... I sadly never studied any code really so am lost in terms of implementation, but it is still cool and valuable to many others.
Image
User avatar
Colonel Robespierre__
 
Posts: 477
Joined: Sat Jun 28, 2008 2:23 pm
Location: New Jersey


Return to Tools Development

Who is online

Users browsing this forum: No registered users

cron