UPDATE (09 NOV 2015): 23andme is retiring Countries of Ancestry, (the tool formerly known as Ancestry Finder) as of November 11, 2015. I’m leaving this blog post out here for posterity, mainly due to vanity and the amount of time I put into creating my Excel sheet. 😉

But, as mentioned in the previous update: you should be using the tools at DNAGedcom.com. Good luck to you!

UPDATE (11 July 2013): It has been more than a year since I created this tool and process, and the 23andme site has gone through some extensive changes. I have not had time to test if the process in this post nor the spreadsheet is still working properly with any possible data format changes.

Feel free to give it all a shot, but I recommend that you may first want to take a look at the DNAGedcom.com site, which has automated things in a much more user-friendly way!

If you are a 23andme customer, you may have noticed that they updated their Ancestry Finder (AF) tool to make it possible to see the matches of the people with whom you share genomes. You can also download this data in comma-separated-variable (.csv) format.

That. is. great. stuff.

I’m sure that people will soon come up with interesting new ways to triangulate their genetic matches and learn more about their ancestry. But, there is one problem: If you are sharing your results with a few hundred people and want to get all of their AF data files, it takes a drop-down selection, wait a few seconds for AF to reload, then a button click and a Save File As click FOR EACH PERSON IN YOUR LIST.

Holy carpal tunnel.

I suppose 23andme might make it possible within their system to get them all at once which will make this all moot. But, I thought there must be a better way to get all of these data files and I found a slightly clunky, yet completely workable method.

There may be more elegant ways to do this, but for now I created an Excel spreadsheet that will help you get you all the data with only a bit copying and pasting – along with some help from a Firefox add-on.

First off, some Caveats

• I am sorry to say I cannot promise I will be able to support or answer all questions on how to use this sheet. I am trying to make it as simple as possible, but for those that might be a bit technologically challenged you may feel it got ugly.
• My hope with this post is that if you just follow the step-by-step you’ll be able to get there. (Most of this info is also found in my spreadsheet.) I suppose if you find this method difficult, then there’s a good chance you don’t want to download and massage the data files anyway.
• This tool is designed to help people get the 23andme AF tool data they have legitimate access to more efficiently. 23andme’s terms and agreements obviously still apply and nothing here is supposed to work around them in any way.

In any case – Please Link Responsibly.

A bit more background and then on with the process.

When you visit the AF tool, part of the interface that loads is a drop-down box with the names of ALL the users you share with and whose results you can view. If you look at the page source, it turns out that the code for the drop down box also includes the user’s encrypted profile_id (nothing non-secure, I believe, just a way to pull up the right data.)

Using that profile_id and the code attached to the Download Results button, I was able to reconstruct the link for each profile in the list. Then I simply created a spreadsheet that pulled out the profile_id and appended it to a simple download link.

Executive Briefing

Below is a detailed, step-by-step process for the downloads. Since that might be confusing at first look, here’s an Executive Briefing on what we will be doing:
1. Logging into 23andme and going to Ancestry Finder.
2. Copying a section of the Ancestry Finder web page HTML source code and pasting it into my pre-created handy-dandy spreadsheet.
3. Saving the Excel spreadsheet as an HTML web page and opening that in Firefox.
4. Using a Firefox download manager add-on to download all the links at once.
5. Play with the data!

Step By Step

Read through this process before you attempt it – down near the bottom you will see I use a Firefox browser add-on and you will need to have it (or a similar one) installed to download the data.

First Things First – Getting Ready

Download and open the Excel spreadsheet I created.

Download and install the DownThemAll! Firefox Extension.
(You will need to restart the browser before it will work properly.)

Login to 23andme. IMPORTANT: Keep your browser open and remain logged in to 23andme during this process or the downloads will not work!

Go to the Ancestry Finder Page

Before you worry about working with the Advanced Controls: They only appear to affect the visual display and do not affect the content of the downloaded data file.

Get the Source Code

Somewhere on the WHITE SPACE of the page, RIGHT click your mouse. A browser menu will appear: LEFT click “View Page Source.” This will open a new page with the HTML code of the web page. (Note: Do not try to do this on or near the chromosome visual or you will get a different menu.) You do not need to understand the code, just follow on.

Update: I realized later that due to the way I wrote the Excel formula, there is a simpler method to this next part than selecting just a section of the HTML code. I will leave the selection process here for reference, but:

You can select the entire sheet and paste it into the Excel sheet and it will work fine! When on the HTML Source page, use the keyboard shortcut <Ctrl A> to select all of the page, then <Ctrl C> to copy it to the clipboard. (Use the <Command> key on a Mac.)

Now move down to the Paste the Code Into the Spreadsheet” section below to continue.

Scroll down the page source until you find a section that starts with:
<!– Start profile select section. –>

You can use the find function <Ctrl F> to get there quicker, but it will be about a third of the way down the page.

It should be easy to spot because following it you will see the code for the list of user profiles you can see in AF.

Use your mouse to select the text starting with the <!– Start profile select section. –> line and highlight ALL THE WAY DOWN to the end of the last line with a profile name in it. (They all start with <option value=””).

If you select more lines than that, don’t worry. I have built in some cleanup in this sheet to ignore it.

With that entire section of code selected, RIGHT Click on the highlighted text and then LEFT click on Copy.

This will copy the text to your clipboard. (For those who are used to using keyboard shortcuts, <Ctrl C> works too.)

Paste the Code Into the Spreadsheet

Return to the “Code Cleanup” tab in the spreadsheet and paste the text you copied starting in Cell A9. There is a note above that cell that says PASTE BELOW HERE. (All other cells in that sheet are protected.)

To the right of that code, you should now see a cleaner version of the User ID and Profile Name, along with a live link to each data file and the link text itself in the last two columns.

At this point you could click on each individual link to download the data files – but this is almost as cumbersome as before. So, we will use a browser add-on to automate the process.

If you have not done so already, download and install the DownThemAll! extension referenced at the start of this list.

Remember: If you just installed DownThemAll! you will have had to restart Firefox for it to work. If you need to restart Firefox now you will probably need to log back in to 23andme again and keep the browser open.

Convert the Spreadsheet to a Web Page

To use the DownThemAll! extension, you will need to save the spreadsheet as an HTML file and open it in Firefox. So: In Excel, click File… Save As then in the “Save As Type” box, select “Web Page” then click Save.

(If you get a message warning about compatibility, just click Yes.)

Now that you have the file as a simple web page of links, you can just drag it into Firefox to open it. (Preferably in a new tab.)

The web page should look just like it appears in the Excel spreadsheet. If, when you open the page you find you are looking at the reference information instead of the links, simply click the “Code Cleanup” tab at the bottom of the page.

Download the Data Files

Now, we’ll use the add-on to get the files – RIGHT Click on some white space in the page, then LEFT click on DownThemAll!

A DownThemAll! window will appear with all of the links listed. RIGHT click in the window and LEFT click “Select All”

Once all are selected, again RIGHT click in the window, then LEFT click “Check Selected Items”

Select a location to save all of the files in the bottom of the window, then click Start! To begin the process

DownThemAll! will show you a progress listing of the files being downloaded. Once completed, you can close the window and any browser tabs and Excel files if you wish.

At this point, the download folder you selected should have one .csv data file for each person in your Ancestry Finder list (including yourself.)

While that took a little bit of work, much easier than doing it manually – especially if you plan to download the data at regular intervals as more people are added to the database!