Monday, August 8, 2016

Your DNA Cousin Match Database: Getting Started with our Excel Template

A reader from a recent post requested a template for setting up a DNA matching database. Because it's quick and easy to set one up in Excel, I threw one together quickly. Please feel free to reuse for non-commercial purposes, but not to repost.

When you open this file, it's going to be a View Only file in my Google Drive. In order to use it yourself, you need to download it. The link to do so is on the top right corner, next to the Print option. You'll then be free to use it in Excel, or with whatever other spreadsheet software you want to use that is compatible with Microsoft Excel.

I've got the Filters already set up and enabled, which means you'll be able to sort and filter information when you analyze the data. In order to see those matches with the greatest similarity/longest segments, sort your Segment Length column from largest to smallest. If you want to see the other matches that might also connect to a given cousin on the same chromosome, sort the Starting or Ending Point from largest to smallest. Reading the ranges between these two numbers, determine where there may be overlap. Use the matching utilities from your testing company or analysis website of choice to determine whether a match exists. Record your findings in your Notes section. If you're able to determine who the common ancestor(s) are that you share with a match, record that information in the Most Recent Common Ancestors (MRCA) column.

If you decide you want to move different columns around, or add more columns, I recommend turning off the Filters (highlight headers, Data > Filter button toggled off), changing things around, then turning the filters back on.

If you use the table in Excel, I've frozen the column headers, so they continue to show up as you scroll down through the data. To enable or disable this option, highlight the headers, go to View > Freeze Panes > Unfreeze Columns, Freeze Top Row, or Freeze First Column, according to what your preferences are. I don't know if this option works in Google Sheets or not, or in any other spreadsheet software. So bear that in mind.

However, now that I'm seeing this open in Google Sheets, I think anyone who decides to use this may want to consider using it there. Not only will this allow you to access your spreadsheet from multiple devices at once, the layout of the file itself is much nice in Sheets than in Excel. Because of the way Excel scrolls the tabs across the bottom of the screen, you have to click through a series of tabs over and over again to get from Chromosome 1 to Chromosome 22. In Google Sheets, however, all of the tabs display across the bottom without any of them being hidden. This may not be an issue, depending on the size of your monitor or display resolution. But definitely check around to see what you like/makes your life easier!

Let us know how it works out for you in the comments, and be sure to like and subscribe to our Genetic Genealogy series on Youtube!

Wednesday, June 8, 2016

Giving Back: Indexing & Transcription Opportunities for Genealogists

So, who wants more free genealogy records online?

Who wants to find them, transcribe them, build the database to host them, and pay to maintain them?

I think we're all a little guilty of this. Whether it's because we simply don't know about all of the opportunities available, or we think we don't have the necessary skills required, or we're just feeling too lazy/busy/set in our ways to help. We've all made the excuses. But there's no time like the present to jump in and lend a helping hand!

Records are unsearchable, and therefore invisible, until they are transcribed, tagged, and indexed. If we want things to be free and searchable, we need to be part of the cost cutting measures. And the repositories who are already taking on the bulk of this free access burden need our help with the most time consuming part. It's the single greatest contribution we can make to a record collection. Why wouldn't we share the skills we've accumulated as genealogists to help institutions across the globe to provide better records access to all of us? If we aren't part of the solution, we're part of the problem.

Check your favorite repositories--local, state, regional, and national--to see what they need from you. If you come across, sponsor, or need volunteers for any transcribing projects, add them in comments!

These are the ones I've come across so far just through Google searching, my own research, and reaching out on social media.

International/National Projects:

State (US):

There are certainly more projects available out there than just these. So please, let us know when you find them. 

The research you help by giving back may just be your own!

Saturday, April 23, 2016

Setting Up a DNA Cousin Match Database: A Success Story

DNA testing for genealogy is one of the best investments I've ever made into my research. The more I invest into my understanding of the subject, the greater returns I receive from it. And at no point was that ever more apparent than when I connected with a distant cousin several months ago.

Photo of Charles Miller Doyle and Birdie Price
from the collection of Irene Doyle Ashley,
 ca. 1920-1950; Scanned by Dwight Edwards,
Alameda California, 2016.
I initially reached out to this cousin more than a year ago, at a point when I was just beginning to figure out what I was doing with DNA analysis. I'd written dozens of such emails, and finally clued into something important. Reaching out to a DNA cousin match is good. Offering to help them determine the connection you share is better. But being able to share a real theory about where you think that connection is--this is the best approach, the essential component to every email we write to DNA cousin matches.

Had I not mentioned to this cousin that I was a Doyle descendant, and through looking around at shared matches I suspected he was too, he might never have written me back. He might have never taken the time to answer the vague form email I'd gotten into the habit of sending. And that would be truly tragic, because without this connection I never would have seen the pictures he shared with me of my 2x great grandparents.

Even though I made this connection on AncestryDNA, the real potential of this connection is untapped at, where I can analyze the DNA segments in greater detail. But inviting him to use GEDmatch and performing the analysis of our DNA segments were only the beginning. Having a way to compare our match to hundreds of other matches, in detail, across various other testing websites is the necessary next step.

By setting up a DNA database, harnessing the powerhouse of DNA testing for genetic genealogy becomes a reality. And in my newest tutorial, I explain how to set up such a database in spreadsheet software you already use. Whether you use Microsoft Excel or Access, Google Sheets, or any other type of spreadsheet software, many of these tips I share will help you to get started with your DNA cousin match analysis.

In many respects, setting up a centralized database of DNA matches isn't a question of starting over. It's learning how to be more organized int he efforts you're already making, in order to obtain the results you want, and solve the mysteries you're trying to unravel through DNA.

[ UPDATE: Be sure to check out our free Excel template for setting up your own DNA database, based on this presentation! ]