Saturday, June 16, 2018

Creating a Basic GEDmatch DNA Workbook

You can copy and paste your results from GEDmatch into any spreadsheet program such as Excel or OpenSource.

I created a DNA spreadsheet workbook which contains the following: One-to-many page, Graphic Tree, Bar Chart, CSV file, Matching Segments, Triangulated Segments, and Gedcom + DNA. The One-to-many and Gedcom + DNA are on the free part of GEDmatch. You will need to join Tier 1 to access and run the rest of these fabulous tools. You will need to make a $10 donation. Joining Tier 1 for a one-month donation can save years of time figuring out who the common ancestors are you share with your matches. 

Some people view it only as a verification of their research. Discovering what's behind the walls, family secrets, adventures, and meeting often unknown living family members for me is what genetic genealogy is all about.  

Explanations of your results from Tier 1 tools can be found on several of my previous posts.

When I am done with running the tools this is what the sheets at the bottom of my workbook look like.

To copy different tool results into a workbook simply select all (Ctrl+A) right click “Copy,” open a blank spreadsheet page, right click your mouse and Paste. Or click Paste on the spreadsheet toolbar.

Sheet 1 One-to-Many

Use Ctrl-A to highlight everything, Right-click Copy. Open a spreadsheet and click Paste. A little circle spins for a while and a pop-up might tell you what you are pasting might look a little different than what you saw on the GEDmatch page. Don’t worry, you didn’t do anything wrong. Simply click “OK.” The circle will start spinning again and then you will see what you copied appear. The “One-to-Many” page might take a minute or two for the page to fully load. If you look at the scroll bar on the far right of your screen when it is all the way to the top it has finished loading the page.

Double click on the sheet at the bottom of the workbook to add a title to the page. (Untitled spreadsheet pages are labeled Sheet 1, Sheet 2, Sheet 3 and so on.)

Click to open a blank sheet for the next set of results.

I adjust the column widths on the “One-to-many” workbook page by sliding the lines between the alphabet letters at the top of the spreadsheet page right or left. The alphabet letters denote the columns in the spreadsheet. If you don't know how or are afraid of spreadsheets read my post, Easy Peasy Spreadsheets

I am so thankful I listened when Jim Bartlett told me I really needed to start using them. The learning (and for me fun) is in the doing.

After you paste your One-to-many page you will see that column “N” is an empty column. I make it wider and give it the heading MRCA (most recent common ancestor) and add notes under it. When the name of the person or grandparent couple is known I add that information.  

Be sure to click on the save icon frequently. Or you can do Ctrl+S.

On the One-to many page I study the Gen and Total cM shared information to help me determine relationships with my matches.

If I don’t know the relationship to a match I add notes of my best guess using the genetic distance and/or the results of “One-to-one comparison” totals which are found by clicking on the blue letter “A.” The One-to-one comparison gives more detailed results and the total amount shared can vary from what you see on the One-to-many page. 

Gen 1= Parent, Gen 2= grandparent, Gen 3= great-grandparent, Gen 4= 2nd great-grandparent. If I don't know the exact answer I might just note "4th great-grandparents" until I figure out exactly who it is.

One of the things I had trouble with is the fact that an MRCA (most recent common ancestor) can be a grandparent couple instead of just one person. 

Word to learn: Nibling. This is an aunt/uncle, niece/nephew relationship.

I check the total cMs shared using the free online DNA Painter cM Relationship chart that also provides odds for possible relationships you could share with your match.

Below is the DNA Painter tool I use to help me figure out the relationship with my match.

I run Tier 1 Tools and select Triangulation Groups Beta Tools for three of pages I include in my DNA Workbook. Each one goes on a separate sheet. The Beta Tools gives you Graphic Tree, Bar Chart, and the CSV file.

Sheet 2 Graphic Tree (See Triangulation Graphic Tree What the Heck Am I looking At?)

Sheet 3 Bar chart (See my previous post on Bar Charts)

Sheet 4 Matching Segments

I love studying this page. It shows which chromosome and position you match with a person BUT it can be confusing because it is two dimensional and doesn’t show triangulation so there is no way to know if the match is on your maternal or paternal line. This is another good sheet to make notes on. I insert a column just before the colored graphic by right clicking my mouse on the cell that contains the letter “J” at the top of the page and choosing “insert.” A good note to make is what their name or alias is on their testing site.

Sheet 5 Segment Triangulation

I prefer to use the middle selection when I run this tool. It shows results by Kit number, chromosome, segment start position. This is the sheet I use to sort matches on maternal and paternal sides.  (How-to in my book and a future post.)

Optional additional sheets

Sheet 7- CSV or Comma Separated Value  

Download these results if you are used to working with spreadsheets and understand sorting. You don’t need this sheet to find your genetic family. I prefer to work with Sheet-5.

Sheet 6 - Gedcom + DNA matches

Though I can’t view the trees from the downloaded spreadsheet, this is a handy reference page for getting around the new GDPR EU (General Data Protection Rules for the European Union) that has resulted in everyone who is living shown as HIDDEN HIDDEN. You can edit this sheet and use it as a reference. Replace the hidden/ hidden with the name of your match by using Ctrl-F, enter the kit number and search your One-to-many page.  Or you can enter the Gedcom file number in “User look-up” which is found on the landing page. When you use “User look-up” you may find your match handles multiple kits of others who share the same most recent common ancestor (MRCA) with you.

I insert an additional column on the Gedcom + DNA spreadsheet and add the name of our MRCA and note if this person is a maternal or paternal match or A or B match when I am working with an adopted person.  

I also like to clean up my downloaded pages by deleting the stuff at the top of the downloaded pages. You can do that by going to the numbered rows at the far left of the page. Click on the row number that contains the lines of copy you want to delete. This highlights the row. Right-click and choose Delete.

If your results are scrunched up so you can't read the page, or you see a bunch of ### where numbers should be, slide the lines between the alphabet letters that indicate column to the right. Play with your sheets to get them the way you want them. 

If you make a mistake when working with your spreadsheets use the swishy back arrow button. 

You can rearrange the position of your DNA workbook pages by clicking on any sheet heading and dragging it right or left.

Happy Finding!

Still having a blast!

No comments:

Post a Comment