Using excel for a chessgame database - Please help!

[mr_bono]

Good afternoon all,
I'm new here, so first of all, welcome.

A few weeks ago I started to play chess, actively, online and in real-life. And I thought that it would be a good idea to 'store' all my games, so I could analyze them whenever I want.

I use MS Excel for this and the way the chessgames are stored is the so-called "PGN-style". It is simply a long textstring with all the moves, ie: 1. d4 d5 2. Nf3 e6 3. Nc3 Nc6. (Each 'move' consists of two moves, one for white and one for black). So far it's easy.

But, as some of you might know, there are standard 'ECO' chess openings. For instance: 1. b4 Nh6 is called the Polish opening, 1. g3 e5 2. Nf3 is called the Benko's opening.. I have one sheet full of all the openings in the world (2018 in total).

On a other sheet I put the games I played. The date, place, time and all the moves in PGN-style. So, what I wanna know is: what opening did I play in a particular game of chess. The problem (for me) is that some games have 30 moves, others 90 moves. On the other hand, some standard 'ECO' openings exists of 2 moves, other of 8 moves. So for me it's to difficult to compare my games to the standard openings.

How the hell am I going to solve this problem?? I really, really hope someone can help me. If something is unclear, feel free to ask. Thanks in advanced.

Mr_bono

 

[etaf]

do you therefore need to check the first 8 moves and test against a list to see what the move is

can you give some more examples

would 2 different opens all depend on the 8th move
and others can be decided in the first move ?

 

[mr_bono]

Thanks for your reaction and sorry if I was unclear.

What I meant was the following: I have a total of 2018 standard openings.
These consist of 1 or more move (see example):

An this goes on for 2000 rows...

So if my chess game PGN is something like this:
1. g4 d5 2. Bg2 Bxg4 3. c4 e4 4. e3 b6 5. Ne5 Nb4 6. Bb5+ Bd7 7. Nxd7 a6 8. Nxb6+ Ke7...... etc. I want Excel to say: this is the A00 Grob, Fritz Gambit..

The problem is that in this example, Excel need to check the first 5 moves; because the A00 Grob, Fritz Gambit consists of 5 moves.
Other openings may depend on less or more moves so it's unclear how many moves Excel has to check..

 

[Snakehips]

mr_bono,

Can you confirm that your game PNG is a single text string?

 

[mr_bono]

Hi Snakehips,

I have the game PGN's as a single text string, and in another sheet, I seperated the moves so each move is in a single column (by using text to columns).

 

[Marcelo Branco]

A possible solution

Create a helper column, say column AA, in the spreadsheet containing the names of the openings and the corresponding movements - assumed as Sheet1 (adjust to suit)

Put this formula in AA2
=TRIM(D2&CHAR(32)&E2&CHAR(32)&G2&CHAR(32)&H2&CHAR(32)&J2&CHAR(32)&K2&CHAR(32)&M2&CHAR(32)&N2)
copy down till the last row with data


Then for a particular game put the first 8 moves in A2, separated by just one space , like below


[TABLE="class: grid"]
<TBODY>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

First 8 moves​

[/TD]
[TD]

Opening​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

g4 d5 Bg2 Bxg4 c4 e4 e5 b6​

[/TD]
[TD]

A00 Grob, Fritz Gambit​

[/TD]
[/TR]
</TBODY>[/TABLE]


Formula in B2
=LOOKUP(2,1/((Sheet1!$AA$2:$AA$2100<>"")*(ISNUMBER(MATCH(Sheet1!$AA$2:$AA$2100&"*",A2,0)))),Sheet1!$B$2:$B$2100)

Hope this helps

M.

 

[Snakehips]

M, mr_bono

I like that! I was thinking along similar lines but could not quite nail it.

I don't think that there is a need to isolate the first 8 moves in column A?

Should be ok to use the full existing PNG string?

 

[Marcelo Branco]

Hi Tony

The only problem I see in my formula, is that it requires that the openings are "ordered" in terms of complexity, i.e., number of movements.
(hope the above sentence makes sense)

It fails, for example, if b4 NH6 is before than b4.

M.

 

[mr_bono]

Thanks for the suggestions guys.
Would it help if I send my excel document or put it online if that is possible?

Mr_bono

 

[Marcelo Branco]

Thanks for the suggestions guys.
Would it help if I send my excel document or put it online if that is possible?

Mr_bono

I think so. Please, put the file online and provide the link.

M.