Adapting the Sortable Spreadsheet and Using Online Utilities

[Most of the content on this page dates back to about 2014. Some of the utilities discussed on this page may have been updated since that time, and some of the instructions posted on this page may not be applicable to the updated utilities.]

Sortable Spreadsheet

The R1a-Y2619 Ashkenazi Levite sortable spreadsheet may be adapted for other groups of Y-DNA results without much difficulty. To do so, you must: (1) replace the Ashkenazi Levite Min, Max, and Mode with the Min, Max, and Mode for the cluster of interest, preferably taken from the Family Tree DNA project page with the largest number of test results for the group of interest; (2) replace the test results for R1a-Y2619 Ashkenazi Levites with the test results for the group of interest (which will require placing into separate cells those marker values reported by Family Tree DNA as multiple numbers separated by hyphens); (3) use conditional formatting to color code each column (or color coding each cell manually, if the number of test results included are limited); (4) change the numbers in the “Number of Tested Markers” row (immediately below the individual test results) by fixing the numbers for the first and last cell in each column; and (5) change the numbers in what is now the “At Ashkenazi Levite Mode” row (below the individual test results) by fixing the numbers for the first and last cell in each column.

You may locate test results on the Family Tree DNA project spreadsheets of interest, using the ancestral information provided on your Family Tree Y-DNA matches to identify other test results.

Note that you may need to adapt the spreadsheet depending upon the number of values on DYS464 that the men in your group have; the sortable spreadsheet is set up to accommodate six values on DYS464, although almost all R1a-Y2619 Ashkenazi Levites have only four values on DYS464.

To color code the cells using conditional formatting, you may: (1) highlight all of the entries in the column to be color coded; (2) click “Conditional Formatting”; (3) click “Color Scales”; (4) click “More Rules”; (5) change “Format Style” to “3-color scale”; (6) change “Type” under Minimum, Midpoint, and Maximum to “Number”; (7) set “Value” at four less than the mode for that marker, the mode for that marker, and the value for that marker, respectively; (8) change “Color” under Minimum, Midpoint, and Maximum to blue, white, and red, respectively; and (9) click “OK.”

Consent is hereby given to the non-commercial use of the format of the sortable spreadsheet; if you use the format, please credit and cite this website.

Y-Utility Spreadsheet

The Y-Utility function may be found at http://www.mymcgee.com/tools/yutility111.html. To analyze 111-marker results, you should use the revised Y-Utility function at http://tinyurl.com/McGee111ModBurgarella2010Rates)

If you want to prepare a Y-Utility spreadsheet for a group of men, you should find the test results for the group of interest and place into separate cells those marker values reported by Family Tree DNA as multiple numbers separated by dashes. (Note that the Y-Utility spreadsheet will accept only four values on DYS464; you may need to adjust your data accordingly.) You will need to include some sort of identification – kit number and/or name – in the left-hand column; you’ll want to keep the description short for purposes of legibility.

In order to be able to use the Y-Utility spreadsheet to sort the sortable spreadsheet based upon distance to an MRCA, you will need to ensure that the rows on the Y-Utility spreadsheet are in the same order as the rows on the sortable spreadsheet.

Y-Utility offers a large number of options, including, inter alia, probabilities (50%, 95%, or a custom number (defaulted to 75%)), mutation rates (I’ve been using the FTDNA mutation rates), and years/generations. After (or before) you have chosen these options, you should paste the identification and marker values into the box under the legend “Paste haplotype or setup data here.” Clicking the “Execute” button will yield the test results.

You can cut and paste the Y-Utility results into an Excel spreadsheet; you will likely find it preferable to paste each Y-Utility spreadsheet into a different page of an Excel spreadsheet, as I have done. You will need to adjust column widths.

Janzen Spreadsheets

A Janzen spreadsheet may easily be adapted for other groups of Y-DNA results. The spreadsheet may be downloaded at http://www.timjanzen.com/dna.html. Alternatively, you may use a Janzen spreadsheet posted on this website if you delete the text; you should not delete the rows of text.

If you want to prepare a Janzen spreadsheet for a subset of R1a-Y2619 Ashkenazi Levites, you can cut and paste from the second page included in the R1a-Y2619 sortable spreadsheet (“67 Markers for Other Utilities”) or the third page included in that spreadsheet (“111 Markers for Other Utilities”). You will want to cut beginning at Column C (the first marker, DYS393) and ending at Column BR or Column DJ (the kit number, date, and ancestor information), respectively.

If you want to prepare a Janzen spreadsheet for a different group of men, you should find the test results for the group of interest and place into separate cells those marker values reported by Family Tree DNA as multiple numbers separated by dashes. You do not need to include kit numbers or names, but including that information may be helpful in ensuring that you have included the marker values that you want to include (but have done so only once).

Fitch, Kitsch, and Neighbor Phylogenetic Trees

The PHYLIP program may be downloaded at http://evolution.genetics.washington.edu/phylip.html.

You paste the PHYLIP data so generated into a .txt file named infile, in the Programs subfolder of the PHYLIP folder that you have downloaded to your computer. Next, you open the Fitch, Kitsch, or Neighbor application in the Programs subfolder. When prompted for the file name, type “infile.txt” (the program will not find the file without the extension). Fitch, Kitsch, or Neighbor will then generate a phylogenetic tree; the process can take hours or days, depending upon the number of men whose results are included and the speed of your computer.

When you have the results, you’ll want to paste them into a Word file (or, if the results will not fit on a single page, into a .txt file). You’ll need to change the orientation from portrait to landscape, and you’ll probably need to expand the horizontal length of the page and, perhaps, to adjust the font size.

Preparing Star Trees

Phylogenetic Network Software from Fluxus Technology Ltd. may be downloaded at http://www.fluxus-engineering.com/sharenet.htm.

In order to prepare a star tree using the Fluxus software, you use the Y-Utility function, in the manner discussed above, checking the box “Generate Fluxus phylogenetic network .ych data.” (If you’re interested in a subgroup of men, you may want to include the results for only those men.)

You paste the Fluxus data so generated into a .txt file, then close the file. Right click on the file's name and change the file extension to .ych, then save the file (as a .ych file).

To generate a tree using the Star Contraction function, follow the instructions below:

Open the Network application file downloaded from the Fluxus website. On the pulldown menu on the top of the page, click on "Calculate Network," click on "Optional Pre-Processing," then click on "Star Contraction."

On the "Star Contraction" page, click on "File," which opens a box for the file to be contracted. Click on the arrow at the right side of the "Files of type" box and choose "Y-chromosomal data file (*.ych)." Find your file and click on it. (If prompted, allow the program to shorten the identification of tested men.)

On the "Star Contraction" page, click on "Parameters" in the pulldown menu and change "Maximum star radius" to "10." Click the "Set" box.

On the "Star Contraction" page, click "Calculation" at the top of the page and save the file as a .pro file. (If prompted, allow the program to continue contraction of the taxa.) Save the file as an .sco file. Click "Exit" at the top of the "Star Contraction" page.

On the pulldown menu on the top of the main Network page, click "Calculate Network," click on "Network Calculations," then click on "Median Joining." Click on "File," then "Open." Click on the arrow at the right side of the "Files of type" box and choose "Star contraction output file (*.sco)." Find your file and click on it.

On the pulldown menu, you can click on "Parameters" and select from a menu of options for changing your calculations.

On the pulldown menu, click on "Calculate Network." You will be given the option of saving your file as "Network Output Files (*.out)." Do so.

To generate a tree without using the Star Contraction function, follow the instructions below:

On the pulldown menu on the top of the main Network page, click "Calculate Network," click on "Network Calculations," then click on "Median Joining." Click on "File," then "Open." Click on the arrow at the right side of the "Files of type" box and find your .ych file, then click on it. (If prompted, allow the program to shorten the identification of tested men.)

On the pulldown menu, you can click on "Parameters" and select from a menu of options for changing your calculations.

On the pulldown menu, click on "Calculate Network." You will be given the option of saving your file as "Network Output Files (*.out)." Do so.

The following instructions apply whether or not you use the Star Contraction function.

On the pulldown menu, click on "Draw Network." Click on "File" on the pulldown menu, which opens a box for the file to be used to draw the network. Click on the arrow at the right side of the "Files of type" box and choose "MJ or RM out files (*.out)." Find your file and click on it.

The program will draw the network. (You may need to respond to prompts.) Click the "Finalise" button. When the network is drawn, you will have various options as to whether, among other things, to display mutated positions, mode names, median vectors, and median vector names. (Note that you can click on a node and see all men who are that node.) Click "File" and "Save," which will allow you to save the file as an .fdi file.

Posting Materials on the Internet

Google Drive allows you to easily put materials on to a web page so that you may share a link to that page. Google Sites allows you to easily create, revise, and maintain a website containing your materials.