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.

Excel Spreadsheet

Postby BGtheBrain on Wed Apr 23, 2014 9:46 am

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

Re: Excel Spreadsheet

Postby dgz345 on Wed Apr 23, 2014 9:52 am

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
User avatar
Lieutenant dgz345
Head Tech
Head Tech
 
Posts: 1379
Joined: Thu Oct 07, 2010 10:53 am

Re: Excel Spreadsheet

Postby dgz345 on Wed Apr 23, 2014 9:53 am

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

ill check it when i get home in the weekend
User avatar
Lieutenant dgz345
Head Tech
Head Tech
 
Posts: 1379
Joined: Thu Oct 07, 2010 10:53 am

Re: Excel Spreadsheet

Postby BGtheBrain on Wed Apr 23, 2014 10:29 am

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

Re: Excel Spreadsheet

Postby dgz345 on Wed Apr 23, 2014 5:55 pm

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.
User avatar
Lieutenant dgz345
Head Tech
Head Tech
 
Posts: 1379
Joined: Thu Oct 07, 2010 10:53 am

Re: Excel Spreadsheet

Postby MrBenn on Wed Apr 23, 2014 6:11 pm

dgz345 wrote: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.
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 Swifte on Thu Apr 24, 2014 8:29 am

MrBenn wrote:
dgz345 wrote: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!
User avatar
Colonel Swifte
 
Posts: 2472
Joined: Wed Nov 14, 2007 12:05 pm
Location: usually Mahgreb
2

Re: Excel Spreadsheet

Postby BGtheBrain on Thu Apr 24, 2014 8:34 am

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

Re: Excel Spreadsheet

Postby runewake2 on Thu Apr 24, 2014 10:02 pm

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...
Conquer Club Mobile Apps: Conqueror for Windows Phone and Windows 8
Conqueror's going Open Source: Conqueror on CodePlex
User avatar
Corporal 1st Class runewake2
 
Posts: 95
Joined: Sun May 23, 2010 1:50 pm
Location: Betwixt Two Curly Braces

Re: Excel Spreadsheet

Postby dgz345 on Fri Apr 25, 2014 3:22 am

runewake2 wrote: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>
User avatar
Lieutenant dgz345
Head Tech
Head Tech
 
Posts: 1379
Joined: Thu Oct 07, 2010 10:53 am

Re: Excel Spreadsheet

Postby BGtheBrain on Fri Apr 25, 2014 7:06 am

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

Re: Excel Spreadsheet

Postby dgz345 on Fri Apr 25, 2014 9:10 am

are you using windows? ill maybe create an program for it. i dont know how it will end
User avatar
Lieutenant dgz345
Head Tech
Head Tech
 
Posts: 1379
Joined: Thu Oct 07, 2010 10:53 am

Re: Excel Spreadsheet

Postby BGtheBrain on Fri Apr 25, 2014 9:14 am

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

Re: Excel Spreadsheet

Postby Dukasaur on Fri Apr 25, 2014 10:16 am

BGtheBrain wrote: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.)
Image
User avatar
Captain Dukasaur
Community Team
Community Team
 
Posts: 25031
Joined: Sat Nov 20, 2010 4:49 pm
Location: Beautiful Niagara
22

Re: Excel Spreadsheet

Postby BGtheBrain on Fri Apr 25, 2014 10:18 am

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

Re: Excel Spreadsheet

Postby DaveH on Fri Apr 25, 2014 11:56 am

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.

GamePoints TotalsPlayerPoints
14191088SuicidalSnowman scored -13 points in this gameSuicidalSnowman-13
BGtheBrain scored 149 points in this gameBGtheBrain149
Steve The Mighty scored -17 points in this gameSteve The Mighty-17
Vid_FISO scored -19 points in this gameVid_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.
Image
User avatar
Lieutenant DaveH
 
Posts: 1561
Joined: Sun Dec 06, 2009 9:12 am
Location: Torquay, Devon

Re: Excel Spreadsheet

Postby DaveH on Fri Apr 25, 2014 12:00 pm

If MrBenn could share his code for getting data directly from Internet to spreadsheet I would also appreciate it!
Image
User avatar
Lieutenant DaveH
 
Posts: 1561
Joined: Sun Dec 06, 2009 9:12 am
Location: Torquay, Devon

Re: Excel Spreadsheet

Postby MrBenn on Fri Apr 25, 2014 7:35 pm

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.
Last edited by MrBenn on Fri Apr 25, 2014 8:01 pm, edited 1 time in total.
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 Fri Apr 25, 2014 8:00 pm

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
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 Sat Apr 26, 2014 1:17 am

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

Re: Excel Spreadsheet

Postby BGtheBrain on Sat Apr 26, 2014 6:57 am

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

Re: Excel Spreadsheet

Postby DaveH on Sat Apr 26, 2014 9:03 am

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

Re: Excel Spreadsheet

Postby MrBenn on Sat Apr 26, 2014 10:46 am

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.
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 Sun Apr 27, 2014 3:49 am

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

Re: Excel Spreadsheet

Postby MrBenn on Sun Apr 27, 2014 3:13 pm

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

Next

Return to Tools Suggestions

Who is online

Users browsing this forum: No registered users

cron