2

Sequel to this question: Live statistics chess960 from chess.com?

So suppose I go to like

https://api.chess.com/pub/player/gmwso/games/2020/12

or

https://api.chess.com/pub/player/gmwso/games/2020/12/pgn

there's gonna be a bunch of stuff like say

[UTCDate "2018.01.03"]
[WhiteElo "2706"]
[BlackElo "2940"]

How do I get this data into a spreadsheet like column 1 is all the dates, column 2 is the corresponding white elo, column 3 black elo, col4 white username and col5 black username?


Update 2: Fixed now. see the 'json' vs the 'preformed'. WOW.

Update 1: It appears Mike Steelson has an answer here, where the code is given as

=arrayformula( regexextract(split( substitute(substitute(substitute(getDataJSON(A1;"/games";"/pgn");"[";"");"]";"");"""";"") ;char(10));"\s.*") )

with an example given here

https://docs.google.com/spreadsheets/d/1MX1o5qdy0K3gTMzbimUV3SmFf-0XPCSJ8Vz4IjI-8Ak/copy

It appears there's a problem when it gets to the case of chess960 only. Consider for example this player: Replacing 'gmwso' with the player's username will yield a weird output. i imagine the output will be messier for mixed chess960 and chess.

1
3

How do I get this data into a spreadsheet like column 1 is all the dates, column 2 is the corresponding white elo, column 3 black elo, col4 white username and col5 black username?

From the .pgn file downloaded at

https://api.chess.com/pub/player/gmwso/games/2020/12/pgn

I have created a file called chess_games.xlsx and I have inserted the five values you are asking using a python2 script. You need to install chess and openpyxl libraries with pip. The .pgn file is read as a long string.

import pgn
import openpyxl
import os

pgn_text = open('ChessCom_gmwso_202012.pgn').read()

dates = []
welo = []
belo = []
white = []
black = []

whiteLong = []
blackLong = []

def getValues(file):
    test_str = file
    test_date = "[Date"
    res1 = [i for i in range(len(test_str)) if test_str.startswith(test_date, i)]
    test_welo = "[WhiteElo"
    res2 = [i for i in range(len(test_str)) if test_str.startswith(test_welo, i)]
    test_belo = "[BlackElo"
    res3 = [i for i in range(len(test_str)) if test_str.startswith(test_belo, i)]
    test_white = "[White "
    res4 = [i for i in range(len(test_str)) if test_str.startswith(test_white, i)]
    test_black = "[Black "
    res5 = [i for i in range(len(test_str)) if test_str.startswith(test_black, i)]
    for i in res1:
    dates.append(test_str[i+7:i+17])
    for i in res2:
        welo.append(test_str[i+11:i+15])
    for i in res3:
        belo.append(test_str[i+11:i+15])
    for i in res4:
        whiteLong.append(test_str[i+8:i+43]) //The max length of a nickname in chess.com is 35 characters
    for i in res5:
        blackLong.append(test_str[i+8:i+43])
    for value in whiteLong:
        posClose = value.find("]")
        white.append(value[0:posClose-1])
    for value in blackLong:
        posClose = value.find("]")
        black.append(value[0:posClose-1])

def generateExcel(dates,welo,belo,white,black):
    file = 'chess_games.xlsx'
    if os.path.isfile(file):
        wb = openpyxl.load_workbook(filename=file)
    else:
        wb = openpyxl.Workbook()
    ws = wb["chess_games"]
    for k in range (len(dates)):
        ws['A'+str(k+1)] = dates[k]
    for k in range (len(welo)):
        ws['B'+str(k+1)] = welo[k]
    for k in range (len(belo)):
        ws['C'+str(k+1)] = belo[k]
    for k in range (len(white)):
        ws['D'+str(k+1)] = white[k]
    for k in range (len(black)):
        ws['E'+str(k+1)] = black[k]
    wb.save(file)

getValues(pgn_text)
generateExcel(dates,welo,belo,white,black)

Download the output .xlsx file

0
1

First, worth noting that this question has nothing to do with chess. It is a programming question and a simple one at that.

The answer is very simple. Write a program to read the files and parse them. The pseudo-code looks something like this:

Read the file
For each line

  1. find and extract any keywords like "WhiteELO", "UTCDate", etc.
  2. If there is no keyword then throw the line away (or do something else if you need to)
  3. If there is a keyword then extract and store the associated data

Once you have processed all the lines of the game in the pgn then write a formatted line to your output file.

Note that for spreadsheet-readable files you can use something like ";" (semicolon) as the field delimiter. Probably better than "," (comma) since the title and player name fields are likely to contain commas.

Note that there was nothing whatsoever to do with chess in that answer. It was all simple data processing.

0
1

Update 2: Fixed now. see the 'json' vs the 'preformed'. WOW.

Update 1: It appears Mike Steelson has an answer here, where the code is given as

=arrayformula( regexextract(split( substitute(substitute(substitute(getDataJSON(A1;"/games";"/pgn");"[";"");"]";"");"""";"") ;char(10));"\s.*") )

with an example given here

https://docs.google.com/spreadsheets/d/1MX1o5qdy0K3gTMzbimUV3SmFf-0XPCSJ8Vz4IjI-8Ak/copy

It appears there's a problem when it gets to the case of chess960 only. Consider for example this player: Replacing 'gmwso' with the player's username will yield a weird output. i imagine the output will be messier for mixed chess960 and chess.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.