Excel Spreadsheet

[BGtheBrain]

*****

[dgz345]

should be possible the way the same way maprank does.

im not home ATM. so i can not create it for you

http://www.conquerclub.com/api.php?mode ... =Y&names=Y

use the api. gn is comma separated

[dgz345]

is it a xls file you are wondering? or just as maprank?

ill check it when i get home in the weekend

[BGtheBrain]

*****

[dgz345]

idk how xls works with getting information from the internet. but ill look into it when i have time. tho ill only look into it because im intressted. so if i lose intrest there will not be a finished product.

[MrBenn]

idk how xls works with getting information from the internet. but ill look into it when i have time. tho ill only look into it because im intressted. so if i lose intrest there will not be a finished product.

I've written some vba code that gets data from api into an xls document... but that's at work right now... If nobody else gets around to it, I might look at it over the weekend.

[Swifte]

idk how xls works with getting information from the internet. but ill look into it when i have time. tho ill only look into it because im intressted. so if i lose intrest there will not be a finished product.

I've written some vba code that gets data from api into an xls document... but that's at work right now... If nobody else gets around to it, I might look at it over the weekend.

I'd be interested to see that as well, if you find it!

[BGtheBrain]

*****

[runewake2]

This will indeed be harder than you want as the points won/lost are not given by the API at this time. You'll need to use the API to get the players in the game and then download each games log and find the points rewarded sections to get this working. Your looking at some very scary macros. I've never written an Office App before, maybe I should...

[dgz345]

This will indeed be harder than you want as the points won/lost are not given by the API at this time. You'll need to use the API to get the players in the game and then download each games log and find the points rewarded sections to get this working. Your looking at some very scary macros. I've never written an Office App before, maybe I should...

the API shows the scorechange for player number if you add &events=Y

<events>
<event timestamp="1141717372">3 eliminated 1 from the game</event>
<event timestamp="1141718708">3 eliminated 2 from the game</event>
<event timestamp="1141718708">3 won the game</event>
<event timestamp="1141718708">1 loses 18 points</event>
<event timestamp="1141718708">2 loses 19 points</event>
<event timestamp="1141718708">3 gains 37 points</event>
</events>

[BGtheBrain]

*****

[dgz345]

are you using windows? ill maybe create an program for it. i dont know how it will end

[BGtheBrain]

*****

[Dukasaur]

Would it be possible to make a sheet formula where I could input a game # in column A, then column B would reflect Map Name, Column C would show player x points won/lost for each player?

I have about 150 games Im trying to compile the data for and this would be sweet.

I would look through the Dave's Tips archive:
http://www.conquerclub.com/forum/viewtopic.php?f=89&t=178308
He did pretty much all of his tournament scoring through macros in Excel.

Or, contact Dave directly. (But check his archive first. What you're looking for might already be covered.)

[BGtheBrain]

*****

[DaveH]

One solution based on copying the data from each game is as follows:

Say Game 14191088; the Points Total section has the summary of the points:

Points Totals
SuicidalSnowman scored -13 points in this game
BGtheBrain scored 49 points in this game
Steve The Mighty scored -17 points in this game
Vid_FISO scored -19 points in this game

(Note Igave you an extra 100 points to test that scores over 100 would be picked up!)
If the 4 lines are copied to a spreadsheet, then a couple of simple excel formulas can be used to extract the relevant information. i.e.

Game	Points Totals	Player	Points
14191088	SuicidalSnowman scored -13 points in this game	SuicidalSnowman	-13
	BGtheBrain scored 149 points in this game	BGtheBrain	149
	Steve The Mighty scored -17 points in this game	Steve The Mighty	-17
	Vid_FISO scored -19 points in this game	Vid_FISO	-19

Here I have written the game number as well, though this may not be necessary.

The formula in the player column is

Code: Select all

=LEFT(B2,FIND("scored",B2,1)-2)

and in the points column

Code: Select all

=MID(B2,FIND("scored",B2,1)+7,FIND("points",B2,1)-1-FIND("scored",B2,1)-7)

Then you could have a macro from a button that would scan down all the player names and summarise the points for each player. (If you want me to write a macro to do this I am happy to do it)

I'll post this in your thread as well and perhaps ythere may be a solution posted to extract the game info non-manually.

[DaveH]

If MrBenn could share his code for getting data directly from Internet to spreadsheet I would also appreciate it!

[MrBenn]

If you have an Excel workbook with a column (A) of Game Numbers (with a column heading), the following vba code looks up each game number and brings back each player, who won, points won/lost, no of kills, and the elimination order.

In order to get this to work, you'll first need to open the Visual Basic Editor (Macros menu or Alt+F11), and make sure you have enabled XML support (Tools > References > tick Microsoft XML 6.0 (or whatever your latest version is). Once that's done, copy and paste the following code into a module

Code: Select all

Sub get_cc_gamedata()

' Assumption that Game number is in column A
' Assumption the column has a header

Set SrchRange = Columns(1).EntireColumn

Set FindCell = SrchRange.Find(What:="*", after:=SrchRange.Cells(1), searchorder:=xlByRows, searchdirection:=xlPrevious)
If Not FindCell Is Nothing Then
    R = FindCell.Row
    If R < 2 Then Exit Sub
End If

Cells(1, 2).Value = "Players"
Cells(1, 3).Value = "Type"
Cells(1, 4).Value = "Map"
Cells(1, 5).Value = "Player Name"
Cells(1, 6).Value = "Player Status"
Cells(1, 7).Value = "Points Gained/Lost"
Cells(1, 8).Value = "Kills"
Cells(1, 9).Value = "Elim Order"

i = 2
Do
'For i = 2 To R Step 1
    GameNo = Cells(i, 1).Value
    If Not GameNo = Empty Then
        GameData = ccGameAPI(CStr(GameNo))
       
        Cells(i, 2).Value = UBound(GameData)
        Cells(i, 3).Value = GameData(0, 0)
        Cells(i, 4).Value = GameData(0, 1)
       
        For p = 1 To UBound(GameData)
            Cells(i, 5).Value = GameData(p, 0)
            Cells(i, 6).Value = GameData(p, 1)
            Cells(i, 7).Value = GameData(p, 2)
            Cells(i, 8).Value = CInt(GameData(p, 3))
            Cells(i, 9).Value = GameData(p, 4)
            If p < UBound(GameData) Then
                Rows(i + 1).EntireRow.Insert
                i = i + 1
                R = R + 1
            End If
        Next p
    End If
i = i + 1
'Next i
Loop While i <= R
Cells.EntireColumn.AutoFit
End Sub


Function ccGameAPI(GameNo As String)

'If this causes a "user defined type not defined" error then:
'Inside the Visual Basic Editor (can be accessed from the Macro menu:
' Go to Tools -> References, then Select Microsoft XML, v6.0 (or whatever your latest is).

Dim xmlDoc As MSXML2.DOMDocument
Dim xresult As MSXML2.IXMLDOMNode
Dim xentry As MSXML2.IXMLDOMNode
Dim xChild As MSXML2.IXMLDOMNode

ccAPIpath = "http://www.conquerclub.com/api.php?mode=gamelist&gn=" & GameNo _
            & "&names=Y&events=Y"

Set xmlDoc = New MSXML2.DOMDocument

With xmlDoc
    .async = False
    .validateOnParse = False
    .Load (ccAPIpath)
    Set GameData = .FirstChild.childNodes(1).FirstChild
End With

p = GameData.selectSingleNode("players").childNodes.Length
Dim GamePlayers()
ReDim GamePlayers(0 To p, 0 To 4)
' (p, 0) = Player Name
' (p, 1) = Plater State (Won/Lost)
' (p, 2) = Points Gained/Lost
' (p, 3) = Eliminations made
' (p, 4) = Kill Order
'UBound(GamePlayers) '-- Number of Players

'GamePlayers(0, 0) = GameData.childNodes(6).Text
'game type (S)tandard, (C)Terminator, (A)ssassin, (D)oubles, (T)riples or (Q)uadruples
GamePlayers(0, 0) = GameData.selectSingleNode("game_type").Text
GamePlayers(0, 1) = GameData.selectSingleNode("map").Text
 
For p = 1 To UBound(GamePlayers) Step 1
    With GameData.selectSingleNode("players").childNodes(p - 1)
        'GameData.childNodes(18).childNodes(e - 1)
        GamePlayers(p, 0) = .Text
        GamePlayers(p, 1) = .Attributes(0).nodeValue
    End With
Next p

ko = 1
For e = 1 To GameData.selectSingleNode("events").childNodes.Length
    With GameData.selectSingleNode("events").childNodes(e - 1)
        'GameData.childNodes(19).childNodes(e - 1)
       
        If Right(.Text, 7) = " points" Then
            l = InStr(.Text, " ")
            p = CInt(Left(.Text, l))
            GamePlayers(p, 2) = CInt(Replace(Replace(Replace( _
                                    Mid(.Text, l, Len(.Text)), _
                                    "loses", "-"), "gains", "+"), "points", ""))
        ElseIf Right(.Text, 14) = " from the game" Then
            l = InStr(.Text, " ")
            p = CInt(Left(.Text, l))
            GamePlayers(p, 3) = GamePlayers(p, 3) + 1
            t = .Text
            t = Mid(.Text, l, Len(.Text))
            GamePlayers(CInt(Replace(Replace( _
                        Mid(.Text, l, Len(.Text)), _
                        "eliminated", ""), "from the game", "")) _
                                                            , 4) = ko
            ko = ko + 1
           
        End If
    End With
Next e

ccGameAPI = GamePlayers

End Function



The Function 'ccGameAPI(GameNo As String)' pulls the data from the CC api, and picks out some of the specific data that I think you were looking for.

The subroutine 'get_cc_gamedata()' handles the spreadsheet side of things, and picking out game numbers and writing the results from the function.

I haven't tested it extensively, and could stick in some more formatting and error-handling options, but hopefully this will be useful.

[MrBenn]

Just tested it on a couple of BR's (lots of players, lots of points exchanged, etc), and had to make a small tweak... I'll update the code above.

MrB

[DaveH]

Wonderful! I have just found the Excel import functions, which I was not aware of before now, but your code saves a lot of additional work.

Opens a whole number of future possibilities!

I have added a short routine to summarise the results, so the revised macros are as follows:

Code: Select all

    Sub get_cc_gamedata(R)

    ' Assumption that Game number is in column A
    ' Assumption the column has a header

    Set SrchRange = Columns(1).EntireColumn

    Set FindCell = SrchRange.Find(What:="*", after:=SrchRange.Cells(1), searchorder:=xlByRows, searchdirection:=xlPrevious)
    If Not FindCell Is Nothing Then
        R = FindCell.Row
        If R < 2 Then Exit Sub
    End If

    Cells(1, 2).Value = "Players"
    Cells(1, 3).Value = "Type"
    Cells(1, 4).Value = "Map"
    Cells(1, 5).Value = "Player Name"
    Cells(1, 6).Value = "Player Status"
    Cells(1, 7).Value = "Points Gained/Lost"
    Cells(1, 8).Value = "Kills"
    Cells(1, 9).Value = "Elim Order"
   
    Cells(1, 11).Value = "Players"
    Cells(1, 12).Value = "Totals"

    i = 2
    Do
    'For i = 2 To R Step 1
        GameNo = Cells(i, 1).Value
        If Not GameNo = Empty Then
            GameData = ccGameAPI(CStr(GameNo))
           
            Cells(i, 2).Value = UBound(GameData)
            Cells(i, 3).Value = GameData(0, 0)
            Cells(i, 4).Value = GameData(0, 1)
           
            For p = 1 To UBound(GameData)
                Cells(i, 5).Value = GameData(p, 0)
                Cells(i, 6).Value = GameData(p, 1)
                Cells(i, 7).Value = GameData(p, 2)
                Cells(i, 8).Value = CInt(GameData(p, 3))
                Cells(i, 9).Value = GameData(p, 4)
                If p < UBound(GameData) Then
                    Rows(i + 1).EntireRow.Insert
                    i = i + 1
                    R = R + 1
                End If
            Next p
        End If
    i = i + 1
    'Next i
    Loop While i <= R
    Cells.EntireColumn.AutoFit
    End Sub


    Function ccGameAPI(GameNo As String)

    'If this causes a "user defined type not defined" error then:
    'Inside the Visual Basic Editor (can be accessed from the Macro menu:
    ' Go to Tools -> References, then Select Microsoft XML, v6.0 (or whatever your latest is).

    Dim xmlDoc As MSXML2.DOMDocument
    Dim xresult As MSXML2.IXMLDOMNode
    Dim xentry As MSXML2.IXMLDOMNode
    Dim xChild As MSXML2.IXMLDOMNode

    ccAPIpath = "http://www.conquerclub.com/api.php?mode=gamelist&gn=" & GameNo _
                & "&names=Y&events=Y"

    Set xmlDoc = New MSXML2.DOMDocument

    With xmlDoc
        .async = False
        .validateOnParse = False
        .Load (ccAPIpath)
        Set GameData = .FirstChild.ChildNodes(1).FirstChild
    End With

    p = GameData.SelectSingleNode("players").ChildNodes.Length
    Dim GamePlayers()
    ReDim GamePlayers(0 To p, 0 To 4)
    ' (p, 0) = Player Name
    ' (p, 1) = Plater State (Won/Lost)
    ' (p, 2) = Points Gained/Lost
    ' (p, 3) = Eliminations made
    ' (p, 4) = Kill Order
    'UBound(GamePlayers) '-- Number of Players

    'GamePlayers(0, 0) = GameData.childNodes(6).Text
    'game type (S)tandard, (C)Terminator, (A)ssassin, (D)oubles, (T)riples or (Q)uadruples
    GamePlayers(0, 0) = GameData.SelectSingleNode("game_type").Text
    GamePlayers(0, 1) = GameData.SelectSingleNode("map").Text
     
    For p = 1 To UBound(GamePlayers) Step 1
        With GameData.SelectSingleNode("players").ChildNodes(p - 1)
            'GameData.childNodes(18).childNodes(e - 1)
            GamePlayers(p, 0) = .Text
            GamePlayers(p, 1) = .Attributes(0).NodeValue
        End With
    Next p

    ko = 1
    For e = 1 To GameData.SelectSingleNode("events").ChildNodes.Length
        With GameData.SelectSingleNode("events").ChildNodes(e - 1)
            'GameData.childNodes(19).childNodes(e - 1)
           
            If Right(.Text, 7) = " points" Then
                l = InStr(.Text, " ")
                p = CInt(Left(.Text, l))
                GamePlayers(p, 2) = CInt(Replace(Replace(Replace( _
                                        Mid(.Text, l, Len(.Text)), _
                                        "loses", "-"), "gains", "+"), "points", ""))
            ElseIf Right(.Text, 14) = " from the game" Then
                l = InStr(.Text, " ")
                p = CInt(Left(.Text, l))
                GamePlayers(p, 3) = GamePlayers(p, 3) + 1
                t = .Text
                t = Mid(.Text, l, Len(.Text))
                GamePlayers(CInt(Replace(Replace( _
                            Mid(.Text, l, Len(.Text)), _
                            "eliminated", ""), "from the game", "")) _
                                                                , 4) = ko
                ko = ko + 1
               
            End If
        End With
    Next e

    ccGameAPI = GamePlayers

    End Function

Sub SumScores()
'
' Game Nos titles in cell(1A)
' List of game numbers in column A from cell(2A)
'
Call get_cc_gamedata(R)

Range(Cells(2, 5), Cells(R, 5)).Select

Selection.Copy
Cells(2, 11).Select
ActiveSheet.Paste

Range(Cells(2, 7), Cells(R, 7)).Select
Selection.Copy
Cells(2, 12).Select
ActiveSheet.Paste

Range(Cells(2, 11), Cells(R, 12)).Select
    Selection.Sort Key1:=Range(Cells(2, 11), Cells(R, 12)), Order1:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

j = 1
While j = 1
    j = 0
    For i = 2 To R - 1
        A = Cells(i, 11).Value
        B = Cells(i + 1, 11).Value
        If A = B And A <> "" Then
         Cells(i, 12).Value = Cells(i, 12).Value + Cells(i + 1, 12).Value
            Cells(i + 1, 11).Value = ""
            Cells(i + 1, 12).Value = ""
            j = 1
        End If

    Next i

    Range(Cells(2, 11), Cells(R, 12)).Select
    Selection.Sort Key1:=Range(Cells(2, 11), Cells(R, 12)), Order1:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

Wend
     
    Range(Cells(2, 11), Cells(R, 12)).Select
    Selection.Sort Key1:=Range(Cells(2, 12), Cells(R, 12)), Order1:=xlDescending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
Stop
End Sub


The added routine SumScores takes the player and points columns and puts them into columns 11 and 12 and then adds and sorts.

To include the macros into your spreadsheet, the easiest way is to go to Developer tab and click Record Macro. Enter to start to run the macro and then immediately stop the macro.
Now go to Macros and select Edit for the macro just recorded. Copy all and paste in the above code for the three routines.

When you have the list of game numbers in column A go to Developer/Macros and Run "SumScores",

[BGtheBrain]

*****

[DaveH]

I have made a couple of minor tweeks to get the terminator points to accumulate and included the final round number, which is needed in some types of tournaments. The code for the three parts is now:

Code: Select all

Sub get_cc_gamedata(R)

    ' Assumption that Game number is in column A
    ' Assumption the column has a header

    Set SrchRange = Columns(1).EntireColumn

    Set FindCell = SrchRange.Find(What:="*", after:=SrchRange.Cells(1), searchorder:=xlByRows, searchdirection:=xlPrevious)
    If Not FindCell Is Nothing Then
        R = FindCell.Row
        If R < 2 Then Exit Sub
    End If

    Cells(1, 1).Value = "Game Nos"
    Cells(1, 2).Value = "Players"
    Cells(1, 3).Value = "Type"
    Cells(1, 4).Value = "Map"
    Cells(1, 5).Value = "Player Name"
    Cells(1, 6).Value = "Player Status"
    Cells(1, 7).Value = "Points Gained/Lost"
    Cells(1, 8).Value = "Kills"
    Cells(1, 9).Value = "Elim Order"
    Cells(1, 10).Value = "Round"
    Cells(1, 12).Value = "Players"
    Cells(1, 13).Value = "Totals"

    i = 2
    Do
    'For i = 2 To R Step 1
        GameNo = Cells(i, 1).Value
        If Not GameNo = Empty Then
            GameData = ccGameAPI(CStr(GameNo))
           
            Cells(i, 2).Value = UBound(GameData)
            Cells(i, 3).Value = GameData(0, 0)
            Cells(i, 4).Value = GameData(0, 1)
           
            For p = 1 To UBound(GameData)
                Cells(i, 5).Value = GameData(p, 0)
                Cells(i, 6).Value = GameData(p, 1)
                Cells(i, 7).Value = GameData(p, 2)
                Cells(i, 8).Value = CInt(GameData(p, 3))
                Cells(i, 9).Value = GameData(p, 4)
                Cells(i, 10).Value = GameData(0, 5)
                If p < UBound(GameData) Then
                    Rows(i + 1).EntireRow.Insert
                    i = i + 1
                    R = R + 1
                End If
            Next p
        End If
    i = i + 1
    'Next i
    Loop While i <= R
    Cells.EntireColumn.AutoFit
    End Sub


    Function ccGameAPI(GameNo As String)

    'If this causes a "user defined type not defined" error then:
    'Inside the Visual Basic Editor (can be accessed from the Macro menu:
    ' Go to Tools -> References, then Select Microsoft XML, v6.0 (or whatever your latest is).

    Dim xmlDoc As MSXML2.DOMDocument
    Dim xresult As MSXML2.IXMLDOMNode
    Dim xentry As MSXML2.IXMLDOMNode
    Dim xChild As MSXML2.IXMLDOMNode

    ccAPIpath = "http://www.conquerclub.com/api.php?mode=gamelist&gn=" & GameNo _
                & "&names=Y&events=Y"

    Set xmlDoc = New MSXML2.DOMDocument

    With xmlDoc
        .async = False
        .validateOnParse = False
        .Load (ccAPIpath)
        Set GameData = .FirstChild.ChildNodes(1).FirstChild
    End With

    p = GameData.SelectSingleNode("players").ChildNodes.Length
    Dim GamePlayers()
    ReDim GamePlayers(0 To p, 0 To 5)
    ' (p, 0) = Player Name
    ' (p, 1) = Player State (Won/Lost)
    ' (p, 2) = Points Gained/Lost
    ' (p, 3) = Eliminations made
    ' (p, 4) = Kill Order
    ' (p, 5) = Round
    'UBound(GamePlayers) '-- Number of Players

    'GamePlayers(0, 0) = GameData.childNodes(6).Text
    'game type (S)tandard, (C)Terminator, (A)ssassin, (D)oubles, (T)riples or (Q)uadruples
    GamePlayers(0, 0) = GameData.SelectSingleNode("game_type").Text
    GamePlayers(0, 1) = GameData.SelectSingleNode("map").Text
    GamePlayers(0, 5) = GameData.SelectSingleNode("round").Text

    For p = 1 To UBound(GamePlayers) Step 1
   
    GamePlayers(p, 2) = 0
   
        With GameData.SelectSingleNode("players").ChildNodes(p - 1)
            'GameData.childNodes(18).childNodes(e - 1)
            GamePlayers(p, 0) = .Text
            GamePlayers(p, 1) = .Attributes(0).NodeValue
           
        End With
    Next p

    ko = 1
    For e = 1 To GameData.SelectSingleNode("events").ChildNodes.Length
        With GameData.SelectSingleNode("events").ChildNodes(e - 1)
            'GameData.childNodes(19).childNodes(e - 1)
         
            If Right(.Text, 7) = " points" Then
                l = InStr(.Text, " ")
                p = CInt(Left(.Text, l))
                GamePlayers(p, 2) = GamePlayers(p, 2) + CInt(Replace(Replace(Replace( _
                                        Mid(.Text, l, Len(.Text)), _
                                        "loses", "-"), "gains", "+"), "points", ""))
           
            ElseIf Right(.Text, 14) = " from the game" Then
                l = InStr(.Text, " ")
                p = CInt(Left(.Text, l))
                GamePlayers(p, 3) = GamePlayers(p, 3) + 1
                t = .Text
                t = Mid(.Text, l, Len(.Text))
                GamePlayers(CInt(Replace(Replace( _
                            Mid(.Text, l, Len(.Text)), _
                            "eliminated", ""), "from the game", "")) _
                                                                , 4) = ko
                ko = ko + 1

            End If
        End With
    Next e
   
    ccGameAPI = GamePlayers

    End Function

Sub SumScores()
'
' Game Nos titles in cell(1A)
' List of game numbers in column A from cell(2A)
'
Call get_cc_gamedata(R)

Range(Cells(2, 5), Cells(R, 5)).Select

Selection.Copy
Cells(2, 12).Select
ActiveSheet.Paste

Range(Cells(2, 7), Cells(R, 7)).Select
Selection.Copy
Cells(2, 13).Select
ActiveSheet.Paste

Range(Cells(2, 12), Cells(R, 13)).Select
    Selection.Sort Key1:=Range(Cells(2, 12), Cells(R, 13)), Order1:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

j = 1
While j = 1
    j = 0
    For i = 2 To R - 1
        A = Cells(i, 12).Value
        B = Cells(i + 1, 12).Value
        If A = B And A <> "" Then
         Cells(i, 13).Value = Cells(i, 13).Value + Cells(i + 1, 13).Value
            Cells(i + 1, 12).Value = ""
            Cells(i + 1, 13).Value = ""
            j = 1
        End If

    Next i

    Range(Cells(2, 12), Cells(R, 13)).Select
    Selection.Sort Key1:=Range(Cells(2, 12), Cells(R, 13)), Order1:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

Wend
   
    Range(Cells(2, 12), Cells(R, 13)).Select
    Selection.Sort Key1:=Range(Cells(2, 13), Cells(R, 13)), Order1:=xlDescending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

End Sub

This gives the following outputs for the different types of games:

show: output

[MrBenn]

Good spot on the cumulative terminator points!

I've had a couple of other thoughts as to how this could be improved for tournaments - it could be possible to populate a list of game numbers from the tournament name... With some variables to control scoring mechanisms, it should be possible to fully automate league tables etc.

I'll look at it again tonight. It would be good to collate some different/typical scoring methods to incorporate into something comprehensive.

[DaveH]

Finding on Tournament names would be great - perhaps the "game label" as well, though bringing that information to the spreadsheet would also work.

I had wondered if the round number that players were eliminated in might lead to additional scoring methods for some TO's. Also players' starting points - otherwise I can't think that there is any other information that would be needed. I can see some complex scoring methods coming up in my future tournaments!

I am so impressed with the elegance of it - and sorry that my additions are not in the same league!

I have added some formatting to put lines under each separate game to make it easier to see - the lines don't show up on my table routine, so I can't print the output appearance here

Code: Select all

 Sub get_cc_gamedata(R)

    ' Assumption that Game number is in column A
    ' Assumption the column has a header

    Set SrchRange = Columns(1).EntireColumn

    Set FindCell = SrchRange.Find(What:="*", after:=SrchRange.Cells(1), searchorder:=xlByRows, searchdirection:=xlPrevious)
    If Not FindCell Is Nothing Then
        R = FindCell.Row
        If R < 2 Then Exit Sub
    End If

    Cells(1, 1).Value = "Game Nos"
    Cells(1, 2).Value = "Players"
    Cells(1, 3).Value = "Type"
    Cells(1, 4).Value = "Map"
    Cells(1, 5).Value = "Player Name"
    Cells(1, 6).Value = "Player Status"
    Cells(1, 7).Value = "Points Gained/Lost"
    Cells(1, 8).Value = "Kills"
    Cells(1, 9).Value = "Elim Order"
    Cells(1, 10).Value = "Round"
    Cells(1, 12).Value = "Players"
    Cells(1, 13).Value = "Totals"
   
    i = 2
    Do
    'For i = 2 To R Step 1
       
        GameNo = Cells(i, 1).Value
        If Not GameNo = Empty Then
            GameData = ccGameAPI(CStr(GameNo))
           
            Cells(i, 2).Value = UBound(GameData)
            Cells(i, 3).Value = GameData(0, 0)
            Cells(i, 4).Value = GameData(0, 1)
         
            Range(Cells(i, 1), Cells(i, 10)).Select
            With Selection.Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlThin
            End With
   
            For p = 1 To UBound(GameData)
                Cells(i, 5).Value = GameData(p, 0)
                Cells(i, 6).Value = GameData(p, 1)
                Cells(i, 7).Value = GameData(p, 2)
                Cells(i, 8).Value = CInt(GameData(p, 3))
                Cells(i, 9).Value = GameData(p, 4)
                Cells(i, 10).Value = GameData(0, 5)
                If p < UBound(GameData) Then
                    Rows(i + 1).EntireRow.Insert
                    i = i + 1
                    R = R + 1
                End If
            Next p
        End If
    i = i + 1
    'Next i
    Loop While i <= R
    Cells.EntireColumn.AutoFit
    End Sub
    Function ccGameAPI(GameNo As String)

    'If this causes a "user defined type not defined" error then:
    'Inside the Visual Basic Editor (can be accessed from the Macro menu:
    ' Go to Tools -> References, then Select Microsoft XML, v6.0 (or whatever your latest is).

    Dim xmlDoc As MSXML2.DOMDocument
    Dim xresult As MSXML2.IXMLDOMNode
    Dim xentry As MSXML2.IXMLDOMNode
    Dim xChild As MSXML2.IXMLDOMNode

    ccAPIpath = "http://www.conquerclub.com/api.php?mode=gamelist&gn=" & GameNo _
                & "&names=Y&events=Y"

    Set xmlDoc = New MSXML2.DOMDocument

    With xmlDoc
        .async = False
        .validateOnParse = False
        .Load (ccAPIpath)
        Set GameData = .FirstChild.ChildNodes(1).FirstChild
    End With

    p = GameData.SelectSingleNode("players").ChildNodes.Length
    Dim GamePlayers()
    ReDim GamePlayers(0 To p, 0 To 5)
    ' (p, 0) = Player Name
    ' (p, 1) = Player State (Won/Lost)
    ' (p, 2) = Points Gained/Lost
    ' (p, 3) = Eliminations made
    ' (p, 4) = Kill Order
    ' (p, 5) = Round
    'UBound(GamePlayers) '-- Number of Players

    'GamePlayers(0, 0) = GameData.childNodes(6).Text
    'game type (S)tandard, (C)Terminator, (A)ssassin, (D)oubles, (T)riples or (Q)uadruples
    GamePlayers(0, 0) = GameData.SelectSingleNode("game_type").Text
    GamePlayers(0, 1) = GameData.SelectSingleNode("map").Text
    GamePlayers(0, 5) = GameData.SelectSingleNode("round").Text

    For p = 1 To UBound(GamePlayers) Step 1
   
    GamePlayers(p, 2) = 0
   
        With GameData.SelectSingleNode("players").ChildNodes(p - 1)
            'GameData.childNodes(18).childNodes(e - 1)
            GamePlayers(p, 0) = .Text
            GamePlayers(p, 1) = .Attributes(0).NodeValue
           
        End With
    Next p

    ko = 1
    For e = 1 To GameData.SelectSingleNode("events").ChildNodes.Length
        With GameData.SelectSingleNode("events").ChildNodes(e - 1)
            'GameData.childNodes(19).childNodes(e - 1)
         
            If Right(.Text, 7) = " points" Then
                l = InStr(.Text, " ")
                p = CInt(Left(.Text, l))
                GamePlayers(p, 2) = GamePlayers(p, 2) + CInt(Replace(Replace(Replace( _
                                        Mid(.Text, l, Len(.Text)), _
                                        "loses", "-"), "gains", "+"), "points", ""))
           
            ElseIf Right(.Text, 14) = " from the game" Then
                l = InStr(.Text, " ")
                p = CInt(Left(.Text, l))
                GamePlayers(p, 3) = GamePlayers(p, 3) + 1
                t = .Text
                t = Mid(.Text, l, Len(.Text))
                GamePlayers(CInt(Replace(Replace( _
                            Mid(.Text, l, Len(.Text)), _
                            "eliminated", ""), "from the game", "")) _
                                                                , 4) = ko
                ko = ko + 1

            End If
        End With
    Next e
   
    ccGameAPI = GamePlayers

    End Function

Sub SumScores()
'
' Game Nos titles in cell(1A)
' List of game numbers in column A from cell(2A)
'
Call get_cc_gamedata(R)

Range(Cells(2, 5), Cells(R, 5)).Select

Selection.Copy
Cells(2, 12).Select
ActiveSheet.Paste

Range(Cells(2, 7), Cells(R, 7)).Select
Selection.Copy
Cells(2, 13).Select
ActiveSheet.Paste

Range(Cells(2, 12), Cells(R, 13)).Select
    Selection.Sort Key1:=Range(Cells(2, 12), Cells(R, 13)), Order1:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

j = 1
While j = 1
    j = 0
    For i = 2 To R - 1
        A = Cells(i, 12).Value
        B = Cells(i + 1, 12).Value
        If A = B And A <> "" Then
         Cells(i, 13).Value = Cells(i, 13).Value + Cells(i + 1, 13).Value
            Cells(i + 1, 12).Value = ""
            Cells(i + 1, 13).Value = ""
            j = 1
        End If

    Next i

    Range(Cells(2, 12), Cells(R, 13)).Select
    Selection.Sort Key1:=Range(Cells(2, 12), Cells(R, 13)), Order1:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

Wend
   
    Range(Cells(2, 12), Cells(R, 13)).Select
        Selection.Borders(xlEdgeTop).LineStyle = xlNone
        Selection.Borders(xlEdgeBottom).LineStyle = xlNone
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Selection.Sort Key1:=Range(Cells(2, 13), Cells(R, 13)), Order1:=xlDescending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

       
End Sub

[MrBenn]

I've got some other bits of code at work that would be useful for getting a unique list of names, and you could use some other formulae to sum your results etc. It would be worth changing some of the variable names to help with consistency across the different procedures. I don't have time tonight but might be able to look at it during the week.