BBO Discussion Forums: Tricks for working with Lin Files in EXCEL - BBO Discussion Forums

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Tricks for working with Lin Files in EXCEL A thread to share excel tricks

#1 User is offline   inquiry 

  • PipPipPipPipPipPipPipPipPipPip
  • Group: Admin
  • Posts: 14,566
  • Joined: 2003-February-13
  • Gender:Male
  • Location:Amelia Island, FL
  • Interests:Bridge, what else?

Posted 2014-May-22, 08:46

THE BASIC PRINCIPLE

I was asked in another thread to discuss how I use excel to store, track, categorize, etc, as well as post hands here in the forum.

To post a hand, the equation is fairly simple. To illustrate, lets assume you label these cells as follows"
A1: Forum post
B1: Comments
C!: BBO http hand record

  • In Cell A2. place the following equation:This equation strips off the http and subsequent handviewer content, just leaving the lin file format of the hand.
=CONCATENATE("[hv=lin=",RIGHT(C2,LEN(C2)-52),"]400|300|",B2,"[/hv]")  

  • In Cell B2, type some of your comments about the hand: Test comments for a JEC match from May 21. 2014 . Note this simple version leaves the players name in the hand recond
  • In Cell C2, paste the hand your are interested in posting. I grabbed the following JEC hand from yesterday
http://www.bridgebase.com/tools/handviewer.html?lin=pn|JEC,wolperts,miamiwiz,dannymiles|st%7C%7Cmd%7C4S2468HAD678TAC24A%2CS9TH23JD9C356789T%2CS7JQKAH456TD2QKCJ%2C%7Crh%7C%7Cah%7CBoard%2010%7Csv%7Cb%7Cmb%7C1H%7Cmb%7Cd%7Cmb%7C2H%7Can%7Cweaker%20bid%7Cmb%7C3H%7Cmb%7Cp%7Cmb%7C3S%7Cmb%7Cp%7Cmb%7C4N%7Cmb%7Cp%7Cmb%7C5C%7Cmb%7Cp%7Cmb%7C6S%7Cmb%7Cp%7Cmb%7Cp%7Cmb%7Cp%7Cpc%7CD9%7Cpc%7CD2%7Cpc%7CD3%7Cpc%7CDT%7Cpc%7CS2%7Cpc%7CS9%7Cpc%7CSA%7Cpc%7CS3%7Cpc%7CSK%7Cpc%7CS5%7Cpc%7CS4%7Cpc%7CST%7Cmc%7C13%7C


Simply copy the value in cell A2 and paste into a new post, as shown below.
Test comments for a JEC match from May 21. 2014 . Note this simple version leaves the players name in the hand recond


IF you don't want the players names posted, you can edit them out of the cut-and-paste posting in the forum, or if you think you never want the names, you can edit the equation in cell A2 as shown in the code below. This not only strips off the handviewer content like earlier, it also removes the player names but leaves the remainder of the lin file format with the hands, bidding, and play. When you cut and paste from A2 to the forum you get the following (I also edited the text in excel before cutting and pasting).

=CONCATENATE("[hv=lin=",RIGHT(C3,LEN(C3)-SEARCH("|st%7C",C3)),"]400|300|",B3,"[/hv]")
.
Test comments for a JEC match from May 21. 2014 . Note this modified version automatically takes the players names out of the hand record.

--Ben--

#2 User is offline   barmar 

  • PipPipPipPipPipPipPipPipPipPipPipPip
  • Group: Admin
  • Posts: 21,398
  • Joined: 2004-August-21
  • Gender:Male

Posted 2014-May-22, 09:12

You already have a pinned thread about using EXCEL for LIN files. Is this information already in there? Or should this thread be merged into it?

#3 User is offline   inquiry 

  • PipPipPipPipPipPipPipPipPipPip
  • Group: Admin
  • Posts: 14,566
  • Joined: 2003-February-13
  • Gender:Male
  • Location:Amelia Island, FL
  • Interests:Bridge, what else?

Posted 2014-May-22, 11:35

View Postbarmar, on 2014-May-22, 09:12, said:

You already have a pinned thread about using EXCEL for LIN files. Is this information already in there? Or should this thread be merged into it?



I plan on adding a lot to this thread, starting with how to rotate the hands for display automatically. Already this trhead shows how to automate stripping out the players names automatically. So, no, I don't think merging is a good idea. This is in response to a specific question, so I what to show different things you can do to automate things with excel.

And I would like to get feedback from others who when see the crazy ways I do things can suggest a more elegant solution using excel.
--Ben--

#4 User is offline   inquiry 

  • PipPipPipPipPipPipPipPipPipPip
  • Group: Admin
  • Posts: 14,566
  • Joined: 2003-February-13
  • Gender:Male
  • Location:Amelia Island, FL
  • Interests:Bridge, what else?

Posted 2014-May-22, 12:02

If you want to post hands from your excel database you might build using your own hands or interesting hands you stumble upon. You may want to be able to rotate the hands. To do so, the bidding has to rotate and the correct vulnerability has to be maintained. There are a number of approaches. If you ever want to use your database to search by HCP in the individual hands, it might be advisable to pull the hands out by seat (it's not necessary). But in the following post I will explain how I automated board 6 from this JEC match: click on board 6 to see hand in browser. As you can see by clicking next, the play is correct in each version, but only when North in the original (un-rotated) version is sitting in the South seat (third movie) does the hand diagram play like what we expect (with declarer in the south seat).

Original hand, unrotated
Rotated so that West was in south seat
Rotated so that north was in the south seat
Rotates so the East was in the south seat

--Ben--

#5 User is offline   PhilKing 

  • PipPipPipPipPipPipPip
  • Group: Advanced Members
  • Posts: 3,240
  • Joined: 2012-June-25

Posted 2014-May-22, 15:17

This thread is going to become legendary.
0

#6 User is offline   inquiry 

  • PipPipPipPipPipPipPipPipPipPip
  • Group: Admin
  • Posts: 14,566
  • Joined: 2003-February-13
  • Gender:Male
  • Location:Amelia Island, FL
  • Interests:Bridge, what else?

Posted 2014-May-22, 19:57

BASIC STRING MANIPLUATION TO ROTATE BRIDGE HANDS

To manipulate the lin file in excel, you have to work with text strings (the entire handviewer thing you cut and paste into excel is a single text string).

Label the following excel cells
A1: Forum post
B1: Comments
C1: DLR
D1: Decl
E1: Vul
F1: cDLR
G1: cDecl
i1: http hand

Paste some handviewer hand record into cell i2.

The first thing you need to know when rotating a file is who dealt the original hand, and who is the declarer (or which position) you want to be declarer. The next thing you need to know is what is the vulnerability.

Let's start with who dealt the hand. This is easy, we will call the four plays 1, 2, 3, and 4 where 1=south, 2=west, 3=north, and 4=East.
This equation will extract into a cell for who is dealer. Enter this equation into cell C2
=MID(i2,SEARCH("%7Cmd",i2)+8,1) (where I2 is where you pasted your hand record). This returns a 1, 2, 3 or 4.

To extract the vulnerability you can use this equation Enter this equation into cell E2
=MID(i2,SEARCH("%7Cmb",ci)-1,1) (where i2 is where you pasted your hand record). This returns a 0, e, n, b. When rotating hands, you don't need to change the vul if it is 0 on b, and you only need to change the "e" to "n" or "n" to "e" if you rotate the hand one or three steps. If you rotate is 0 or 2 steps, no vulnerability change is needed.

So when I rotate hands, I have one cell with the Actual declarer position when the hand was played. There will be ways to automate this, but the easiest is when you paste a hand into excel, enter the real declarer position as either "N", "E", "W", "S". So enter the real declarer position into cell D2.'

The automation assumes you want declarer to be "south" so it will rotate the hands depending upon which letter you enter here. Before we get to how to handle the actual hands during rotating, lets examine how we calculate what the vulnerability will be after the hands are rotated. Lets make a few assumptions. First if when we rotate the hands, the dealer will have to change. We show the new dealer in cell F2 using the following equation.
=ABS(C2-IF(D2="S",0,IF(D2="W",1,IF(D2="N",2,3))))

In cell G2, we correct the vulnerability for the rotated hand using this equation.
=IF(OR(E2="b",E2="0"),E2,IF(ISEVEN(C2-F2),E2,IF(E2="e","n","e")))

The equations above get you ready to rotate the hand. The actual part of the hands that need to be rotated are the cards. As you may know the cards are stored in the order of South's hand,West hands,north hands,East hand. Actually the east hand is not given to us on the current bbo search engine (handviewer calculates the last 13 cards for you). So what we need do is: if West is the original declarer, change the hand record so they are in this order (original positions retained in title (west,north,east,south), and if North is the dealer (north,east,south,west), and finally if east is the dealer (east,south,west,north).

This means based upon the original declarer, you need to extract the hands. Let's take a simple example. West is the declarer. So you will want to start the hand record by removing the south hand from the beginning of the string, and reinserting it at the end of the hand string. One way to do this is to first, extract the string that represents all four hands in the correct order. Let's call this the SWNE order. The following equation (in cell M2 will do just that, again where i2 is the http hand record...
=CONCATENATE(LEFT(RIGHT(I2,LEN(I2)-SEARCH("md%7C",I2)-5),60),) this returns (the %2C's are not a mistake).
S46JQAH89AD26C6TJ%2CS357H56JD578TC457%2CS8TH34TD9JKC238KA%2C

Now to get the sequence WNES, NESW, and ESWN you can just lob off the first hand each time and add it back to end in cells N2, O2, and P2
N2 contains: =CONCATENATE(RIGHT(M2,40),",",LEFT(M2,17))
O2 contains: =CONCATENATE(RIGHT(N2,38),",",LEFT(N2,17))
P2 contains: =CONCATENATE(",",LEFT(M2,57))

Excel will "pick" the correct version of the hands (SWNE, WNES, NESW, or ESWN) based upon the value in "DECLARER" column (cell D2 in this example). Normally I would include this pick in the equation in cell A2, but here to show how it is done, lets put the following equation in cell L2

=IF(D2="S",M2,IF(D2="W",N2,IF(D2="N",O2,P2))) This equation effectively rotates the "Declarer" as identified in Cell D2 to "south".

Reconstruct the lin file information. After gathering all the pieces (vul, dealer, cVul, cDel, hands rotated for all positions), it is time to put the pieces together, along with any comments by the following equation in cell A2

=CONCATENATE("[hv=lin=md|",F2,L2,"|st||sv|",G2,"|m",RIGHT(I2,LEN(I2)-SEARCH("mb%7C",I2)),"]400|300|",B2,"[/hv]") 


That will create the handviewer example shown above with the bidding, hands, vulnerability, play all corrected for the rotated hand. Let's break out what each part of the equation does.

=CONCATENATE("[hv=lin=md|", <--- this part sets calls the handviewer applet to display the lin hand, the md| part replaces needed things stripped out.
F2,  <-- inserts the corrected dealer from cell F2
L2,  <-- inserts the rotated hands based on who is declarer from cell L2
"|st||sv|", <-- inserts some needed text to set up the correct vulnerability
G2, <--- Inserts the correct vulnerability (0, n, e, b) based on the rotated had
"|m", <-- adds a piece of text needed to start the bidding
RIGHT(I2,LEN(I2)-SEARCH("mb%7C",I2)), <-- inserts all the bidding and play from the hand
"] 400|300|", <-- sets the size of the hand diagram
B2, <-- adds any comments you may have made about the hand in cell B2
"[/hv]") <-- closes the handviewer applet that the hv tag opens. 


You can copy the field to subsequent rows and to keep multiple rows each one handling one hand. Thousands of hands could be on one spreadsheet.

No one needs type the equations into excel. If you are interested in this specific use of excel, I will be glad to email you a spreadsheet described as above. The point was to show how to manipulate the text strings.

Next we show how to build a "bridgebrowser" type datatbase spreadsheet that allows you find your hands based upon your opening bid, opponents bid, partners response, openers rebid, responders second bid. combinations of those, final contract, opening lead, your hcp, etc. I am certain the equations I use in building the various parts of the database could use some improvement (mine work, there are going to be more elegant solutions, however, I am sure).

If you wanted to display the rotated hand in a modern browser, you could make a slight change to a copy of cell A2 which you paste in cell K2. Here is the equation
CONCATENATE("htp://www.bridgebase.com/tools/handviewer.html?lin=md|",F2,L2,"|st||sv|",G2,"|m",RIGHT(I2,LEN(I2)-SEARCH("mb%7C",I2))) 

--Ben--

#7 User is offline   inquiry 

  • PipPipPipPipPipPipPipPipPipPip
  • Group: Admin
  • Posts: 14,566
  • Joined: 2003-February-13
  • Gender:Male
  • Location:Amelia Island, FL
  • Interests:Bridge, what else?

Posted 2014-May-23, 12:17

STARTING AN EXCEL BRIDGEHAND DATABASE

We are going to start building an excel bridge hand database. This will take several post to show the logic, and we will build a simple one and a complex one, depending upon your needs. The main advantage of the complex one is it will have the par contract and all makeable contracts stored in the database.

To build the simple one, we need to grab the hands you want. One way is to just take hands one at a time and paste them into excel. It will take years to get a large database. Another way is to import hands of interest. If you are only taking your own hands is probably the use most people will want for their large database. The simple database will grab hands from BBO Myhands database webpage. This will be the start of the database, you will grab future hands from this website as well. A more complex database will use the Double Dummy Solver to grab the hands, and will include all makeable contracts for each hand in the database. The complex database will also have stored in it the "par contract" which you can search on, should you like. The choice of which to use is up to you.

For this example (if you would like to follow along), I will use the hands played by JEC in April. The my hands database currently contains 1818 hands by JEC from April 10 to the end of April. That is enough to show how the process works effectively.

Step 1. Simple Database: go to the website above, enter JEC as player to search for (or you can use your own user name), enter the start date of April 30th (not 31).. and player name JEC and Interval to retrieve as 1 month. Then press search. This will take a few seconds.
Step 2. When the page of results appears, press Control A to select all, then control C to copy the page content. Got to a blank Excel spreadsheet and press Control and V to paste the content.
Step 3. Getting just the hands with a single header. Find the row that begins with a it should be the 9th row. Use the mouse to select the Nº and a few other cells to the right of it.
Step 4. From Excel's home page, click the Sort&Filter icon, then select menu item filter.
Step 5. Click the down arrow key on the Nº cell and select "number filter" in the drop down box and pick "less than" and enter a big number, like 30000.
Step 6. Select all the displayed text from the row beginning with the Nº both left and right (columns all the way over to traveler and rows all the way down to hand record 1818.
Step 7. Copy the selected text and paste it on another spreadsheet page.

For the complex database.
STEP 1. Use the double dummy solver (homepage for DDS), In DDS click the "download result" menu, enter JEC as BBO username under the BBO section, and click the last calendar month radio button. Then click download. After a short time, it will tell you to save the file, save it as the default JEC-2014-04.pbn
STEP 2. After you save it, click the "scorecard" menu item, then from the menu, click the "calculate par result" option. This will take a while, go watch a TV show or come back to the forum and type start some new topics... it will run in the background.
STEP 3. After the double dummy stuff is complete, save the file. Then click on scorecard (if not already displayed) and under file, click save as scorecard and after that is saved, click save as DD table as cvs file.
STEP 4. Open the JEC-2014-04-Scorecard and JEC-2014-04 DD Table files in Excel
STEP 5. In Column D, copy then name from Cell D2 cells D1819.
STEP 6. Drag the spreadsheet labeled JEC-2014-04 DD table from the file with the same name over to the file labeled JEC-2014-04 Scorecard (so that file now has two spreadsheets).

You should now have one of two (or maybe both) of the start of a database. You can already do some neat stuff with this. For instance, you can select the first column in either "database" and click on filter. Then go to the column labeled "result", use the pull down arrow and select the filter that says begins with and enter a 7. In either database you will find that out of the 1818 hands, there were 13 grand slams bid (many as saves). A sad point right now is the hands are not yet in either database. We will address that in the next post, and adding equations for extracting vulnerability, opening lead, and ways to probe the individual bids in the auction a bunch of other thing.


--Ben--

#8 User is offline   inquiry 

  • PipPipPipPipPipPipPipPipPipPip
  • Group: Admin
  • Posts: 14,566
  • Joined: 2003-February-13
  • Gender:Male
  • Location:Amelia Island, FL
  • Interests:Bridge, what else?

Posted 2014-May-25, 09:55

GETTING THE HAND RECORDS INTO THE DATABASE

If you are building an interesting hand database, where only selected hands go into the database, it makes sense to just cut and paste the hand record into excel from a lin file or handviewer hyperlink directly, along with any comments you want. The first post in this database provides the basics for doing that. You can add subsequent tweaks we will see that can be used with larger databases.

When you grab lots of data, like all the hands you or someone else played in a month, or all the different ways people played a hand single hand in a tournament, you really don't want to do a lot of cutting and pasting. You want to automate the process. When BBO first made myhands available it had two hyperlinks in it. One was to download a lin file one was to show the traveler. The only way to automate the hand retrieval was after you played with the NetbridgevU (BBO windows client) you could call up the hands from the c:/bridge base online/hands/your username/xx-xx-xx-x.lin where xx's gave the date played. Open that lin file and copy the hand records and paste them into the excel spreadsheet you were making (doing this once a day for each day of hands you added to the database). Each hand record was one line and it pasted directly into excel. It was a little painful if you changed tables multiple times in a day but was manageable. Even today if you use the windows client instead of FLASH, this method is available to you to enter the hands played into the database.

When BBO came out with their excellent handviewer way of displaying the hands so you see the hands in a browser, they replaced the download a lin file link with a hyperlink that pointed directly to the hand in the format of lin file, that looked like this example (but with two t's in http)..
 htp://www.bridgebase.com/tools/handviewer.html?lin=
[font="MS Gothic"]pn|JEC,radiators,smispi,thurston|qx|o2,BOARD 2|rh||ah|Board 2|md|4S86HA62DA9CAT9763,S932HK7DKQ874CQ52,SKQT4HJ543DJ62CK4|sv|n|em|NS 0.00|sa|0|mb|p|mb|1C|mb|p|mb|1H|mb|p|mb|2C|mb|p|mb|p|mb|p|pg||pc|DK|pc|D2|pc|D3|pc|DA|pg||pc|D9|pc|DQ|pc|D6|pc|DT|pg||pc|S9|pc|SQ|pc|SA|pc|S6|pg||pc|H8|pc|HA|pc|H7|pc|H3|pg||pc|CA|pc|C2|pc|C4|pc|C8|pg||pc|C3|pc|C5|pc|CK|pc|CJ|pg||pc|DJ|pc|D5|pc|H6|pc|D4|pg||pc|SK|pc|S5|pc|S8|pc|S3|pg||mc|9|pg||[/font]

That was perfect, you could use a well known excel function (GetURL) to extract the complete lin file record from each hand in the database you copied from myhands. Adding hands to the database was a one step process... go to myhands, find all the hands in the range you were interested in, and paste the entire thing into excel. Walla, you were done. That worked for about a year or two great!

However, people were unhappy that we lost the function to manually click on each lin file to download a copy of the hand in question to our hard drives. So BBO made three changes to the format of the data returned by myhands. Both lethal to the one step process. First, they added back the lin file download link, second they made the download link appear in the same cell as the hyperlink to the handviewer version of the hand record, and third they made the underlying hyperlink to an indirect reference for the hand (so instead of getting a record with complete instructions about the hand, you got a hyperlink to something that looks like this... (htp://www.bridgebase.com/tools/handviewer.html?myhand=M-8078965-1400852067). After a month or two, that hyperlink is broken. You can postethose hyperlinks in the forum but the hands disappear forever when they are removed from the myhands database. And you can not query the indirect reference for the opening bid, the hcp, the player names, etc. After BBO made these changes, I had to go back and grab each lin file from my hard drive and paste the hands in (making sure I lined the newly pasted hands up with the hand records grabbed from the myhands site).

Some day there maybe a simple way to grabbing all the hands at once in a single file (see uday's post in this thread....and hotshots offer to make a crawler to grab the data. Feel free to add your voice to that would be a good idea). and getting them pasted into the excel bridge hand databases. Removing the indirect reference would also stop people from copying the indirect reference from a myhand search and pasting it in the forum. Those hands show up in the forum for only as long as the hand remains in searchable on the myhands database and then the link is broken and the hand disappears from the post. Another possible solution is available, I have discovered that the source file after a search on myhands contains, in addition to the indirect link to hand mentioned above, an onclick reference that contains all the needed information (see code segment below), so if someone was clever with greasemonkey or imacro, or IE7pro we can probably get designed a script to massage the results of myhands to display the data in the proper format (again, I removed a "t" from http in the text):

<td class="movie"><A href="htp://www.bridgebase.com/tools/handviewer.html?myhand=M-9063435-1400938217" onclick="hv_popuplin('pn|JEC,vito,smispi,sixela|st%7C%7Cmd%7C3S269JHJKD2367QKC8%2CSQH78QD58C2356TQK%2CS347TH59TD9JAC4JA%2C%7Crh%7C%7Cah%7CBoard%201%7Csv%7Co%7Cmb%7Cp%7Cmb%7C1H%7Cmb%7C2D%7Cmb%7C3C%7Cmb%7Cd%21%7Can%7CS%20%2B%20D%20tolerance%7Cmb%7Cp%7Cmb%7C3S%7Cmb%7C4H%7Cmb%7Cp%7Cmb%7Cp%7Cmb%7Cp%7Cpc%7CS6%7Cpc%7CSQ%7Cpc%7CS7%7Cpc%7CS5%7Cpc%7CH7%7Cpc%7CH5%7Cpc%7CHA%7Cpc%7CHJ%7Cpc%7CSA%7Cpc%7CS2%7Cpc%7CD5%7Cpc%7CS3%7Cpc%7CSK%7Cpc%7CS9%7Cpc%7CD8%7Cpc%7CS4%7Cpc%7CC7%7Cpc%7CC8%7Cpc%7CCK%7Cpc%7CCA%7Cpc%7CD9%7Cpc%7CDT%7Cpc%7CDQ%7Cpc%7CH8%7Cpc%7CCQ%7Cpc%7CC4%7Cpc%7CC9%7Cpc%7CD2%7Cpc%7CC2%7Cpc%7CCJ%7Cpc%7CH2%7Cpc%7CD3%7Cpc%7CD4%7Cpc%7CD6%7Cpc%7CHQ%7Cpc%7CDJ%7Cpc%7CC3%7Cpc%7CDA%7Cpc%7CS8%7Cpc%7CHK%7Cpc%7CDK%7Cpc%7CC5%7Cpc%7CST%7Cpc%7CH3%7Cpc%7CH4%7Cpc%7CD7%7Cmc%7C9%7C');this.style.color='red';return false;">Movie</A>&nbsp;or&nbsp;<A HREF="fetchlin.php?id=9063435&when_played=1400938217">Lin</A></td>
  


There became a reasonable work around when Bridge Captain released a version of double dummy solver that could download hands from the myhand site that works as an acceptable kludge until we can get one of the solutions from the previous paragraph working. You use Double Dummy solver to download a month worth o hands at one time (or a week, or 24 hours). Next you use any of the PBN to LIN file converters to convert the pbn file created by Double dummy solver into a lin file. Then, make sure each lin file exist as a single "paragraph". You may need to massage the PBN file from Double Dummy solver before you convert to Lin, depending upon which program you use to convert it. You can certainly write a little code or use Macro's in words to make sure the lin hands are one paragraph each before cut-and-pasting into excel.

Once you get the hands into Excel-matched up with myhands table with results and players, you are ready to insert equations that will extract from the hands other information that you might want to search by. We will get to those issue in the next post. In the meanwhile, if having a database of bridgehands that can be probed and tested sounds like something you would be interested in, please support hotshot's suggestion that the ability to download all the lin file hands to a single file be implemented. Or at the very least, the indirect reference be changed to the direct hand reference. That would not only help with people building databases, it would help with the posting of interesting hands in these forums.
--Ben--

#9 User is offline   inquiry 

  • PipPipPipPipPipPipPipPipPipPip
  • Group: Admin
  • Posts: 14,566
  • Joined: 2003-February-13
  • Gender:Male
  • Location:Amelia Island, FL
  • Interests:Bridge, what else?

Posted 2014-June-15, 18:21

Posted Image

Ok, I since I am back, let's continue the database building. I have posted in another thread how to "manipulate" the output from myhands using greasemonkey script. For the purpose of this study, I did just that. The output now includes the hyperlink in the correct format to use in excel with no further manipulation.

So I used this method to grab all the hands played by JEC from April 10th through May 31, 2014. There were 4293 such hands during this time period. After I copied and pasted these hands into Excel (and removed the nebulas Lines telling us team number and repetitive headers (time, north, south, date, etc), I extracted the Lin file from the "movie" hyperlink (no need to go to the hyperlink, the grease monkey script has imbedded the lin file as a http hyperlink directly in the movie link). From that I extract the opening bid, each of the first 16 bids, the vulnerability, the dealer, and other features each into the individual excel cells. Now I can search by a lot of potential factors.

For instance, how many times did JEC or his partner downgrade a balanced 18 HCP hand an open 1NT? The answer was four times... out of the 4293 hands.. Here are the four hands.
Notice the 4333 shape for the downgrade.

Notice 4333 general shape in each of these four hands.
Again
Once again 4333


Out of the 4000+ hands, JEC never upgraded 14 point hands, but some of his partners did (a total of six times. All in all the partnership of JEC opened 1NT a total of 288 times and average +1.10184 imps per 1NT opening bid. JEC himself opened 1NT 144 times (amazing 50-50 split) and averaged 0.870347 imps per 1NT opening bid.

This just gives you an idea how you might begin using such an excel database build from myhands. Next will will add double dummy info to the database.
--Ben--

#10 User is offline   inquiry 

  • PipPipPipPipPipPipPipPipPipPip
  • Group: Admin
  • Posts: 14,566
  • Joined: 2003-February-13
  • Gender:Male
  • Location:Amelia Island, FL
  • Interests:Bridge, what else?

Posted 2014-June-25, 22:52

Many of you will want to have the par contracts and the double dummy number of tricks for each hand in your Excel Database (built as shown above). Using Double Dummy Solver (DDS) by Bridge captain, you can add these to the database with just a little extra work.

First you will have to put all the hands from your database into the double dummy solver. This works best a few hundred hands at a time, or all of one month from myhands database. When you get the hands loaded into DDS, click the option menu item and make sure the name of the player you are using (probably you, in the case of the database we are building, JEC) is picked as "the fixed player". IF you download the hands for a week or month then of course the picked player will be known

Then click the scorecard and click on the "calculate par result" menu item. Let it calculate the par result for all the hands, then save the file. Next you will need to create two comma delimited files (*.cvs) from the File option on the "SCOREBOARD" window of DDS. The first is the scoreboard to CSV and the second is DD play to CSV.

The scoreboard mimics a lot of the data we already have in the database (players, contract, results, score, etc), but it has five important bits of information you need for your database if you are interested in the par result info"

Par Result       Your Par Score               You vs. Par                DD Bidding                 DD Play
2C S +1                         110                                  0                           We missed double            0
5D N/S =                       400                                  1                           Par was bid                       1


What happened here on the first board, EW played 2S -1, which is the double dummy predicted result. "You could have doubled and got 200 instead of one hundred, explaining the not so cryptic "we missed double".
On the second hand, you were plus one to 1 imp to par and you took one more trick than double dummy said was possible (you were in 5D making six).

For most of us, adding those five columns to the excel spreadsheet making sure the deals correspond with the hands in the database. The second csv file has the double dummy results for each position (N, S, E, W) and each possible strain (, , , , NT). It looks something like this.

Deal    N-NT    N-S    N-H    N-D    N-C    E-NT    E-S    E-H    E-D    E-C    S-NT    S-S    S-H    S-D    S-C    W-NT    W-S    W-H    W-D    W-C
1           6          5         7         4        7         6          7        5        7        4         7          6        8        5        9          6          7          5        7          4
2           8          9         7       11      11         3          4        6        1        1         8          9        7      11      11          3          4          6        1          1


Here you can see how many tricks could be taken by each player (double dummy) on the first two boards from the table at the top of post. On the first one, SOUTH could take 9 tricks in clubs (thus par ended up being 2CS+1). Note, north can only take 7 tricks in clubs. On deal two, North or South could take 11 tricks in either minor. I don't use this second *.cvs file often. IF you do use it, you wlll need to do a vertical sort because to use this information, you need to use the lookup function and the header (the N-NT, N-S, etc) have to be in alphabetical order. I will explain how to do that if anyone else is crazy enough to want all this data. The key data is already embedded in the early table (the par contract and how many trick more or less than double dummy you played in the contract you did play).

When you add the five key fields to your database. You can pull out all the slams you missed, all the -2 or -3 undertricks you took compared to double dummy. How many times you overbid and find out why, etc. Really it is all fairly easy and easy to do. You can even grab the hands (say all the slams you missed), place them in a single Lin file by cutting and pasting into notepad *you need to make one key addition to do this* and then open those hands in something like DDS or bridge composer for further examination.

I will stop posting about these tricks for now. But if anyone wants the JEC database to see how it works, or if want help setting up their own database I will be glad to help.
--Ben--

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users