Page 2 of 2
Re: Excel Spreadsheet
Posted:
Mon Apr 28, 2014 2:51 am
by 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.
Re: Excel Spreadsheet
Posted:
Mon Apr 28, 2014 8:10 am
by BGtheBrain
*****
Re: Excel Spreadsheet
Posted:
Wed Apr 30, 2014 7:12 pm
by 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
Re: Excel Spreadsheet
Posted:
Mon May 05, 2014 12:30 am
by DaveH
Better and better! Well done mate!
Re: Excel Spreadsheet
Posted:
Wed May 14, 2014 11:20 am
by 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?
Re: Excel Spreadsheet
Posted:
Wed May 14, 2014 12:06 pm
by dgz345
not from using the game finder. but if you use the api you could work it somehow
Re: Excel Spreadsheet
Posted:
Wed May 14, 2014 3:57 pm
by 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.
Re: Excel Spreadsheet
Posted:
Thu May 15, 2014 12:54 am
by DaveH
You can copy and paste of course - a lot of data but you can then extract the data you need from each game.
Re: Excel Spreadsheet
Posted:
Thu May 15, 2014 1:48 pm
by MrBenn
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.
Re: Excel Spreadsheet
Posted:
Thu May 29, 2014 5:22 pm
by MrBenn
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...
Re: Excel Spreadsheet
Posted:
Thu May 29, 2014 8:39 pm
by 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.
Re: Excel Spreadsheet
Posted:
Fri May 30, 2014 4:52 pm
by MrBenn
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?
Re: Excel Spreadsheet
Posted:
Fri May 30, 2014 5:05 pm
by 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).
Re: Excel Spreadsheet
Posted:
Mon Jun 09, 2014 4:33 pm
by MrBenn
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