SNP tools for Microsoft Excel (last updated in Oct. 18, 2012)
Overview
SNP tools is a general add-in for Microsoft Excel to do data conversion and basic analysis for Single Nucleotide Polymorphism (SNP) data.
SNP tools enhances the ability of MS-Excel for genetic and epidemiological functions, such as the calculation of odds ratio (OR), confidence interval (CI), p-value, and power. SNP tools can convert your laboratorial data in Excel to the format of Haploview, Plink, Merlin, SNPHAP, Phase and PedPhase for the further analysis.
SNP tools for MS-Excel can be used for MS-Excel 2000-2011 in MS-Windows 2000, XP, Vista, and Windows 7. The Mac OS X version for MS-Excel 2011 is also available.
Install and uninstall SNP tools
Download: SNP tools (V1.80) for MS-Windows, SNP_tools_Mac (V1.70) for Mac OS X
Install SNP tools in MS-Windows 2000-XP and MS-Excel 2000-2003
Install SNP tools in MS-Windows Vista, Windows 7 and MS-Excel 2007 and 2010
Install SNP tools in MS-Excel for Mac 2011
Important: You might need to adjust your security level to "Medium" in the table of "Security level" of "tools- Macro-Security" in Excel, if your system refuses to accept the Add-ins. In the new update version of SNP tools, a certificate was added by a digital signature, making installation friendlier. The medium security level arrows you to decide whether enable a "Enable" button in the window of "Security Warning".
In some systems it might require users to download and register Common dialogue runtime library and RefEdit by themselves (or see following install guide)
How to install Common dialogue runtime library
How to solve problem of missing RefEdit
How to let SNP tools automatically run Haploview, Phase and SNPHAP
Usage of SNP_tools
An Example file with input data and the usage of SNP built-in functions
How to convert your own SNP data into the formats of Haploview and Plink
How to convert Affymetrix chip data into the format of Haploview and Plink
Usage of some frequent used built-in
functions:
OR =oddsratio(a,b,c,d)
where a,b,c,d are the 4 numbers to calculate odds ration in
Excel (for details see example file)
lower 95% CI =OR_CI95L(a,b,c,d)
upper 95% CI =OR_CI95U(a,b,c,d)
X2 =OR_x2(a,b,c,d)
p-value
=OR_P(a,b,c,d)
Power =OR_Power(a,b,c,d)
ORMH =strata_OR(B4:E5) (MH = Mantel Haenszel strata calculation)
X2MH
=strata_OR_x2(B4:E5)
PMH =strata_OR_P(B4:E3)
in the example data and formulas of some special built-in functions, mainly for strata data, which will input a rectangle range of cells. If you see the function is a cell like "='C:\Documents and Settings\bowang\Application Data\Microsoft\AddIns\SNP_tools.xla'!Strata_OR_CI95L(B4:E5)", please delete the prefix before '!' and set your function only the last part, i.e. "=Strata_OR_CI95L(B4:E5)", and hit ENTER key.
If you see some cells like
"#VALUE!" or "#WERT!" in the example file, you may need to refresh the
calculation by double-click the cell and hit ENTER key. Some cells may show
"######"m it means the column is not wide enough to show the long
digits, change the width in the menu Format-->Column-->Width to set a long
value to show all the cell value.
Downstream analysis software
We compiled the source code of SNPHAP for Linux in cygwin thus it can be run in DOS/MS-Windows. Here is the SNPHAP for DOS/MS-Windows and the cygwin dll.
How to download genotype file from HAPMAP and convert into Haploview formats.
How to download HapMap (HapMart 0.7) and convert into Haploview format by SNP tools
Citation
Chen, B., et al., SNP_tools: A compact tool package for analysis and conversion of genotype data for MS-Excel. BMC Res Notes, 2009. 2(1): p. 214.
Questions and Help Requests
SNP tools was mainly developed in Windows XP (Windows 2000) + MS-Excel 2003. It works well in Windows 7 and MS-Excel 2007 now. If any problems running in special platform, please feel free to let us know: c.bowang@dkfz.de or snptools@gmail.com.
