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.
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:
<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
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".
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
Well, we have the file, the sheet and the macro in Excel . 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.
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.
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.
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
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.
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.
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).
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)
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.
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.
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.
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!
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.
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!
...) 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).
