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

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.

DaveH

Posts: 1474
Joined: Sun Dec 06, 2009 9:12 am
Location: Torquay, Devon
Medals: 121

### Re: Excel Spreadsheet

*****
Last edited by BGtheBrain on Thu Jun 29, 2017 3:32 pm, edited 1 time in total.

BGtheBrain

Posts: 2768
Joined: Thu Sep 02, 2010 2:50 pm
Medals: 108

### Re: Excel Spreadsheet

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 StringDim APIpath As StringDim xmlDoc As MSXML2.DOMDocumentDim xNode As MSXML2.IXMLDOMNodeDim gData()Dim pData()Dim pRange As RangeDim pStats As RangeDim 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 = pDataCol = UBound(headers(1)) - LBound(headers(1)) + 1Cells(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)) + 1CurPage = 1nextRow = 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 'xmlDocCurPage = CurPage + 1Loop 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 PSet 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 PLACEEnd SubFunction 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`

PB: 2661 | He's blue... If he were green he would die | No mod would be stupid enough to do that

MrBenn

Posts: 6879
Joined: Wed Nov 21, 2007 9:32 am
Location: Off Duty
Medals: 67

### Re: Excel Spreadsheet

Better and better! Well done mate!

DaveH

Posts: 1474
Joined: Sun Dec 06, 2009 9:12 am
Location: Torquay, Devon
Medals: 121

### Re: Excel Spreadsheet

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

Posts: 1508
Joined: Wed Jul 11, 2012 9:01 pm
Medals: 77

### Re: Excel Spreadsheet

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

dgz345

Posts: 1171
Joined: Thu Oct 07, 2010 10:53 am
Medals: 58

### Re: Excel Spreadsheet

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

Posts: 1508
Joined: Wed Jul 11, 2012 9:01 pm
Medals: 77

### Re: Excel Spreadsheet

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

DaveH

Posts: 1474
Joined: Sun Dec 06, 2009 9:12 am
Location: Torquay, Devon
Medals: 121

### Re: Excel Spreadsheet

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.

PB: 2661 | He's blue... If he were green he would die | No mod would be stupid enough to do that

MrBenn

Posts: 6879
Joined: Wed Nov 21, 2007 9:32 am
Location: Off Duty
Medals: 67

### Re: Excel Spreadsheet

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

PB: 2661 | He's blue... If he were green he would die | No mod would be stupid enough to do that

MrBenn

Posts: 6879
Joined: Wed Nov 21, 2007 9:32 am
Location: Off Duty
Medals: 67

### Re: Excel Spreadsheet

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

Posts: 1508
Joined: Wed Jul 11, 2012 9:01 pm
Medals: 77

### Re: Excel Spreadsheet

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?

PB: 2661 | He's blue... If he were green he would die | No mod would be stupid enough to do that

MrBenn

Posts: 6879
Joined: Wed Nov 21, 2007 9:32 am
Location: Off Duty
Medals: 67

### Re: Excel Spreadsheet

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

Posts: 1508
Joined: Wed Jul 11, 2012 9:01 pm
Medals: 77

### Re: Excel Spreadsheet

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

PB: 2661 | He's blue... If he were green he would die | No mod would be stupid enough to do that

MrBenn

Posts: 6879
Joined: Wed Nov 21, 2007 9:32 am
Location: Off Duty
Medals: 67

Previous