This page describes how to extract data from pairs events held on Bridge Base Online (BBO), extract the hand-records, re-score the event, post on a bridge website and submit to the EBU.
Design and coding: Chris Chambers
Tester in chief: Peter Bushby
Thanks to: Anne Edwards, Ian Mitchell, Gordon Rainsford, Conrad Simons
This is done wholly from within an Excel spreadsheet, 'BBO-Scorer' (on this page, just 'scorer'), which you can download from this site below. It uses only mechanisms made public by BBO, BridgeWebs and DDS.
The name includes the version number to ensure stale copies are not downloaded: by all means shorten and save as 'BBO-Scorer.xlsm'.
Keep the scorer in a folder of its own.
Best practice: create a super-folder for scorings and a subfolder for each event. You can copy the spreadsheet forward as you score more events and keep any data you build up. The scorer will create files in that folder and it will make it easier for you to find them.
The spreadsheet includes program code (in addition to spreadsheet formulas) and bears the 'macro enabled' extension, '.xlsm'. You must acknowledge warnings on download and when you first open in Microsoft Excel. Be careful to 'OK', 'Yes', 'Enable', etc. all the dire caveats. If you miss one, close the saved sheet and reopen.
Though logins and passwords may be requested, they are not stored.
1.2a – Enhancements and fixes:
This release adds an interface for analysing the PBN file automatically when it is created from the travellers downloaded from BBO, thus avoiding the necessity to 'go outside' the spreadsheet.
Installation of the calculation library is not essential. If you are happy, for example using Dealer4 as suggested at 'PBN File Creation' below, then update the spreadsheet and go about your scoring.
The analysis engine is the same as employed by many applications (including Dealer4) written by Bo Haglund and Soren Hein (see DDS for background). The authors make available a dynamic link library (DLL) for which I created an Excel interface, another DLL (styled for Excel interaction with the extension .xll). The two DLLs, within a folder, are compressed into a single file.
To install: download the single file, 'DDSolver.zip', from the 'Calculation Library' link above. This is usually saved in a 'download' folder; find and right-click on the name for the short-cut menu which will include 'Extract', 'Unzip' or similar. Extract somewhere sensible (e.g. close to your scoring operations), be careful to retain the containing folder.
Open the new 1.2a sheet and on BBO Data double-click the new cell and browse to the folder chosen above. Thereafter, the solver will install itself, even across different copies and versions of the spreadsheet, as long as the location of solver's containing folder is unchanged.
Also in this release.
Reveal 1.2 details
This release includes options for 'replay links' of the auction and play at each table from the BridgeWebs display. This makes use of an enhancement to Bridge Solver Online, itself already present inside BridgeWebs in the 'Play Again' double-dummy facility (implemented by John Goacher, based on DDS by Bo Haglund and Soren Hein).
The option to include this feature is on the Totals tab, top-left under the date, title and description. It is a drop-down selection, off and two on-settings, whether or not to use real names or logins in the replay box.
To see an example of this, see the results on this site from 11th June, links on the far right of the scorecards.
This enhancement uses an element within the USEBIO file that is non-compliant and only processed by BridgeWebs. Accordingly, if the option is selected, two USEBIO files are produced at the final step, one for BridgeWebs, one for UMS upload prefixed by 'EBU_'.
If replays are not included, as previously, only one file is produced and is effective for both BridgeWebs and UMS submission as before.
Also in this release.
Reveal 1.1h details
For reasons I cannot wholly explain – but were probably user error – the actual copy 1.1g was not the latest. Or at least it isn't now. Certainly it did not include wholly numeric pair-IDs essential for EBUScore. This I hope will be a 'clean' release addressing that and including a few tidies and efficiencies.
.Add2 method on sorting has been reverted to plain .Add to avoid errors with earlier versions of Excel.Reveal 1.1g details
Reveal 1.1f details
Reveal 1.1e details
Reveal 1.1d details
Currently the scorer,
IMP Pairs potentially on request.
It is untested but I would be surprised if the scorer worked on Microsoft 365 for Mac: it makes too much use of odd access methods.
No exceptional facility with Excel is assumed but some would definitely not go amiss.
There are five visible tabs within the workbook:
The downloaded version contains no private data but to provide a picture, a real example has been anonymised.
You will have to enter your mappings from BBO logins to club names on Logins. Currently, EBU Numbers is filtered for our county of Suffolk, but it is easily modified using Power Query. This is included in Excel 2016 and later and is available as an add-in for Excel 2010 and 2013 (which appears to work, despite warnings).
Data is extracted from BBO by a number of means, relevant to the methods each page uses. BBO has the ability to remember your login details and that allows you to easily login and also interrogate playing records, both your own and of others. This is achieved by ticking the 'Remember me' or 'Keep me logged in': those actions store a small piece of data on your device – a cookie – which is used for subsequent, login-free, accesses.
The scorer exploits the same mechanism but for it to work there is something that must be done first.
There is a first-time button which attempts to find the cookie for you. On BBO Data, double-click on the blue-background cell, .
It brings up a dialog asking for your login details and, invisibly, logs in to Internet Explorer. This creates a cookie and returns it (it begins with your BBO username).
If the above fails – and it might – you can discover the cookie in your browser. But the exact method is browser specific. Login to 'myhands':
Do not dismiss the page after successfully logging in – remember to tick the 'Keep me logged in'.
You need to look at 'storage' in that page. In Firefox, Chrome and Microsoft Edge, the easiest way is to enter developer mode with the three-key action 'Ctrl+shift+I'. Look for the 'storage' tab and there, find the 'myhands_token'. Use your keyboard skills to copy/paste or simply take a note.
The text you need begins with your username and does not include quotation marks. Copy it into the cell to the right of the 'Cookie' button on BBO Data.
You do not need to do this again; leave the cookie string in plain view but do not send the sheet to others. My understanding of password cookies is not extensive but I note that on another machine in my house (and therefore attached to the outside world by the same router) my cookie was the same. So, machine identity is not bound in – but IP Address might be. Also, I do not know for certain if BBO cookies expire or are 'kept alive' by use – though I believe the latter.
You can copy the cookie to a new version of the sheet or use the tool to retrieve it again.
From Release 1.2a, the cookie is saved and inserted in new sheets.
You have to enter your own login/real-name pairs on the Logins tab. Overwrite the test data by typing/pasting in, your own.
The data that comes with a download is harmless. You need at least two lines: if you delete all of it, Totals will be full of errors. And clearing, not deleting rows, is always nicer.
EBU Numbers are taken from the 'ScoreBridge' database maintained by the EBU. To change the filter, on the EBU Numbers tab, select the 'Query' menu then the 'Edit' menu-item (far left). Easiest is to change the filter at the top of the window – just as in a filtered area on a worksheet.
From Release 1.2a, there is an option to save login data under a club key.
You will gradually build up a list that cross-references logins / real names / EBU names (the three 'type-in') columns. If you download a new version, copy your accumulated list (in columns A/B/C) over the test-data in the new version (but see comment above for later versions).
There are three steps to routine scoring (really re-scoring of course):
First, set up the data in the first tab, Totals, relevant to this scoring – date and event description. First time through, you must enter the other configuration information along the top four rows. However, much of that will be subsequently unchanged.
Cells with a red-triangle in the upper-right corner have notes and hovering over them shows the text.
On BBO Data, double-click on the blue-background cell, ; that brings up a dialog which prompts for the 4-digit BBO Tourney ID (after the '#'). If your event is recent, say in the last two or three days, the list of Pairs Tournaments (most recent 2000 events) will include it. But currently, with so many tournaments listing does not last long. However, your event is still there and if it cannot be found…
If you know the host-director you can use,
http://webutil.bridgebase.com/v2/tarchive.php?m=h&h=Username&d=Username
Substituting 'Username' for the host-director. From the list, use the 'Boards' link and arrive at a list of travellers. Click on 'Board 1 traveller' and when that is displayed, from the browser address-bar, copy the numbers at the end, they are in three parts, separated by a hyphen.
Alternatively, from the 'myhands' query,
Change the username to anyone who played. Navigate to the first traveller and extract the information as above, ignoring the appending of your player's username.
This is the first of three macros to run. Get up the macro dialog using the two-key action, 'Alt+F8' (that's the simultaneous pressing of the Alt key [bottom left keyboard] and the function key F8 [along the top] – on a laptop, do not in addition, press 'shift' or 'function').
Alternatively, from the sheet, use the shortcut, Ctrl+Shift+A.
What happens now takes a little time. The program retrieves the data from BBO and stores it, board-by-board, in a file in the same folder as the scorer. BBO is a very busy site and this avoids having to go back online if the sheet needs to be rerun.
Progress is indicated in Excel's status bar bottom left and, from Release 1.2a, in a dialog which vanishes once the procedure is complete.
Note: at release 1.1g this changed and was further updated at 1.2: it is quicker when it works, doesn't hang, and control is returned to Excel so a 'Not Responding' state should be short-lived. But sometimes BBO does not send a traveller and not all boards are downloaded.
As of release 1.2, downloading the missing boards will resume after a short interval (2 seconds).
Unless you know something has gone seriously wrong, leave to continue (or save a second or two by 'OK'). At busy times this may take two or three goes. This is not onerous as all previously downloaded travellers are stored and no Internet access is required other than for those not yet retrieved.
At any time – at least, before BBO tidies away the data – you can rerun by repeating the macro, AcquireBBOTravellers (but you will have to reclean the data)
At version 1.2a calculation of makeable contracts happens automatically if the calculation libraries are installed. See the release notes for 1.2a for directions. Once installed, you can ignore the steps in this section's final paragraph.
During this process the deals are extracted and a deal-file is created,
<ISO-date>-<BBO-Ref>-<club-name>-<file.title>.pbn
This does not include contract analysis, just the deals. Feed this into your favourite double-dummy solver. You can upload the file with the generated results (from here, the final step of three) or via the admin area of the website as usual.
Online it is not uncommon for pairs to be replaced part-way through: connections are lost, computers malfunction, substitutes leave unaware of the tournament conditions. Sometime this happens several times. If there is no red on BBO Data after getting the raw BBO travellers that's very good news.
The red boxes above 'NS#' and 'EW#' are counts of the mismatching pairs – that is, where they don't match the uname1/uname2 in the blue-shaded area top left, that of board one.
You must correct the uname1/uname2 entries in the whole of the sheet. Tips:
When you've got rid of the red boxes above NS# and EW#, use 'Alt+F8' again,
Alternatively, from the sheet, use the shortcut, Ctrl+Shift+T.
That will score the event and transfer across all the names to Totals.
USEBIO is a standard format for bridge results and has been used by the EBU since 2010 see http://www.usebio.org/. It is also a supported format for uploading results to BridgeWebs.
Note: the scorer treats adjustments differently to BBO. The Neuberg formula is used by the scorer whereas, possibly in keeping with ACBL practice, BBO is less complex. The adjustment is applied and removed then percentages assigned to the remaining scores in the reduced field. Thus a unique top scores 100% whereas, under Neuberg, it is slightly less.
When you are assured that the results are correct, generate the file by running the macro (another 'Alt+F8'),
Alternatively, from the sheet, use the shortcut, Ctrl+Shift+U.
You will be prompted to upload the results; if you do decide to, a further prompt will require the site password (each time). If you have multiple events each day, take care with the 'BW.Index' on the Totals tab.
First time through, there is an option to upload the PBN file. For completeness, you should do this after you have run it through a double-dummy solver but if you accidentally miss out this step, it will ask you again when the file is updated. If you do not want to recreate and upload the results in this case, use the BridgeWebs interface.
Originally a single USEBIO file was created that served for both BridgeWebs display and submission to the EBU's Universal Membership Scheme. But to show 'replay links' on websites, BridgeWebs used a non-compliant item in the file. Release 1.2, which includes an option to show replays, therefore produces two files, one to upload to BridgeWebs (as above), one for the EBU. The second has the prefix, 'EBU_'.
Now you deserve a drink (in a later version, I hope to include earlier refreshment breaks).
This page updated 2020-07-10.