Sunday, November 27, 2016

What is AncestryDNA Helper

Those of you who have read this blog for a while and have done a DNA test at AncestryDNA are probably wondering what AncestryDNA Helper is and if you should be using it.

Maybe. It's not for the faint of heart.

If you are comfortable working with spreadsheets, do look into it. If you have more than one DNA test that you manage at Ancestry, it definitely holds potential for you. If you are willing and able to dig deep into the data and follow advice from the online group and from me, then go for it.

The AncestryDNA Helper is an extension for the Chrome browser. When I first installed it, it had fairly major problems, so I uninstalled it. It came up on a blog I follow earlier in 2016 and so I tried it again. It still has problems, but with nine DNA tests in my account, I decided to work with it since I have the technical ability to deal with the data.

The Helper will scan all your matches and all the ancestors that you can are able to view in their trees. It puts all that information into a database on your computer. The data holds a lot of power and potential, but getting to it and using it is where you may become frustrated.

Assuming you want to check it out, install the Chrome browser if you have not done so. Then add the AncestryDNA Helper extension from the Chrome Store.

Once installed, when you bring up the DNA test results, you will see new buttons. Your main page will add the following section near the top. Before you can do anything with the data, it has to be scanned into the database. Expect to spend about an hour for every 20 pages of matches.

There is documentation at Before you do anything, do go read that page and download and read the PDF that is linked near the bottom of that page.

If you decide to explore this tool, be sure to join the Yahoo Group where the discussion of issues and solutions takes place. It's a moderated list and many of my messages seem to get dropped, probably due to ending up in the moderator's spam folder. If it happens to you, don't feel alone.

Let me know if you join the journey and have questions. I'll do my best to help.

Friday, November 25, 2016

Download Matches for AncestryDNA Helper

All technical content can now be found on a new blog named SQLite for AncestryDNA Helper. I'm returning this blog to its original purpose.

Download Ancestors of Matches for AncestryDNA Helper Database

All technical content can now be found on a new blog named SQLite for AncestryDNA Helper. I'm returning this blog to its original purpose.

How Many AncestryDNA Matches do I Have?

All technical content can now be found on a new blog named SQLite for AncestryDNA Helper. I'm returning this blog to its original purpose.

Thursday, November 24, 2016

SQLite Queries for AncestryDNA Helper

All technical content can now be found on a new blog named SQLite for AncestryDNA Helper.

The previous post showed how to install and use the SQLite Manager plug-in for Firefox to access the AncestryDNA Helper database. Now that you've installed the plug-in, here are a couple of queries to use.

The first step is to look at the table named tests. Use the Browse & Search tab to look at the information. You need to know the exact spelling of each of your tests. I have four test kits in this particular database and for this post, I'll use only my kit as an example.

Now that I have the exact spelling for the kit name, let's do some data mining.

Click into the Execute SQL tab. There will be a starter SQL statement in the SQL box. Just clear it out.

Counting Surnames

In this first example, I'm counting all the A surnames in the ancestors of my matches, sorting them with the largest number at the top and exporting them to a CSV that can be opened in Excel.

Paste the SQL into the box. It doesn't need to all be showing. Click on Run SQL and wait for the result.

Using the Actions button you can then view the result (as shown) in a table format or in the raw CSV format. When you are happy with the result, you can save the result in CSV format to a file so that you can work with it further in Excel or your favorite spreadsheet program.

All other queries are run in the exact same way.

Here is the SQL query:

-- Match ancestor count by surname, one letter of the alphabet
-- Column headings: "Kit","Match Surname","Number of Ancestors"
SELECT as "Kit",
    UPPER(surname) as "Match Surname",
    count(*) as "Number of Ancestors"
FROM ancestors
    join tests on ancestors.testid =
where "Elizabeth L Richards"
   and surname like "A%"
  and matchid not in
    (select id from tests)
group by,
order by    count(*) desc,
    UPPER(surname) asc

Notice the colored and bolded text. Replace my test name (in red) with your test name. The letter A can be any letter of the alphabet. If you remove the entire black bolded line, every surname will be counted instead of just one letter. The brown section prevents my other three tests from being included in the counts. That section can be removed if you want to count ancestors from other kits you have in your database.

Mining One Surname

Having chosen the Alexander surname to research further, I want to dig into those ancestors of matches. This query will return all the ancestors except those of my immediate family.

Here is the SQL query:

-- Match ancestors for one kit and one surname
--Column headings: "Kit","Match Surname","Match Full Name", "Born","Died","Metaphone","Predicted Relationship","Confidence","Hint","Private","cM","Segments","Match Admin","Match Name","Note","Ethnic Regions","Ethnic Trace Regions"

SELECT as "Kit",
    UPPER(surname) as "Match Surname",
    fullname "Match Full Name",
    IFNULL(born,"") as "Born",
    IFNULL(died,"") as "Died",
    metaphone as "Metaphone",
    range as "Predicted Relationship",
    confidence as "Confidence",
    case hint when 1 then "Yes" else "" end as "Hint",
    case private when 1 then "Yes" else "" end as "Private",
    IFNULL(centimorgans,"") as "cM",
    IFNULL(segments,"") as "Segments",
    matches.admin as "Match Admin", as "Match Name",
    note as "Note",
    ethnicregions as "Ethnic Regions",
    ethnictraceregions as "Ethnic Trace Regions"
FROM ancestors
    join tests on ancestors.testid =
    join matches on ancestors.testid = matches.testid and ancestors.matchid = matches.matchid
where "Elizabeth L Richards"
   and UPPER(surname) = UPPER("Alexander")
   and range not like "%mmediate family%"
order by     UPPER(surname),

Notice the red and bolded text. Replace my name with your test name. Replace the surname Alexander with any surname. The bolded line causes ancestors of immediate and close family to be ignored. If you remove the entire bolded line, the ancestors of those immediate family members will be included in the list.

There are many more queries that can (and should) be constructed. These two are intended as a beginning to assist with the current problems with downloading ancestors.

Wednesday, November 23, 2016

SQLite Manager for Firefox for AncestryDNA Helper

All technical content can now be found on a new blog named SQLite for AncestryDNA Helper.

This is a special post that is off my usual ramblings. It is directed specifically to users of the Chrome extension called AncestryDNA Helper. If you don't use it, just ignore this post and any others on this topic.

The database that is built by the AncestryDNA Helper is a SQLite database. One of the group members has suggested a tool to help dig into the data that is in that database. The tool is a plug-in for Firefox and is named SQLite Manager. If you don't have Firefox, you'll need to install it first.

To add the plug-in to Firefox, use your favorite search engine to locate it on the Mozilla website. Once you find it, click on the green bar that says Add to Firefox. You'll then need to close Firefox and start it again.

After SQLite Manager is loaded, you'll want to add it to your menu. Click the menu icon on the far right and choose Customize at the bottom of the panel.

You can add SQLite Manager to the menu in two ways. You can drag the icon to the menu bar. For example, I dragged  it next to the fox in my menu bar. You can also choose to show the menu toolbar, which will already have the plug-in listed.

Here is the result of choosing both options. I can now start SQLite Manager from either place.

When SQLite Manager starts, it needs a database to work with. You will want to protect the original AncestryDNA Helper database, so step carefully through making a working copy. You'll want to do this each time your database changes.

Choose from the menu Database then Connect Database.

You will need to navigate very deeply into the Chrome structure to find the database. The AncestryDNA Helper documentation has a good description of how to find it. My username on my PC is Beth so my path to the database is this:
C:\Users\Beth\AppData\Local\Google\Chrome\User Data\Default\databases\chrome-extension_hjflmfphflaeehhpdiggobllgffelfee_0

The database does not have any file type associated with it. At the bottom right of the selection window, change the file type to All Files.

The file name will just be a number. It will be the number 1 unless you have uninstalled and reinstalled the Helper. The number bumps up by 1 each time you do so. My number is 12, so I choose the file 12 and click Open.

When the database opens, the filename (12) is at the top of the screen on the left side and the path in use is at the top of the window. When working with this tool, you'll be able to see at a glance if you are using the original or a copy.

 First thing: make that copy. Choose from the menu Database then Copy Database.

Give the copy a name you will recognize.

Choose a folder that is comfortable for you. I chose My Documents. Click on Select Folder and the copy will be saved.

Notice how the path at the top and the file name on the left now show the location of the copy that I'll be working with.

In another post, I'll share some SQL queries that you can use. You can use Browse & Search to look at data, though I am finding one million ancestors for the matches of four tests rather a lot of data. Queries are a far better option.

When you're done and ready to close, choose the menu option Database and then Close Database.

Exit the plug-in either from the X in the right hand corner or use the menu choices Database and Exit.

Next time: some SQL queries.

Friday, November 18, 2016

Getting Wild with Mary and Her FAN Club

When last searching for Mary, we considered that facts such as a birth date could be misstated or misinterpreted. But what about her name? By limiting a search to exact spellings, it is possible that the creative spellings used in various records can be missed. It's important to use every trick in your toolbox to find those hidden records.

One extremely useful tool is the wildcard search. Both Ancestry and Family Search allow wildcard searches and many other sites do also. Be sure to read the help pages for wildcard searching and also review them every once in a while, as the rules change over time.

There are two wildcards you can use and there is one big "gotcha".

First the "gotcha". Both sites require three letters in addition to one or more wildcards. If you are searching for a short name such as Lou Day, then wildcards are not going to be of any help at all. There are a few other rules that you'll want to know, so do read the help pages.

For an example of the two wildcards, let's look at Mary Maddox Neff and her sister-in-law, a member of Mary's FAN club. The wildcard that is used makes a huge difference in these searches.
  • Mar? Madd?x
  • Mar* Ta*m*g*


The Question Mark

I'm not a fan of the question mark (?). It represents exactly one letter. So the first search will find Mary Maddox or Marg. Maddux, but not Maria Madox. There are so many creative spellings for names that limiting the matching to one exact letter seems wasteful to me, though you may find it useful in some situations.


The Asterisk

The second search is far more powerful, as the asterisk (*) represents zero, one or several letters. Searching for Mary's sister-in-law is tricky, as the surname is spelled several different ways, as is her first name, which is Mary on her tombstone and Maria on the marriage record. The family name is spelled as:
  • Tammage on the tombstones
  • Tallmadge in the 1850 census
  • Talmadge in 1860
  • Talmage in 1870
  • Tammadge on the 1853 marriage record of Maria and David Maddox
You can see how all these variations would match to a search of Mar* Ta*m*g*.

Here are examples of the many varied results from Ancestry and Family Search. You'll notice that some results, such as the first one, match the wildcards, but don't match the name you want. That's expected and you'll have to just ignore them. It's a case of seeing too many results rather than too few.

Ancestry Results

Family Search Results

Be sure to try out wildcards for yourself to look for those elusive ancestors.

Friday, November 4, 2016

Did My Grandparents Cheer for the Chicago Cubs?

One of my co-workers asked how an Arizona native living in the American South had become a fan of the Chicago Cubs. Of course, it's all tied to family.

Thinking about my family's Chicago roots led me to the realization that sports affiliations are not a part of my family history knowledge. I failed to ask about sports when I interviewed an elderly cousin some 16 years ago. Now he's gone -- the last of a generation who knew my grandparents and great-grandparents through the eyes of a teenager.

It's hard to think of all the questions we should ask of our family, so this is just a little reminder to ask about sports if it's something you'd like to know.

Reminiscing about our Chicago history, I realized that the 1907 and 1908 World Series wins by the Cubs would have been a part of the fabric of the lives of my grandparents and their families. They lived in neighborhoods on the north side of Chicago, a few miles north of Wrigley Field. In their championship seasons, the Cubs played at the West Side Grounds, which was further south, but they were still the hometown team.

My grandmother, Ruth McFarlane, was a girl of nine in 1907. Her father had been badly hurt in a work accident on the Chicago street car system and the family struggled through financial difficulties. Her schoolmates and church friends would no doubt have been excited about the accomplishments of the home team and perhaps she and her younger sister were also. Did her parents have the luxury of enjoying the wins for a brief moment? By 1910, Ruth had dropped out of school and gone to work to contribute to the family income. I imagine that baseball was, for her, a background thought of no consequence.

My grandfather, Oliver Ekstrom, was four years old in 1907, and lived a life of financial comfort in the Andersonville Swedish-American community. He was the youngest of five living siblings and would have shared the excitement of his siblings and friends when the team brought home the championships. Were his immigrant parents also caught up in the excitement? Did they rejoice at the wins?

Nine years later the Cubs moved to what is now Wrigley Field. I'd like to think that Oliver was able to attend some of the games with his brothers or with his brother-in-law, a Chicago policeman. After Oliver and Ruth married, they went to Central America as missionaries, weakening the strong Chicago ties. With their early deaths, their children grew up in boarding schools in other places.

By the time the Cubs suffered the Curse of the Billy Goat and lost the World Series in 1945, Ruth and her parents were dead, as were Oliver, his father and two siblings. Only Ruth's sister and Oliver's mother and two siblings remained in Chicago, along with three of Oliver's nieces and nephews. That extended family still has members who live in the Chicago area; however, Oliver's children scattered, with three brothers eventually landing in Arizona.

Regardless of Oliver's connection to the Cubs, many of his descendants have embraced the Cubs and have enjoyed visits to the historic ballpark. I've been to Wrigley Field only once, though I've seen the Cubs play at other ballparks. I celebrate their 2016 World Series win as I reflect on the history of my family and our Chicago roots.

Quick drop page and elements from Digital Scrapper's Liberty (2009) and  Sports Mad (2012) kits