New! An editable TPA scorecard to share online

[Tann]

TPA Scorecard 4.8 is out:

Link to the .ods file in the first post.

- Add Xenon and TAF Gold Edition
- Leaderboard data updated to June 7th, 2015
- Formulas have been simplified:
* Time of data extraction and calculation reduced to 35s (instead of nearly 1mn)
* All the formulas are now correctly preloaded during the file opening (whatever is the Calc configuration/version)
* Fix the problem of partial data extraction/calculation from the database

 

[vikingerik]

I'd be all over it if it could automatically import scores somehow, either from Farsight online or TPA's local save file.

Suppose I should follow up on that one. Yes, the automatic import in LibreOffice worked fine. Although it depresses me slightly to see all the tables where I've dipped to #3 or #5 and could take back #1 if only I felt like grinding for twelve hours. Which leads to a feature suggestion, it would be great if it could show what the scores above me are. (Generalized in some way to be useful to those who don't habitually reside in the top ten, of course.)

It didn't import any of my challenge data, does it try?

Technical suggestions: The formula in the HOF points column could be simplified as =MIN(F6*10^3/350000000, 1000) to avoid repeating the calculation twice. Also I'd put the table HOF max value in its own column, both to be visible and to make it a proper piece of data rather than buried inside a formula.

"Bram Stocker’s Dracula" has a typo.

 

[Tann]

Suppose I should follow up on that one.

Yep, I didn't think that FS would have ever released the leaderboard master data. But they did it, kudos to them.

Which leads to a feature suggestion, it would be great if it could show what the scores above me are. (Generalized in some way to be useful to those who don't habitually reside in the top ten, of course.

Yes, it would be easy to implement. Something like re-using the data in the rank column:

1st formula:

=VLOOKUP(I6-1;Data.A1:E2000;3;0)

Vertical search for your rank-1 (and -2, -3, etc. if needed), in the hidden Data sheet (2000 lines for Totan), value of the 3rd column (Score).

or

=SUMPRODUCT((Rank=I6-1)*(Table="Tales of the Arabian Nights")*Score)

Same formula than your score extraction, but with a different array (rank-1 instead of your player ID)

I don't know which formula induces a faster calculation time (my two cents on VLOOKUP). I have to test it.


I could also extract the player ID ranked above you, by modifying the column number:

=VLOOKUP(I6-1;Data.A1:E2000;2;0)

I think I could add all that stuff in a new sheet, named "Comparison" or something like that, with the better score on each table, and the 5 scores above you.

It didn't import any of my challenge data, does it try?

No, as the Challenge data are not in the .csv file provided by Farsight. Maybe we could ask FS if they can build it too and post it on the official website?
Fields that still need a manual filling: Goals, Challenge Data, Tournament Ranking.

Technical suggestions: The formula in the HOF points column could be simplified as =MIN(F6*10^3/350000000, 1000) to avoid repeating the calculation twice.

Nice, thanks. I will add it for the next release.

Also I'd put the table HOF max value in its own column, both to be visible and to make it a proper piece of data rather than buried inside a formula.

Actually, you can check all the HOF scores for each table by pointing your mouse on the upper-right corner of the "HOF points" title cell on each Season card (the tiny red square for the tooltip is masked by the red color on the Season 1 sheet, so it can be easily missed.)

At first, I wanted to insert a tooltip in each HOF cell, but once converted to PDF, there are ugly little icons everywhere.

The other problem is that I have not much room remaining. I still can split the Score column, but I keep that for the incoming new difficulty setting, which will have its own leaderboards data (so I will need another "Rank" column, let's say "Rank Hard Difficulty"

"Bram Stocker’s Dracula" has a typo.

Oops, thanks! I will fix it too.

Thanks for your feedback.

 

[Tann]

TPA Scorecard 4.9 is out:

Link to the .ods file in the first post.

- Leaderboard data updated to June 28th, 2015

- Add Red & Ted's Road Show

- All the extraction formulas have been rewritten:
* Time of data extraction and calculation reduced to 1s (yes, 1 second, instead of nearly 35s)
* Fix the problem of the wrong returned values when a Player ID has more than one score on a table (i.e. he plays on two different devices with the same ID)

- The HOF tooltips are now more visible.

- And the BIG new feature for the end: it's now possible to compare your highscores in many different ways, via the "Compare Page". [VS] buttons have been added near each of your ranks and will redirect you to a mini-leaderboard:

Screenshot:

 

[vikingerik]

Nice work, yes the import works almost instantly. And that comparison page is exactly what I wanted to find tables where I can improve my position. Now if only it would stop listing Tarek above me...

 

[Tann]

TPA Scorecard 5.1 is out:

Link to the .ods file in the first post.

- Leaderboard data updated to September 13th, 2015

- Add SafeCracker and SafeCracker : Assault on the Vault
- Add Season 5 scorecard
- Add The Getaway: High Speed 2
- Add a link to latest Invitro's Top 100 (September, 2015)

 

[Tann]

TPA Scorecard 5.2 is out:

Link to the .ods file in the first post.

Note: to open the files, LibreOffice 5.0.2 is now the minimum version required (see the links in the opening post to download it)

- Leaderboard data updated to October 11th, 2015
- Differential data: September 13th, 2015

**** New features:

Huge update as the TPA Scorecard allows now to display the ranking variation on a 1-month period.

A Diff. Rank column on each scorecard and a Diff. Average Rank on the Overview page have been added. It compares the number of ranks lost or won between two scorecards (usually during one or two months).

****

The way the Scorecard works has been slightly modified, as there's now ~250K lines of data. See the Readme file in the .zip folder for more informations.

****

It's now possible to compare all your scores with another player, giving you the total number of win/loss (higher/lower hisghcores) and win percentage against this opponent, and the win/loss data per table.

The comparison per table gives now a Target Rank/Top based on your current ranking. Example: if you're ranked #335 (Top 500) on a table, the minimum score to enter Top 200 (rank 200) will be now displayed above yours, rather than the player directly a rank above like before (not much utility, except in Top 10). The Tops sorting is the same than on the Overview sheet: Top 10, Top 20, Top 50, Top 100, Top 200, Top 500, Top 1000.

****

Other changes:

- ODS File suited for the latest Libreoffice version (5.0.2 mandatory)
- Add F14-Tomcat (HOF score still unknown though)
- Modify the average rank formulas
- Add the total number of tables played on the Overview page.
- Add more navigation buttons (I got a fever, and the only prescription is more buttons!)
- Link to Invitro's Top 100 updated.
- Modify margin and document size for a better pdf export result.


Enjoy.

 

[vikingerik]

I'm still using this. The head to head comparison seems deliberately designed for me to chase Tarek.

I thought the rank differential wasn't working, then finally noticed the readme file that you have to start with Login.ods and follow the instructions into the main sheet. It all worked once I did that. Although that's not exactly intuitive, so whatever you could do to make that smoother would help people pick it up.

Would you consider excluding hacked scores like invitro is doing? Some tables, particularly Big Shot, are a mess up in the top few ranks.

 

[Tann]

I'm still using this. The head to head comparison seems deliberately designed for me to chase Tarek.

Glad you like it. I have a Nemesis too, I chase him to reach the Invitro's Top 100, it's the reason that gave me the idea of head-to-head feature.

I thought the rank differential wasn't working, then finally noticed the readme file that you have to start with Login.ods and follow the instructions into the main sheet. It all worked once I did that. Although that's not exactly intuitive, so whatever you could do to make that smoother would help people pick it up.

Actually, I have a version with the two files merged in one. Everything works right except one major grip: it takes 25 seconds to open it each time. :/

That's why I split the file in two.

But yeah, I agree, it's not user-friendly at all and a little cumbersome the first time (CTRL+SHIFT+F9 is not mandatory, but it's a lot faster than updating the links when opening the file).

I have some idea to improve that and come back to a single file, but it requires some testing first.

EDIT: I tested the trick, and it works perfectly. \o/ I will replace the zip file when it's done.

Would you consider excluding hacked scores like invitro is doing? Some tables, particularly Big Shot, are a mess up in the top few ranks.

I didn't know Invitro excludes more hacked scores from the csv file (I thought it was already done by FS, except on broken table leaderboards like ToTAN). If Invitro can provide a file that lists the excluded scores (I guess he uses some scripts to do to the work), it should be possible to do the same in the scorecard.

I'll ask him.

 

[Tann]

The TPA_Scorecard_5-2 zip file has been updated in the first post.

The Scorecard now works almost the same way than before.

1) Extract the Scorecard and Diff files in the same folder.

2) Open the Scorecard file with Libreoffice Calc.

3) Click "No" in the startup prompt asking to update the links (the startup prompt can be desactived in the Libreoffice options if you wish)

4) Enter your player ID on the Overview page (cell D2) then press Enter.

Et voilà.

 

[vikingerik]

Yes, works fine for me. It also seems to work fine even if you say "yes" to the startup update prompt. I would just let people do the intuitive thing there and not worry about the Ctrl-shift-F9 instruction even if it's a bit slower.

 

[Tann]

Yes, works fine for me. It also seems to work fine even if you say "yes" to the startup update prompt. I would just let people do the intuitive thing there and not worry about the Ctrl-shift-F9 instruction even if it's a bit slower.

Actually, it asks to update the links, i.e. to check if the data have been modified in the other spreadsheet, but it has no purpose here (Diff.ods just stores the old leaderboard data, used for the rank differential stuff). The startup update prompt is activated by default in LO, so I can't prevent it to pop up for another user.

So by clicking "Yes", it reads and checks the 130.000 lines of data for nothing (it takes ~13 seconds).

Whatever, press CTRL+SHIFT+F9 has no use now (I removed it from the instructions): all the calculations for rank differential are now done in the Scorecard, so there's no need to get the results from the other spreadsheet, unlike the previous version I posted yesterday.

 

[Tann]

TPA Scorecard 5.3 is out:

Link to the .ods file in the first post.

- Leaderboard data updated to November 8th, 2015
- Differential data updated to October 11th, 2015

- Add Mary Shelley's Frankenstein (HoF score missing)
- Add F-14 HoF score (thanks to DanBradford)
- Add a Invitro's Top 100 sheet (thanks to Invitro's to provide this top)

 

[Tann]

TPA Scorecard 5.4 is out:

Link to the .ods file in the first post.

- Leaderboard data updated to December 6th, 2015
- Differential data updated to November 8th, 2015
- Invitro's Top 100 updated to November 27th, 2015

Additions:

- Add No Fear: Dangerous Sports
- Add MSF HOF Score (thanks to relaxation)

Fixes:

- Fix the "VS Rank" formulas on the "Compare" sheet.
- Clean some formulas
- Invert BoP and MM lines on the Season One Scorecard

New Features:

- Add the highscore date for each table
- Improve the Season Scorecard presentation for a more convenient reading (cf. the screenshots in the opening post)
- Remove 37 hacked/bogus scores not deleted yet by Farsight in the Leaderboard Data (thanks to Invitro for the list)

 

[Tann]

TPA Scorecard 5.5 is out:

Link to the .ods file in the first post.

- Add Judge Dredd & Judge Dredd Super Game

- Leaderboard data updated to January 3rd, 2016
- Differential data updated to December 6th, 2015
- Invitro's Top 100 updated to December 27th, 2015

Note: I forgot to change the year of the leaderboard data (January 3rd, 2015). Obviously, it's 2016.

 

[Tann]

TPA Scorecard 5.6 is out:

Link to the .ods file in the first post.

- Add Fireball and El Dorado
- Remove 3 tables from the total of played tables (SC:AOTV, TAFG, JD:SG aren't unique tables): these 3 tables don't count anymore into the overall statistics (average ranks, HOF score, etc).
- Add the number of tables played by season.
- Remove Challenge and Tournament Ranking columns (no database available, so it's useless and tedious to fill in)

******
New Feature:

- Add calculation of Invitro's Top 100 points (total, per table, and diff. with the latest Top available). SC:AOTV, TAFG, JD:SG are not taken in account, and there's a modified formula for the tied scores on MSF.

See screenshots in the opening post.
******

- Leaderboard data updated to Februray 7th, 2016
- Differential data updated to January 3rd, 2016
- Invitro's Top 100 updated to December 27th, 2015

 

[Tann]

TPA Scorecard 5.7 is out:

Link to the .ods file in the first post.

- Add Hurricane
- Fix diff. calculation for Season 5 tables

- Leaderboard data updated to March 6th, 2016
- Differential data updated to February 7th, 2016
- Invitro's Top 100 updated to February 19th, 2016

 

[vikingerik]

A really minor flaw: the columns on the comparison sheet aren't wide enough to show the trillion scores on CFTBL.

 

[Tann]

A really minor flaw: the columns on the comparison sheet aren't wide enough to show the trillion scores on CFTBL.

Oh? Weird. It is displayed correctly on my screen (font Trebuchet MS / size 8 pt).

 

[Tann]

TPA Scorecard 5.8 is out:

Link to the .ods file in the first post.

- Add Rescue 911

- Leaderboard data updated to April 3rd, 2016
- Differential data updated to March 6th, 2016
- Invitro's Top 100 updated to March 19th, 2016