Excel Spreadsheet

[DaveH]

Great. What we have at present is a radical improvement on the code I was using so there's no rush in finalising the routine.

Getting consistency and perhaps more labelling would be useful when making it available for others.

[BGtheBrain]

*****

[MrBenn]

I've written something a bit more comprehensive that will take a tournament name as an input and then retrieve data for every game in it, with minimal API calls. I'm currently trying to work out how to calculate the round in which a player has been eliminated, and how to programatically implement some elegant formulae that give various totals etc.

Add this code as a new subprocedure (there's a useful function in here as well that will return a unique list of items from any given range of cells)

Code: Select all

Sub Get_cc_tournament()

Dim tourney As String
Dim APIpath As String
Dim xmlDoc As MSXML2.DOMDocument
Dim xNode As MSXML2.IXMLDOMNode

Dim gData()
Dim pData()

Dim pRange As Range
Dim pStats As Range

Dim headers(1 To 2)
headers(1) = Array("Game No", "Tournament Label", "Map", "Players", "Game Type", "Round Limit", "Status")
headers(2) = Array("Player Name", "Elim Order", "Kills", "Points", "Result", "Round")

'Cells(nextRow, UBound(gData) + 1).Resize(UBound(pData, 1) - LBound(pData, 1) + 1, UBound(pData, 2) - LBound(pData, 2) + 1).Value = pData
Col = UBound(headers(1)) - LBound(headers(1)) + 1
Cells(1, 1).Resize(1, Col).Value = headers(1)
Cells(1, Col + 1).Resize(1, UBound(headers(2)) - LBound(headers(2)) + 1).Value = headers(2)
Col = Col + UBound(headers(2)) - LBound(headers(2)) + 1

CurPage = 1
nextRow = 2
    'Get tournament name from inputbox
    Deftourney = ActiveSheet.Name
    'Deftourney = "7 Man Madness!"
    tourney = InputBox("Please enter a Conquer Club Tournament Name:", , Deftourney)
    If tourney = "" Then Exit Sub
    
Do
    APIpath = "http://www.conquerclub.com/api.php?mode=gamelist&to=" & tourney _
                & "&names=Y&events=Y"
    If CurPage > 1 Then APIpath = APIpath & "&page=" & CurPage
    
    Set xmlDoc = New MSXML2.DOMDocument
    
    With xmlDoc
        'Load the xml from CC API
        .async = False
        .validateOnParse = False
        .Load (APIpath)
          
        'Get number of pages
        Set xNode = .FirstChild.selectSingleNode("page")
        With xNode
            l = InStr(.Text, " ")
            CurPage = CInt(Left(.Text, l))
            l = InStrRev(.Text, " ")
            MaxPage = CInt(Mid(.Text, l))
        End With
        
        'Get number of games in xml
        Set xNode = .FirstChild.selectSingleNode("games")
        MaxGames = xNode.Attributes.getNamedItem("total").Text
        FileGames = xNode.childNodes.Length
        
        For g = 0 To (FileGames - 1) Step 1
            
            With xNode.childNodes(g)
   
                ReDim gData(1 To 7)
                gData(1) = .selectSingleNode("game_number").Text
                gData(1) = "=HYPERLINK(""http://www.conquerclub.com/game.php?game=" & _
                                    gData(1) & """,""" & gData(1) & """)"
                gData(2) = .selectSingleNode("tournament").Text
                gData(3) = .selectSingleNode("map").Text
                gData(4) = .selectSingleNode("players").childNodes.Length
                gData(5) = .selectSingleNode("game_type").Text
                gData(6) = .selectSingleNode("round_limit").Text
                gData(7) = .selectSingleNode("game_state").Text
                
                
                Select Case gData(5) 'Game Type
                    Case "S"
                        gData(5) = "Standard"
                    Case "C"
                        gData(5) = "Terminator"
                    Case "A"
                        gData(5) = "Assassin"
                    Case "P"
                        gData(5) = "Polymorphic"
                    Case "D"
                        gData(5) = "Doubles"
                    Case "T"
                        gData(5) = "Triples"
                    Case "Q"
                        gData(5) = "Quads"
                    Case Else
                        'Unknown Game Type
                End Select
                
                Select Case gData(7) 'game state (W)aiting, (A)ctive or (F)inished
                    Case "W"
                        gData(7) = "Waiting"
                    Case "A"
                        gData(7) = "Active"
                    Case "F"
                        gData(7) = "Finished"
                    Case Else
                        'Unknown Game State
                End Select
                
                
                'Range("A1:A" & UBound(GData)) = WorksheetFunction.Transpose(GData)
                Range(Cells(nextRow, 1), Cells(nextRow, UBound(gData))) = gData
                'nextRow = nextRow + 1
                    
                ReDim pData(1 To gData(4), 0 To 5)
                '(playerNo, (0=name, 1=Elim Order, 2=Kills, 3=Points, 4=Result, 5=Round) )
        
                For p = 1 To gData(4)
                    With .selectSingleNode("players").childNodes(p - 1)
                        pData(p, 0) = .Text
                        pData(p, 4) = .Attributes.getNamedItem("state").nodeValue
                    End With 'Players
                    pData(p, 5) = .selectSingleNode("round").Text
                Next p
                
                ko = 1
                For e = 1 To .selectSingleNode("events").childNodes.Length
                    With .selectSingleNode("events").childNodes(e - 1)
                        'GameXML.childNodes(19).childNodes(e - 1)
                        
                        If Right(.Text, 7) = " points" Then
                            l = InStr(.Text, " ")
                            p = CInt(Left(.Text, l))
                            pData(p, 3) = pData(p, 3) + _
                                             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))
                            If Not p = 0 Then 'not eliminated by neutral
                                pData(p, 2) = pData(p, 2) + 1
                            End If
                            t = .Text
                            t = Mid(.Text, l, Len(.Text))
                            pData(CInt(Replace(Replace( _
                                        Mid(.Text, l, Len(.Text)), _
                                        "eliminated", ""), "from the game", "")) _
                                        , 1) = ko     ' pData(p,4) = Kill Order
                            ko = ko + 1
                            
                        End If
                    End With
                Next e
                            
                Cells(nextRow, UBound(gData) + 1).Resize(UBound(pData, 1) - LBound(pData, 1) + 1, UBound(pData, 2) - LBound(pData, 2) + 1).Value = pData
                nextRow = nextRow + gData(4)
                
                With Rows(nextRow).EntireRow.Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .Weight = xlMedium
                    .ColorIndex = xlAutomatic
                End With
                
            End With 'Game(g) 'xNode.childNodes(g)
               
        Next g
        
    End With 'xmlDoc

CurPage = CurPage + 1
Loop While CurPage <= MaxPage

'#### All Game Data has now been imported (will error if too many rows for spreadsheet)
Cells(1, Col + 1).Value = CStr(MaxGames) & " Games"

Set pRange = Range(Cells(2, 8), Cells(nextRow - 1, 8))
uPlayers = UniqueList(pRange)

StatCol = 16   'Col P
Set pStats = Range("P2:P" & CStr(UBound(uPlayers) + 2))

Cells(1, StatCol).Value = "Player Name"
pStats = WorksheetFunction.Transpose(uPlayers)

Set pStats = pRange.Offset(0, 1)

' NEED TO ADD IN SOME STATS FORMULAE
' ALSO NEED TO CALCULATE WHICH ROUND AN ELIMINATION TOOK PLACE


End Sub




Function UniqueList(Optional ListRange As Range)

    If ListRange Is Nothing Then Set ListRange = Selection

    Dim List As Variant
    List = ListRange.Value
    
    With CreateObject("scripting.dictionary")
        For Each element In List
            If Not element = Empty Then c00 = .Item(element)
        Next
        UniqueList = .keys
    End With
        
End Function

[DaveH]

Better and better! Well done mate!

[elonpuckhog]

On a different but somewhat related subject:

Lets say I run a search in Game Finder for all games completed on Promo Point (there are currently about 1700 results).

Is there a way I can export all of these to Excel so I can work with them from there?

[dgz345]

not from using the game finder. but if you use the api you could work it somehow

[elonpuckhog]

Thanks for the info dgz. I did try that, but I couldn't seem to get it to work. I was able to return the 9 pages of output, but it was way too much info (I only need game numbers) and when I plugged it into Excel, it all went into one cell.

No big deal, really. I was just wondering if it was possible, and since it seems like it might be some work I'm not really interested. Just wanted to thank you for the response.

[DaveH]

You can copy and paste of course - a lot of data but you can then extract the data you need from each game.

[MrBenn]

Thanks for the info dgz. I did try that, but I couldn't seem to get it to work. I was able to return the 9 pages of output, but it was way too much info (I only need game numbers) and when I plugged it into Excel, it all went into one cell.

No big deal, really. I was just wondering if it was possible, and since it seems like it might be some work I'm not really interested. Just wanted to thank you for the response.

This can be done with an api call - I'll post some detail when I'm in front of a computer.

[MrBenn]

Thanks for the info dgz. I did try that, but I couldn't seem to get it to work. I was able to return the 9 pages of output, but it was way too much info (I only need game numbers) and when I plugged it into Excel, it all went into one cell.

What are you planning to do with your list of game numbers?

I'm currently trying to work out how to calculate the round in which a player has been eliminated

It looks like this cannot currentytl be done via the API. I've got some array formulae that work nicely - it would be helpful to have some examples of scoring variations so I can test some out...

[elonpuckhog]

Ideally, I'd like to be able to pull up a list (say, all completed games of Promo Summit). Then, once I have those game numbers, I would use random.org to select me a random set of numbers, so I could go into those random games and get data. I'm trying to use stat sampling to prove/disprove a theory I have. I could do it through game finder, but it would be way more tedious that way, and I just don't care enough.

[MrBenn]

Ideally, I'd like to be able to pull up a list (say, all completed games of Promo Summit). Then, once I have those game numbers, I would use random.org to select me a random set of numbers, so I could go into those random games and get data. I'm trying to use stat sampling to prove/disprove a theory I have. I could do it through game finder, but it would be way more tedious that way, and I just don't care enough.

What stats did you want to sample?

[elonpuckhog]

I was gonna take a sample of game numbers, and then go into each game and determine how the game was won (CP objective, UP objective, round limit or deadbeat).

[MrBenn]

I was gonna take a sample of game numbers, and then go into each game and determine how the game was won (CP objective, UP objective, round imit or deadbeat).

hmmm... I wonder if that can be determined from api game events? I'll look into that.

In the meantime I've had to make a suggestion for an api change which would enable rounds to be included, so scoring could be based on how long people survived: http://www.conquerclub.com/forum/viewtopic.php?f=4&t=205162