Conquer Club

Excel Spreadsheet

Have suggestion for a new or old tool/enhancement? Come on in!

Moderator: Tech Team

Forum rules
Please read the Community Guidelines before posting.

Re: Excel Spreadsheet

Postby DaveH on Mon Apr 28, 2014 2:51 am

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.
Image
User avatar
Lieutenant DaveH
 
Posts: 1561
Joined: Sun Dec 06, 2009 9:12 am
Location: Torquay, Devon

Re: Excel Spreadsheet

Postby BGtheBrain on Mon Apr 28, 2014 8:10 am

*****
Last edited by BGtheBrain on Thu Jun 29, 2017 3:32 pm, edited 1 time in total.
User avatar
Captain BGtheBrain
 
Posts: 2770
Joined: Thu Sep 02, 2010 2:50 pm

Re: Excel Spreadsheet

Postby MrBenn on Wed Apr 30, 2014 7:12 pm

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



Image
PB: 2661 | He's blue... If he were green he would die | No mod would be stupid enough to do that
User avatar
Lieutenant MrBenn
 
Posts: 6880
Joined: Wed Nov 21, 2007 9:32 am
Location: Off Duty

Re: Excel Spreadsheet

Postby DaveH on Mon May 05, 2014 12:30 am

Better and better! Well done mate!
Image
User avatar
Lieutenant DaveH
 
Posts: 1561
Joined: Sun Dec 06, 2009 9:12 am
Location: Torquay, Devon

Re: Excel Spreadsheet

Postby elonpuckhog on Wed May 14, 2014 11:20 am

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?
Corporal 1st Class elonpuckhog
 
Posts: 1511
Joined: Wed Jul 11, 2012 9:01 pm

Re: Excel Spreadsheet

Postby dgz345 on Wed May 14, 2014 12:06 pm

not from using the game finder. but if you use the api you could work it somehow
User avatar
Lieutenant dgz345
Head Tech
Head Tech
 
Posts: 1379
Joined: Thu Oct 07, 2010 10:53 am

Re: Excel Spreadsheet

Postby elonpuckhog on Wed May 14, 2014 3:57 pm

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.
Corporal 1st Class elonpuckhog
 
Posts: 1511
Joined: Wed Jul 11, 2012 9:01 pm

Re: Excel Spreadsheet

Postby DaveH on Thu May 15, 2014 12:54 am

You can copy and paste of course - a lot of data but you can then extract the data you need from each game.
Image
User avatar
Lieutenant DaveH
 
Posts: 1561
Joined: Sun Dec 06, 2009 9:12 am
Location: Torquay, Devon

Re: Excel Spreadsheet

Postby MrBenn on Thu May 15, 2014 1:48 pm

elonpuckhog wrote: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.
Image
PB: 2661 | He's blue... If he were green he would die | No mod would be stupid enough to do that
User avatar
Lieutenant MrBenn
 
Posts: 6880
Joined: Wed Nov 21, 2007 9:32 am
Location: Off Duty

Re: Excel Spreadsheet

Postby MrBenn on Thu May 29, 2014 5:22 pm

elonpuckhog wrote: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?

MrBenn wrote: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...
Image
PB: 2661 | He's blue... If he were green he would die | No mod would be stupid enough to do that
User avatar
Lieutenant MrBenn
 
Posts: 6880
Joined: Wed Nov 21, 2007 9:32 am
Location: Off Duty

Re: Excel Spreadsheet

Postby elonpuckhog on Thu May 29, 2014 8:39 pm

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.
Corporal 1st Class elonpuckhog
 
Posts: 1511
Joined: Wed Jul 11, 2012 9:01 pm

Re: Excel Spreadsheet

Postby MrBenn on Fri May 30, 2014 4:52 pm

elonpuckhog wrote: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?
Image
PB: 2661 | He's blue... If he were green he would die | No mod would be stupid enough to do that
User avatar
Lieutenant MrBenn
 
Posts: 6880
Joined: Wed Nov 21, 2007 9:32 am
Location: Off Duty

Re: Excel Spreadsheet

Postby elonpuckhog on Fri May 30, 2014 5:05 pm

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).
Corporal 1st Class elonpuckhog
 
Posts: 1511
Joined: Wed Jul 11, 2012 9:01 pm

Re: Excel Spreadsheet

Postby MrBenn on Mon Jun 09, 2014 4:33 pm

elonpuckhog wrote: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
Image
PB: 2661 | He's blue... If he were green he would die | No mod would be stupid enough to do that
User avatar
Lieutenant MrBenn
 
Posts: 6880
Joined: Wed Nov 21, 2007 9:32 am
Location: Off Duty

Previous

Return to Tools Suggestions

Who is online

Users browsing this forum: No registered users

cron