Player data in Excel (from API)

[GeneralFault]

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

[spoiler=Wishlist and Roadmap]

• Collect game data
• Collect players data
• Collect event data
• Graphical user interface
• Make search list via input form
• common statistics details

[/spoiler]

[GeneralFault]

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.
[spoiler=Base of the Routine]

Code: Select all

Sub PlayerData()
'routine to load all player games and their details
'written by GeneralFault

'loading all variables used in this subroutine
Dim Url_API, strPages, strPlName, strEvent, strTemp, strState, chkBlank As String
Dim xmlDoc As MSXML2.DOMDocument60
Dim xNode, yNode As MSXML2.IXMLDOMNode
Dim nrPages, nrGames, nrNodes, nrPlayers, nrEvents, Points As Integer
Dim h, i, j, k, m, PlayerNumber As Integer
End Sub

[/spoiler]'

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".
[spoiler=Preparation of the macro]

Code: Select all

'----------------------------------------Preparation----------------------------------------
'populate all relevant variables
Url_API = "http://www.conquerclub.com/api.php?mode=gamelist&un=generalfault&names=Y&gs=F&events=Y"

'preparing execution by retreiving first xml-batch
Set xmlDoc = New MSXML2.DOMDocument60
With xmlDoc
    'Load the xml from CC API
    .async = False
    .validateOnParse = False
    .Load (Url_API)

    'select first node to get the number of pages
    Set xNode = .FirstChild.SelectSingleNode("page")
    With xNode
        strPages = xNode.nodeTypedValue
        'trim string to number
        strPages = Mid(strPages, 5)
        nrPages = CInt(strPages)
    End With 'xNode pages
    
    'select second node to get the number of games played
    Set xNode = .FirstChild.SelectSingleNode("games")
    With xNode
        nrGames = xNode.Attributes.getNamedItem("total").NodeValue
    End With 'xNode games

    Worksheets("PlayerData").Select
    Cells(1, 1).Value = "Number of games in total: " & nrGames
    
End With 'xmlDoc

[/spoiler]

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.
[spoiler=end of the macro]

Code: Select all

MsgBox "done"

[/spoiler]

So the macro now looks like
[spoiler=Final Macro]

Code: Select all

Sub PlayerData()
'routine to load all player games and their details
'written by GeneralFault

'loading all variables used in this subroutine
Dim Url_API, strPages, strPlName, strEvent, strTemp, strState, chkBlank As String
Dim xmlDoc As MSXML2.DOMDocument60
Dim xNode, yNode As MSXML2.IXMLDOMNode
Dim nrPages, nrGames, nrNodes, nrPlayers, nrEvents, Points As Integer
Dim h, i, j, k, m, PlayerNumber As Integer

'----------------------------------------Preparation----------------------------------------
'populate all relevant variables
Url_API = "http://www.conquerclub.com/api.php?mode=gamelist&un=generalfault&names=Y&gs=F&events=Y"

'preparing execution by retreiving first xml-batch
Set xmlDoc = New MSXML2.DOMDocument60
With xmlDoc
    'Load the xml from CC API
    .async = False
    .validateOnParse = False
    .Load (Url_API)

    'select first node to get the number of pages
    Set xNode = .FirstChild.SelectSingleNode("page")
    With xNode
        strPages = xNode.nodeTypedValue
        'trim string to number
        strPages = Mid(strPages, 5)
        nrPages = CInt(strPages)
    End With 'xNode pages
    
    'select second node to get the number of games played
    Set xNode = .FirstChild.SelectSingleNode("games")
    With xNode
        nrGames = xNode.Attributes.getNamedItem("total").NodeValue
    End With 'xNode games

    Worksheets("PlayerData").Select
    Cells(1, 1).Value = "Number of games in total: " & nrGames
    
End With 'xmlDoc

MsgBox "done"

End Sub

[/spoiler]

Happy coding!!

[sempaispellcheck]

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

[GeneralFault]

Thanks [player]sempaispellcheck[/player]. 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.....

[GeneralFault]

Chapter 2: First execution loops

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.

[spoiler=First code for looping through pages]

Code: Select all

'----------------------------------------Execution----------------------------------------
'setting h as counter through all games
'starting at row 4
h = 3

For i = 1 To nrPages
    
    Url_API = "http://www.conquerclub.com/api.php?mode=gamelist&un=generalfault&names=Y&gs=F&events=Y" & Chr(38) & "page=" & i
    
    'load the correct api in the for-loop
    Set xmlDoc = New MSXML2.DOMDocument60

next

[/spoiler]

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.
[spoiler=Loading XML in one page]

Code: Select all

With xmlDoc
        .async = False
        .validateOnParse = False
        .Load (Url_API)
    
        Set xNode = .FirstChild.SelectSingleNode("games")
        nrNodes = xNode.ChildNodes.Length

end with 'xmldoc

[/spoiler]

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.
[spoiler=Looping through the games]

Code: Select all

'get all childnodes to populate the details
        With xNode.ChildNodes
            For j = 0 To nrNodes - 1 'TIJDELIJK!!!
                h = h + 1
            Next
       End With 'xnode

[/spoiler]

So the macro now looks like
[spoiler=Final Macro]

Code: Select all

Sub PlayerData()
'routine to load all player games and their details
'written by GeneralFault

'loading all variables used in this subroutine
Dim Url_API, strPages, strPlName, strEvent, strTemp, strState, chkBlank As String
Dim xmlDoc As MSXML2.DOMDocument60
Dim xNode, yNode As MSXML2.IXMLDOMNode
Dim nrPages, nrGames, nrNodes, nrPlayers, nrEvents, Points As Integer
Dim h, i, j, k, m, PlayerNumber As Integer

'----------------------------------------Preparation----------------------------------------
'populate all relevant variables
Url_API = "http://www.conquerclub.com/api.php?mode=gamelist&un=generalfault&names=Y&gs=F&events=Y"

'preparing execution by retreiving first xml-batch
Set xmlDoc = New MSXML2.DOMDocument60
With xmlDoc
    'Load the xml from CC API
    .async = False
    .validateOnParse = False
    .Load (Url_API)

    'select first node to get the number of pages
    Set xNode = .FirstChild.SelectSingleNode("page")
    With xNode
        strPages = xNode.nodeTypedValue
        'trim string to number
        strPages = Mid(strPages, 5)
        nrPages = CInt(strPages)
    End With 'xNode pages
    
    'select second node to get the number of games played
    Set xNode = .FirstChild.SelectSingleNode("games")
    With xNode
        nrGames = xNode.Attributes.getNamedItem("total").NodeValue
    End With 'xNode games

    Worksheets("PlayerData").Select
    Cells(1, 1).Value = "Number of games in total: " & nrGames
    
End With 'xmlDoc

'----------------------------------------Execution----------------------------------------
'setting h as counter through all games
'starting at row 4
h = 3

For i = 1 To nrPages 'TIJDELIJK!!!
    
    Url_API = "http://www.conquerclub.com/api.php?mode=gamelist&un=generalfault&names=Y&gs=F&events=Y" & Chr(38) & "page=" & i
    
    'load the correct api in the for-loop
    Set xmlDoc = New MSXML2.DOMDocument60
    
    With xmlDoc
        .async = False
        .validateOnParse = False
        .Load (Url_API)
    
        Set xNode = .FirstChild.SelectSingleNode("games")
        nrNodes = xNode.ChildNodes.Length
        
        'get all childnodes to populate the details
        With xNode.ChildNodes
            For j = 0 To nrNodes - 1 'TIJDELIJK!!!
                h = h + 1

            Next
        End With 'xnode
    
    End With 'xmldoc

Next


MsgBox "done"

End Sub

[/spoiler]

Happy coding!!

[Army of GOD]

yuck, VBA is so ugly

so what's the end result of the routine? (ie waht does it look like in Excel)

[GeneralFault]

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?

[sempaispellcheck]

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

sempai

[GeneralFault]

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.....

[sempaispellcheck]

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.

sempai

[GeneralFault]

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

Finally, a real OS on your laptop

[sempaispellcheck]

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

Finally, a real OS on your laptop

sempai

[GeneralFault]

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.
[spoiler=With the game]

Code: Select all

With xNode.ChildNodes(j)
End with

[/spoiler]

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.
[spoiler=game number]

Code: Select all

 '-------------------- -------------------- --------------------
Cells(h, 1).Value = .SelectSingleNode("game_number").Text

[/spoiler]

We skip the second and the third row for now, to add the player state and the score later.
[spoiler=player state and score]

Code: Select all

-------------------- -------------------- --------------------
skip one to enter player state later
skip another one to enter player score later

[/spoiler]

Select the game state: The options are (W)aiting, (A)ctive or (F)inished.
[spoiler=game state]

Code: Select all

-------------------- -------------------- --------------------
strTemp = .SelectSingleNode("game_state").Text
gs - game state (W)aiting, (A)ctive or (F)inished
Select Case strTemp
     Case "W"
          strTemp = "Waiting"
     Case "A"
          strTemp = "Active"
     Case "F"
          strTemp = "Finished"
     Case Else
          strTemp = "Unknown"
End Select
Cells(h, 4).Value = strTemp

[/spoiler]

Retrieve the tournament name. If the tournament is an official tournament of the CC-community, the tag is populated with the name.
[spoiler=tournament name]

Code: Select all

-------------------- -------------------- --------------------
Cells(h, 5).Value = .SelectSingleNode("tournament").Text

[/spoiler]

The private tag is the tag which seperates the N(Public), Y(Private) , T(Tournament) games
[spoiler=private or public game]

Code: Select all

'-------------------- -------------------- --------------------
strTemp = .SelectSingleNode("private").Text
'private - N(Public), Y(Private) , T(Tournament)
Select Case strTemp
     Case "N"
          strTemp = "Public"
     Case "Y"
          strTemp = "Private"
     Case "T"
          strTemp = "Tournament"
     Case Else
          strTemp = "Unknown"
End Select
Cells(h, 6).Value = strTemp

[/spoiler]

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).
[spoiler=speed game]

Code: Select all

strTemp = .SelectSingleNode("speed_game").Text
'speed_game - N(Casual), 1(1min Speed), 2(1min Speed), 3(1min Speed), 4(1min Speed), 5(1min Speed)
'different than the API explanation
Select Case strTemp
     Case "N"
          strTemp = "Casual"
     Case "Y"
          strTemp = "Speed"
     Case "S"
          strTemp = "Speed"
     Case "1"
          strTemp = "1 min Speed"
     Case "2"
          strTemp = "2 min Speed"
     Case "3"
          strTemp = "3 min Speed"
     Case "4"
          strTemp = "4 min Speed"
     Case "5"
          strTemp = "5 min Speed"
     Case Else
          strTemp = "Unknown"
End Select
Cells(h, 7).Value = strTemp

[/spoiler]

Retrieve the name of the played map.
[spoiler=Map name]

Code: Select all

'-------------------- -------------------- --------------------
Cells(h, 8).Value = .SelectSingleNode("map").Text

[/spoiler]

So the macro now looks like
[spoiler=Final Macro]

Code: Select all

                With xNode.ChildNodes(j)
                    '-------------------- -------------------- --------------------
                    Cells(h, 1).Value = .SelectSingleNode("game_number").Text
                    '-------------------- -------------------- --------------------
                    'skip one to enter player state later
                    'skip another one to enter player score later
                    '-------------------- -------------------- --------------------
                    strTemp = .SelectSingleNode("game_state").Text
                    'gs - game state (W)aiting, (A)ctive or (F)inished
                    Select Case strTemp
                        Case "W"
                            strTemp = "Waiting"
                        Case "A"
                            strTemp = "Active"
                        Case "F"
                            strTemp = "Finished"
                        Case Else
                            strTemp = "Unknown"
                    End Select
                    Cells(h, 4).Value = strTemp
                    '-------------------- -------------------- --------------------
                    Cells(h, 5).Value = .SelectSingleNode("tournament").Text
                    '-------------------- -------------------- --------------------
                    strTemp = .SelectSingleNode("private").Text
                    'private - N(Public), Y(Private) , T(Tournament)
                    Select Case strTemp
                        Case "N"
                            strTemp = "Public"
                        Case "Y"
                            strTemp = "Private"
                        Case "T"
                            strTemp = "Tournament"
                        Case Else
                            strTemp = "Unknown"
                    End Select
                    Cells(h, 6).Value = strTemp
                    '-------------------- -------------------- --------------------
                    strTemp = .SelectSingleNode("speed_game").Text
                    'speed_game - N(Casual), 1(1min Speed), 2(1min Speed), 3(1min Speed), 4(1min Speed), 5(1min Speed)
                    'different than the API explanation
                    Select Case strTemp
                        Case "N"
                            strTemp = "Casual"
                        Case "Y"
                            strTemp = "Speed"
                        Case "S"
                            strTemp = "Speed"
                        Case "1"
                            strTemp = "1 min Speed"
                        Case "2"
                            strTemp = "2 min Speed"
                        Case "3"
                            strTemp = "3 min Speed"
                        Case "4"
                            strTemp = "4 min Speed"
                        Case "5"
                            strTemp = "5 min Speed"
                        Case Else
                            strTemp = "Unknown"
                    End Select
                    Cells(h, 7).Value = strTemp
                    '-------------------- -------------------- --------------------
                    Cells(h, 8).Value = .SelectSingleNode("map").Text
      end with

[/spoiler]

Happy coding!!

[GeneralFault]

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)
[spoiler=game type]

Code: Select all

'-------------------- -------------------- --------------------
strTemp = .SelectSingleNode("game_type").Text
'game_type - S(Standard), C(Terminator), A(Assassin), D(Doubles), T(Triples), Q(Quadruples), P(Polymorphic)
Select Case strTemp
     Case "S"
          strTemp = "Standard"
     Case "C"
          strTemp = "Terminator"
     Case "A"
          strTemp = "Assassin"
     Case "D"
          strTemp = "Doubles"
     Case "T"
          strTemp = "Triples"
     Case "Q"
          strTemp = "Quadruples"
     Case "P"
          strTemp = "Polymorphic"
     Case Else
          strTemp = "Unknown"
End Select
Cells(h, 9).Value = strTemp

[/spoiler]

The element initial troops holds the information about the manual or automatisch troop deployment.
[spoiler=initial troops]

Code: Select all

'-------------------- -------------------- --------------------
strTemp = .SelectSingleNode("initial_troops").Text
'initial_troops - E(Automatic), M(Manual)
Select Case strTemp
     Case "E"
            strTemp = "Automatic"
     Case "M"
            strTemp = "Manual"
     Case Else
            strTemp = "Unknown"
End Select
Cells(h, 10).Value = strTemp

[/spoiler]

The play order is the next element. S(Sequential), F(Freestyle)
[spoiler=play order]

Code: Select all

'-------------------- -------------------- --------------------
strTemp = .SelectSingleNode("play_order").Text
'play_order - S(Sequential), F(Freestyle)
Select Case strTemp
     Case "S"
          strTemp = "Sequential"
     Case "F"
          strTemp = "Freestyle"
     Case Else
          strTemp = "Unknown"
End Select
Cells(h, 11).Value = strTemp

[/spoiler]

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

Code: Select all

'-------------------- -------------------- --------------------
strTemp = .SelectSingleNode("bonus_cards").Text
'bonus_cards - 2 (Escalating) , 3 (Flat Rate) , 1 (No Spoils), 4(Nuclear), 5(Zombie)
Select Case strTemp
     Case "1"
           strTemp = "No Spoils"
     Case "2"
           strTemp = "Escalating"
     Case "3"
           strTemp = "Flat Rate"
     Case "4"
           strTemp = "Nuclear"
     Case "5"
           strTemp = "Zombie"
     Case Else
          strTemp = "Unknown"
End Select
Cells(h, 12).Value = strTemp

[/spoiler]

There are several options for the element Fortifications. C(Chained), O(Adjaecent), M(Unlimited), P(Parachute), N(None)
[spoiler=Fortifications]

Code: Select all

'-------------------- -------------------- --------------------
strTemp = .SelectSingleNode("fortifications").Text
'fortifications - C(Chained), O(Adjaecent), M(Unlimited), P(Parachute), N(None)
Select Case strTemp
     Case "C"
          strTemp = "Chained"
     Case "O"
          strTemp = "Adjacent"
     Case "M"
          strTemp = "Unlimited"
     Case "P"
          strTemp = "Parachute"
     Case "N"
          strTemp = "None"
     Case Else
           strTemp = "Unknown"
End Select
Cells(h, 13).Value = strTemp

[/spoiler]

The next element is Fog of War. There's only fog or no fog, so that's fairly simple.
[spoiler=Fog of War]

Code: Select all

'-------------------- -------------------- --------------------
strTemp = .SelectSingleNode("war_fog").Text
'war_fog -  N(No Fog) or Y(Fog)
Select Case strTemp
     Case "N"
          strTemp = "No Fog"
     Case "Y"
          strTemp = "Fog"
     Case Else
          strTemp = "Unknown"
End Select
Cells(h, 14).Value = strTemp

[/spoiler]

The next element is Trench. Als simple, just the options yes or no
[spoiler=Trench Warfare]

Code: Select all

strTemp = .SelectSingleNode("trench_warfare").Text
'trench_warfare - (Y)es, (N)o
Select Case strTemp
     Case "Y"
          strTemp = "Trench"
     Case "N"
          strTemp = "No Trench"
     Case Else
          strTemp = "Unknown"
End Select
Cells(h, 15).Value = strTemp

[/spoiler]

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.
[spoiler=Round limit]

Code: Select all

'-------------------- -------------------- --------------------
strTemp = .SelectSingleNode("round_limit").Text
'round_limit - 0, 20, 30, 50, 100
Select Case strTemp
     Case "0"
           strTemp = "No limit"
     Case "20"
           strTemp = "20 rounds"
     Case "30"
           strTemp = "30 rounds"
     Case "50"
           strTemp = "50 rounds"
     Case "100"
           strTemp = "100 rounds"
     Case Else
           strTemp = "Unknown"
End Select
Cells(h, 16).Value = strTemp

[/spoiler]

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.
[spoiler=Round and Poly Slots]

Code: Select all

'-------------------- -------------------- --------------------
Cells(h, 17).Value = .SelectSingleNode("round").Text
'-------------------- -------------------- --------------------
Cells(h, 18).Value = .SelectSingleNode("poly_slots").Text
'-------------------- -------------------- --------------------

[/spoiler]

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.
[spoiler=Final Macro]

Code: Select all

'-------------------- -------------------- --------------------
strDate = .SelectSingleNode("time_remaining").Text
If strDate = 0 And Cells(h, 4).Value = "Finished" Then
     Cells(h, 18).Value = Finished
Else
     'calculate later
End If
'-------------------- -------------------- --------------------
'skip one for date

[/spoiler]

So the macro now looks like
[spoiler=Final Macro]

Code: Select all

                    '-------------------- -------------------- --------------------
                    strTemp = .SelectSingleNode("game_type").Text
                    'game_type - S(Standard), C(Terminator), A(Assassin), D(Doubles), T(Triples), Q(Quadruples), P(Polymorphic)
                    Select Case strTemp
                        Case "S"
                            strTemp = "Standard"
                        Case "C"
                            strTemp = "Terminator"
                        Case "A"
                            strTemp = "Assassin"
                        Case "D"
                            strTemp = "Doubles"
                        Case "T"
                            strTemp = "Triples"
                        Case "Q"
                            strTemp = "Quadruples"
                        Case "P"
                            strTemp = "Polymorphic"
                        Case Else
                            strTemp = "Unknown"
                    End Select
                    Cells(h, 9).Value = strTemp
                    '-------------------- -------------------- --------------------
                    strTemp = .SelectSingleNode("initial_troops").Text
                    'initial_troops - E(Automatic), M(Manual)
                    Select Case strTemp
                        Case "E"
                            strTemp = "Automatic"
                        Case "M"
                            strTemp = "Manual"
                        Case Else
                            strTemp = "Unknown"
                    End Select
                    Cells(h, 10).Value = strTemp
                    '-------------------- -------------------- --------------------
                    strTemp = .SelectSingleNode("play_order").Text
                    'play_order - S(Sequential), F(Freestyle)
                    Select Case strTemp
                        Case "S"
                            strTemp = "Sequential"
                        Case "F"
                            strTemp = "Freestyle"
                        Case Else
                            strTemp = "Unknown"
                    End Select
                    Cells(h, 11).Value = strTemp
                    '-------------------- -------------------- --------------------
                    strTemp = .SelectSingleNode("bonus_cards").Text
                    'bonus_cards - 2 (Escalating) , 3 (Flat Rate) , 1 (No Spoils), 4(Nuclear), 5(Zombie)
                    Select Case strTemp
                        Case "1"
                            strTemp = "No Spoils"
                        Case "2"
                            strTemp = "Escalating"
                        Case "3"
                            strTemp = "Flat Rate"
                        Case "4"
                            strTemp = "Nuclear"
                        Case "5"
                            strTemp = "Zombie"
                        Case Else
                            strTemp = "Unknown"
                    End Select
                    Cells(h, 12).Value = strTemp
                    '-------------------- -------------------- --------------------
                    strTemp = .SelectSingleNode("fortifications").Text
                    'fortifications - C(Chained), O(Adjaecent), M(Unlimited), P(Parachute), N(None)
                    Select Case strTemp
                        Case "C"
                            strTemp = "Chained"
                        Case "O"
                            strTemp = "Adjacent"
                        Case "M"
                            strTemp = "Unlimited"
                        Case "P"
                            strTemp = "Parachute"
                        Case "N"
                            strTemp = "None"
                        Case Else
                            strTemp = "Unknown"
                    End Select
                    Cells(h, 13).Value = strTemp
                    '-------------------- -------------------- --------------------
                    strTemp = .SelectSingleNode("war_fog").Text
                    'war_fog -  N(No Fog) or Y(Fog)
                    Select Case strTemp
                        Case "N"
                            strTemp = "No Fog"
                        Case "Y"
                            strTemp = "Fog"
                        Case Else
                            strTemp = "Unknown"
                    End Select
                    Cells(h, 14).Value = strTemp
                    '-------------------- -------------------- --------------------
                    strTemp = .SelectSingleNode("trench_warfare").Text
                    'trench_warfare - (Y)es, (N)o
                    Select Case strTemp
                        Case "Y"
                            strTemp = "Trench"
                        Case "N"
                            strTemp = "No Trench"
                        Case Else
                            strTemp = "Unknown"
                    End Select
                    Cells(h, 15).Value = strTemp
                    '-------------------- -------------------- --------------------
                    strTemp = .SelectSingleNode("round_limit").Text
                    'round_limit - 0, 20, 30, 50, 100
                    Select Case strTemp
                        Case "0"
                            strTemp = "No limit"
                        Case "20"
                            strTemp = "20 rounds"
                        Case "30"
                            strTemp = "30 rounds"
                        Case "50"
                            strTemp = "50 rounds"
                        Case "100"
                            strTemp = "100 rounds"
                        Case Else
                            strTemp = "Unknown"
                    End Select
                    Cells(h, 16).Value = strTemp
                    '-------------------- -------------------- --------------------
                    Cells(h, 17).Value = .SelectSingleNode("round").Text
                    '-------------------- -------------------- --------------------
                    Cells(h, 18).Value = .SelectSingleNode("poly_slots").Text
                    '-------------------- -------------------- --------------------
                    strDate = .SelectSingleNode("time_remaining").Text
                    If strDate = 0 And Cells(h, 4).Value = "Finished" Then
                        Cells(h, 18).Value = Finished
                    Else
                        'calculate later
                    End If
                    '-------------------- -------------------- --------------------
                    'skip one for date

[/spoiler]

Happy coding!!

[GeneralFault]

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!


[spoiler=Final Macro]

Code: Select all

'-------------------- -------------------- --------------------
                    'childnode(18) is the collection of players
                    With xNode.ChildNodes(j).ChildNodes(18)
                        k = 22
                        nrPlayers = xNode.ChildNodes(j).ChildNodes(18).ChildNodes.Length
                        Cells(h, 21).Value = nrPlayers
                        PlayerNumber = 99
                        For m = 0 To nrPlayers - 1
                            strPlName = .ChildNodes(m).nodeTypedValue
                            If strPlName = "GeneralFault" Then
                                strState = .ChildNodes(m).Attributes.getNamedItem("state").Text
                                Cells(h, 2).Value = strState
                                If PlayerNumber = 99 Then
                                    PlayerNumber = m + 1
                                Else
                                    'playernumber is already assigned (polymorphic)
                                End If
                            Else
                                'excel cannot handle usernames starting with an =
                                strTemp = Left(strPlName, 1)
                                If strTemp = "=" Then
                                    strPlName = " " & strPlName
                                Else
                                    'do nothing
                                End If
                                Cells(h, k).Value = strPlName
                                k = k + 1
                            End If
                        Next
                    End With

[/spoiler]

Happy coding!!

[GeneralFault]

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.

[spoiler=Final Macro]

Code: Select all

'-------------------- -------------------- --------------------
                    'childnode(19) is the collection of events
                    With xNode.ChildNodes(j).ChildNodes(19)
                        nrEvents = xNode.ChildNodes(j).ChildNodes(19).ChildNodes.Length
                        For m = 0 To nrEvents - 1
                            strEvent = xNode.ChildNodes(j).ChildNodes(19).ChildNodes(m).nodeTypedValue
                            strDate = .ChildNodes(m).Attributes.getNamedItem("timestamp").Text
                            realDate = DateAdd("s", strDate, "01/01/1970") ' 00:00:00
                            strTemp = Len(CStr(PlayerNumber))
                            If strTemp = 1 Then
                                'playernumber has one digit, select the correct eventstrings
                                If Left(strEvent, 1) = PlayerNumber Then
                                    If strState = "Won" Then
                                        strTemp = PlayerNumber & " gains "
                                        If strTemp = Left(strEvent, 8) Then
                                            'remove first 8 characters from string and last 7 characters
                                            strEvent = Mid(strEvent, 8)
                                            strEvent = Left(strEvent, Len(strEvent) - 7)
                                            Points = CInt(strEvent)
                                            Cells(h, 3).Value = Points
                                            Cells(h, 20).Value = realDate
                                        Else
                                            'do nothing
                                        End If
                                    Else
                                        'state is lost
                                        strTemp = PlayerNumber & " loses "
                                        If strTemp = Left(strEvent, 8) Then
                                            'remove first 8 characters from string and last 7 characters
                                            strEvent = Mid(strEvent, 8)
                                            strEvent = "-" & Left(strEvent, Len(strEvent) - 7)
                                            Points = CInt(strEvent)
                                            Cells(h, 3).Value = Points
                                            Cells(h, 20).Value = realDate
                                        Else
                                            'do nothing
                                        End If
                                    End If
                                Else
                                    'eventstring is not relevant for selected player
                                End If
                            Else
                                'playernumber has two digits, select the correct eventstrings
                                If Left(strEvent, 2) = PlayerNumber Then
                                    If strState = "Won" Then
                                        strTemp = PlayerNumber & " gains "
                                        If strTemp = Left(strEvent, 9) Then
                                            'remove first 9 characters from string and last 7 characters
                                            strEvent = Mid(strEvent, 9)
                                            strEvent = Left(strEvent, Len(strEvent) - 7)
                                            Points = CInt(strEvent)
                                            Cells(h, 3).Value = Points
                                            Cells(h, 20).Value = realDate
                                        Else
                                            'do nothing
                                        End If
                                    Else
                                        'state is lost
                                        strTemp = PlayerNumber & " loses "
                                        If strTemp = Left(strEvent, 9) Then
                                            'remove first 9 characters from string and last 7 characters
                                            strEvent = Mid(strEvent, 9)
                                            strEvent = "-" & Left(strEvent, Len(strEvent) - 7)
                                            Points = CInt(strEvent)
                                            Cells(h, 3).Value = Points
                                            Cells(h, 20).Value = realDate
                                        Else
                                            'do nothing
                                        End If
                                    End If
                                Else
                                    'eventstring is not relevant for selected player
                                End If
                            End If
                            'change cell with date to mm/dd/yyyy ATTENTION: DUTCH SETTINGS NOW
                            Cells(h, 20).NumberFormat = "dd/mm/yyyy"
                        Next

[/spoiler]

Happy coding!!

[GeneralFault]

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.

[spoiler=The Whole Final Macro]

Code: Select all

Sub PlayerData()
'routine to load all player games and their details
'written by GeneralFault

'loading all variables used in this subroutine
Dim Url_API, strPages, strPlName, strEvent, strTemp, strState, strDate As String
Dim xmlDoc As MSXML2.DOMDocument60
Dim xNode, yNode As MSXML2.IXMLDOMNode
Dim nrPages, nrGames, nrNodes, nrPlayers, nrEvents, Points As Integer
Dim h, i, j, k, m, PlayerNumber As Integer
Dim realDate As Date

'----------------------------------------Preparation----------------------------------------
'populate all relevant variables
Url_API = "http://www.conquerclub.com/api.php?mode=gamelist&un=generalfault&names=Y&gs=F&events=Y"

'preparing execution by retreiving first xml-batch
Set xmlDoc = New MSXML2.DOMDocument60
With xmlDoc
    'Load the xml from CC API
    .async = False
    .validateOnParse = False
    .Load (Url_API)

    'select first node to get the number of pages
    Set xNode = .FirstChild.SelectSingleNode("page")
    With xNode
        strPages = xNode.nodeTypedValue
        'trim string to number
        strPages = Mid(strPages, 5)
        nrPages = CInt(strPages)
    End With 'xNode pages
    
    'select second node to get the number of games played
    Set xNode = .FirstChild.SelectSingleNode("games")
    With xNode
        nrGames = xNode.Attributes.getNamedItem("total").NodeValue
    End With 'xNode games

    Worksheets("PlayerData").Select
    Cells(1, 1).Value = "Number of games in total: " & nrGames
    
End With 'xmlDoc

'----------------------------------------Execution----------------------------------------
'setting h as counter through all games
'starting at row 4
h = 3

For i = 1 To nrPages
    
    Url_API = "http://www.conquerclub.com/api.php?mode=gamelist&un=generalfault&names=Y&gs=F&events=Y" & Chr(38) & "page=" & i
    
    'load the correct api in the for-loop
    Set xmlDoc = New MSXML2.DOMDocument60
    
    With xmlDoc
        .async = False
        .validateOnParse = False
        .Load (Url_API)
    
        Set xNode = .FirstChild.SelectSingleNode("games")
        nrNodes = xNode.ChildNodes.Length
        
        'get all childnodes to populate the details
        With xNode.ChildNodes
            For j = 0 To nrNodes - 1
                h = h + 1
                With xNode.ChildNodes(j)
                    '-------------------- -------------------- --------------------
                    Cells(h, 1).Value = .SelectSingleNode("game_number").Text
                    '-------------------- -------------------- --------------------
                    'skip one to enter player state later
                    'skip another one to enter player score later
                    '-------------------- -------------------- --------------------
                    strTemp = .SelectSingleNode("game_state").Text
                    'gs - game state (W)aiting, (A)ctive or (F)inished
                    Select Case strTemp
                        Case "W"
                            strTemp = "Waiting"
                        Case "A"
                            strTemp = "Active"
                        Case "F"
                            strTemp = "Finished"
                        Case Else
                            strTemp = "Unknown"
                    End Select
                    Cells(h, 4).Value = strTemp
                    '-------------------- -------------------- --------------------
                    Cells(h, 5).Value = .SelectSingleNode("tournament").Text
                    '-------------------- -------------------- --------------------
                    strTemp = .SelectSingleNode("private").Text
                    'private - N(Public), Y(Private) , T(Tournament)
                    Select Case strTemp
                        Case "N"
                            strTemp = "Public"
                        Case "Y"
                            strTemp = "Private"
                        Case "T"
                            strTemp = "Tournament"
                        Case Else
                            strTemp = "Unknown"
                    End Select
                    Cells(h, 6).Value = strTemp
                    '-------------------- -------------------- --------------------
                    strTemp = .SelectSingleNode("speed_game").Text
                    'speed_game - N(Casual), 1(1min Speed), 2(1min Speed), 3(1min Speed), 4(1min Speed), 5(1min Speed)
                    'different than the API explanation
                    Select Case strTemp
                        Case "N"
                            strTemp = "Casual"
                        Case "Y"
                            strTemp = "Speed"
                        Case "S"
                            strTemp = "Speed"
                        Case "1"
                            strTemp = "1 min Speed"
                        Case "2"
                            strTemp = "2 min Speed"
                        Case "3"
                            strTemp = "3 min Speed"
                        Case "4"
                            strTemp = "4 min Speed"
                        Case "5"
                            strTemp = "5 min Speed"
                        Case Else
                            strTemp = "Unknown"
                    End Select
                    Cells(h, 7).Value = strTemp
                    '-------------------- -------------------- --------------------
                    Cells(h, 8).Value = .SelectSingleNode("map").Text
                    '-------------------- -------------------- --------------------
                    strTemp = .SelectSingleNode("game_type").Text
                    'game_type - S(Standard), C(Terminator), A(Assassin), D(Doubles), T(Triples), Q(Quadruples), P(Polymorphic)
                    Select Case strTemp
                        Case "S"
                            strTemp = "Standard"
                        Case "C"
                            strTemp = "Terminator"
                        Case "A"
                            strTemp = "Assassin"
                        Case "D"
                            strTemp = "Doubles"
                        Case "T"
                            strTemp = "Triples"
                        Case "Q"
                            strTemp = "Quadruples"
                        Case "P"
                            strTemp = "Polymorphic"
                        Case Else
                            strTemp = "Unknown"
                    End Select
                    Cells(h, 9).Value = strTemp
                    '-------------------- -------------------- --------------------
                    strTemp = .SelectSingleNode("initial_troops").Text
                    'initial_troops - E(Automatic), M(Manual)
                    Select Case strTemp
                        Case "E"
                            strTemp = "Automatic"
                        Case "M"
                            strTemp = "Manual"
                        Case Else
                            strTemp = "Unknown"
                    End Select
                    Cells(h, 10).Value = strTemp
                    '-------------------- -------------------- --------------------
                    strTemp = .SelectSingleNode("play_order").Text
                    'play_order - S(Sequential), F(Freestyle)
                    Select Case strTemp
                        Case "S"
                            strTemp = "Sequential"
                        Case "F"
                            strTemp = "Freestyle"
                        Case Else
                            strTemp = "Unknown"
                    End Select
                    Cells(h, 11).Value = strTemp
                    '-------------------- -------------------- --------------------
                    strTemp = .SelectSingleNode("bonus_cards").Text
                    'bonus_cards - 2 (Escalating) , 3 (Flat Rate) , 1 (No Spoils), 4(Nuclear), 5(Zombie)
                    Select Case strTemp
                        Case "1"
                            strTemp = "No Spoils"
                        Case "2"
                            strTemp = "Escalating"
                        Case "3"
                            strTemp = "Flat Rate"
                        Case "4"
                            strTemp = "Nuclear"
                        Case "5"
                            strTemp = "Zombie"
                        Case Else
                            strTemp = "Unknown"
                    End Select
                    Cells(h, 12).Value = strTemp
                    '-------------------- -------------------- --------------------
                    strTemp = .SelectSingleNode("fortifications").Text
                    'fortifications - C(Chained), O(Adjaecent), M(Unlimited), P(Parachute), N(None)
                    Select Case strTemp
                        Case "C"
                            strTemp = "Chained"
                        Case "O"
                            strTemp = "Adjacent"
                        Case "M"
                            strTemp = "Unlimited"
                        Case "P"
                            strTemp = "Parachute"
                        Case "N"
                            strTemp = "None"
                        Case Else
                            strTemp = "Unknown"
                    End Select
                    Cells(h, 13).Value = strTemp
                    '-------------------- -------------------- --------------------
                    strTemp = .SelectSingleNode("war_fog").Text
                    'war_fog -  N(No Fog) or Y(Fog)
                    Select Case strTemp
                        Case "N"
                            strTemp = "No Fog"
                        Case "Y"
                            strTemp = "Fog"
                        Case Else
                            strTemp = "Unknown"
                    End Select
                    Cells(h, 14).Value = strTemp
                    '-------------------- -------------------- --------------------
                    strTemp = .SelectSingleNode("trench_warfare").Text
                    'trench_warfare - (Y)es, (N)o
                    Select Case strTemp
                        Case "Y"
                            strTemp = "Trench"
                        Case "N"
                            strTemp = "No Trench"
                        Case Else
                            strTemp = "Unknown"
                    End Select
                    Cells(h, 15).Value = strTemp
                    '-------------------- -------------------- --------------------
                    strTemp = .SelectSingleNode("round_limit").Text
                    'round_limit - 0, 20, 30, 50, 100
                    Select Case strTemp
                        Case "0"
                            strTemp = "No limit"
                        Case "20"
                            strTemp = "20 rounds"
                        Case "30"
                            strTemp = "30 rounds"
                        Case "50"
                            strTemp = "50 rounds"
                        Case "100"
                            strTemp = "100 rounds"
                        Case Else
                            strTemp = "Unknown"
                    End Select
                    Cells(h, 16).Value = strTemp
                    '-------------------- -------------------- --------------------
                    Cells(h, 17).Value = .SelectSingleNode("round").Text
                    '-------------------- -------------------- --------------------
                    Cells(h, 18).Value = .SelectSingleNode("poly_slots").Text
                    '-------------------- -------------------- --------------------
                    strDate = .SelectSingleNode("time_remaining").Text
                    If strDate = 0 And Cells(h, 4).Value = "Finished" Then
                        Cells(h, 18).Value = Finished
                    Else
                        'calculate later
                    End If
                    '-------------------- -------------------- --------------------
                    'skip one for date
                    '-------------------- -------------------- --------------------
                    'childnode(18) is the collection of players
                    With xNode.ChildNodes(j).ChildNodes(18)
                        k = 22
                        nrPlayers = xNode.ChildNodes(j).ChildNodes(18).ChildNodes.Length
                        Cells(h, 21).Value = nrPlayers
                        PlayerNumber = 99
                        For m = 0 To nrPlayers - 1
                            strPlName = .ChildNodes(m).nodeTypedValue
                            If strPlName = "GeneralFault" Then
                                strState = .ChildNodes(m).Attributes.getNamedItem("state").Text
                                Cells(h, 2).Value = strState
                                If PlayerNumber = 99 Then
                                    PlayerNumber = m + 1
                                Else
                                    'playernumber is already assigned (polymorphic)
                                End If
                            Else
                                'excel cannot handle usernames starting with an =
                                strTemp = Left(strPlName, 1)
                                If strTemp = "=" Then
                                    strPlName = " " & strPlName
                                Else
                                    'do nothing
                                End If
                                Cells(h, k).Value = strPlName
                                k = k + 1
                            End If
                        Next
                    End With
                    '-------------------- -------------------- --------------------
                    'childnode(19) is the collection of events
                    With xNode.ChildNodes(j).ChildNodes(19)
                        nrEvents = xNode.ChildNodes(j).ChildNodes(19).ChildNodes.Length
                        For m = 0 To nrEvents - 1
                            strEvent = xNode.ChildNodes(j).ChildNodes(19).ChildNodes(m).nodeTypedValue
                            strDate = .ChildNodes(m).Attributes.getNamedItem("timestamp").Text
                            realDate = DateAdd("s", strDate, "01/01/1970") ' 00:00:00
                            strTemp = Len(CStr(PlayerNumber))
                            If strTemp = 1 Then
                                'playernumber has one digit, select the correct eventstrings
                                If Left(strEvent, 1) = PlayerNumber Then
                                    If strState = "Won" Then
                                        strTemp = PlayerNumber & " gains "
                                        If strTemp = Left(strEvent, 8) Then
                                            'remove first 8 characters from string and last 7 characters
                                            strEvent = Mid(strEvent, 8)
                                            strEvent = Left(strEvent, Len(strEvent) - 7)
                                            Points = CInt(strEvent)
                                            Cells(h, 3).Value = Points
                                            Cells(h, 20).Value = realDate
                                        Else
                                            'do nothing
                                        End If
                                    Else
                                        'state is lost
                                        strTemp = PlayerNumber & " loses "
                                        If strTemp = Left(strEvent, 8) Then
                                            'remove first 8 characters from string and last 7 characters
                                            strEvent = Mid(strEvent, 8)
                                            strEvent = "-" & Left(strEvent, Len(strEvent) - 7)
                                            Points = CInt(strEvent)
                                            Cells(h, 3).Value = Points
                                            Cells(h, 20).Value = realDate
                                        Else
                                            'do nothing
                                        End If
                                    End If
                                Else
                                    'eventstring is not relevant for selected player
                                End If
                            Else
                                'playernumber has two digits, select the correct eventstrings
                                If Left(strEvent, 2) = PlayerNumber Then
                                    If strState = "Won" Then
                                        strTemp = PlayerNumber & " gains "
                                        If strTemp = Left(strEvent, 9) Then
                                            'remove first 9 characters from string and last 7 characters
                                            strEvent = Mid(strEvent, 9)
                                            strEvent = Left(strEvent, Len(strEvent) - 7)
                                            Points = CInt(strEvent)
                                            Cells(h, 3).Value = Points
                                            Cells(h, 20).Value = realDate
                                        Else
                                            'do nothing
                                        End If
                                    Else
                                        'state is lost
                                        strTemp = PlayerNumber & " loses "
                                        If strTemp = Left(strEvent, 9) Then
                                            'remove first 9 characters from string and last 7 characters
                                            strEvent = Mid(strEvent, 9)
                                            strEvent = "-" & Left(strEvent, Len(strEvent) - 7)
                                            Points = CInt(strEvent)
                                            Cells(h, 3).Value = Points
                                            Cells(h, 20).Value = realDate
                                        Else
                                            'do nothing
                                        End If
                                    End If
                                Else
                                    'eventstring is not relevant for selected player
                                End If
                            End If
                            'change cell with date to mm/dd/yyyy ATTENTION: DUTCH SETTINGS NOW
                            Cells(h, 20).NumberFormat = "dd/mm/yyyy"
                        Next
                        '-------------------- -------------------- --------------------
                    End With
                End With 'xnode.childNodes
            Next
        End With 'xnode
    
    End With 'xmldoc

Next

MsgBox "done"

End Sub

[/spoiler]

Happy coding!!

[Robespierre__]

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.