Using Speadsheets for Genealogy

INTRODUCTION

This post is about how I have used spreadsheets for genealogy. 

What I have used

There are several applications that may be used to create and manipulate data in spreadsheet format. I have used Excel, Open Office Calc and Google Sheets to create, view and manipulate the information. The actual data can be moved between the programs and I have also imported files in to Custodian. 

Why I use spreadsheets

Like many family historians and especially One Namers I find myself dealing with large amounts of data and indexes or transcriptions as finding aids for original records. Particularly with common surnames there may be several individuals with same name and birth year in a single place. 

How do you analyse the information to work out who is your man or woman you are looking for?

Spreadsheets also help organise information so that it is easy to retrieve. My Custodian program is just an organised group of spreadsheets with the fields already named. 

How I use or have used spreadsheets.

I started using spreadsheets when I found I needed to use a computer to collate the information I was gathering. I have files dating back to 2004, when I was collecting data from the 1901 census, using it to piece together family groups and ensure I had not missed individuals. 

Before the likes of Ancestry and Find My Past had easy to search databases the FreeBMD website was a site I used a lot. This is the result of a search I did 15 years ago  in text format and as a spreadsheet. 

Whilst both formats contain the same information it is much easier to sort and identify those of interest in the spreadsheet format. This allowed me to ensure I had the right individual and by cross referencing with census records I could order certificates for the correct person(s).

With the advent of a new GRO Online Index which includes mother's maiden name for births 1837-1916 and age at death 1837-1957 it has become much easier to ensure that the correct birth or death certificate is ordered and I have even managed to track down children who missed the census. 

The index of marriages did not provide the surname of the spouse until 1912 and no new index has been made available so FreeBMD is still a useful starting point when looking for the surname of the wife. If it was a second marriage the name is not the maiden name but surname at marriage.

As I mentioned above it is useful to be able to sort information to make it easier to analyse. You can also hide columns and rows in spreadsheets. This can be extremely useful as you can have numerous columns or rows of data and hide or reveal so only those you need are showing. 

This makes scrolling much easier particularly if a large amount of data is recorded. 

In my example I have hidden a column on each sheet, locked the headings so that they show when scrolling and I have previously sorted the information so that each event now has its own page and is in date order.

This shows a partial screen shot of the results of a search at FreeBMD completed 27 Aug 2017

The image above illustrates the format of the search result on the website not easy to work with, but fortunately the information can be downloaded, and as I have illustrated, can be organised and assessed. 

The programs that we use as genealogists, which we call family history software, are databases which could be built around a spreadsheet with the ability to link in to images, video, sound and text files held within the program or elsewhere. My program Family Historian has plugins which enable you to build a tree from a csv file . I have yet to try this with my own data but I think this may be useful when I can get the chance to play with it. I will write a further post to update where I am with this. At the moment this option may have limited capability.

I am sure there are other ways I have used a spreadsheet format and I will write an update at a later date when I remember.

I would love to hear more about how others use or have used spreadsheets. I am no expert and am happy to learn from others.

Compiling Tools

What are Compiling Tools?

Tools that I use which help me to gather together what I have collected.

Evernote can be used to collect together groups of information as can One Note.

Custodian 4 may also be classed as a Compilation Tool.

But what do I want to do with the information I have gathered and transcribed?

How do I use the ingredients to make the final product?

Before I did the research did I have a clear question?

What items have I found to help answer my question?

How have I recorded the results of my research?

If I have set up a good research log I should have my results recorded. Assuming I am using the Genealogical Proof Standard I should have carried out a reasonably exhaustive search and should have information which I need to analyse.

What would I use to bring together this information?

The database collections mentioned above will contain the information but they will not help me to discover whether my reasonably exhaustive search has provided an answer. 

The simplest compilation tool has to be a pen and paper handwritten or a typed text document such as Word, Google Doc or Open Office Document. 

This allows you to record your findings from each of the sources you have used. 

If you have been using a digital research log all that may be required is to cut and paste the results. 

This method also allows for any analysis and conclusion to be added so that the complete file may be appended to the answer for the research question.

Some genealogists use blogs to discuss their findings. In which case the url for the post could be used.

For the more complicated research questions using analysis tools such as Evidentia can aid the compilation of information during research so that nothing is missed.

Setting up the right system before you start to research so that you record what you find once and let the technology work for you. 

Compiling your findings is an important step as it can quickly indicate whether the research you have done answers your question it can also highlight any conflicts.

Sometimes a table or other more visual way of displaying results may be needed such as a map or drawing. These can all be included within most text documents. Whatever works to help with the analysis of the information.

Below is an example of a simple way of compiling your results so that you can build a conclusion for a research question.

Compilation of Research Results

New Year Resolution

In 2016 I hope to finally get my Genealogy Do Over started.

During 2015 I barely got off the starting blocks.

This year before I enter anything else into the new project I started, which at present contains 5 individuals, the documents & photographs need to be organized.

On this blog I plan to discuss how I am using software to organize my digital records this software may be specifically developed for genealogy or just a tool that I am using.

For further discussion please see my other blog where I will discuss any other organization topics.

Also see posts in the DearMYRTLE Genealogy Community and Facebook group.

I would also recommend following the GenTools Study Group which started in December 2015. 

How I deal with organizing my genealogy files

Why reorganize?

Some 2 or more years ago I decided that I really needed to get my genealogy files organized.

I had things on my computer in several places, I had paper copies and printouts and hand written notes. The whole thing was a mess.

If I was not going to duplicate my research I need to get things straight. I did not want to lose anything and only wanted to keep the original file or as close to the original as I could get.

I also wanted a system that I could translate to those original paper document that I want to keep and a consistent system seemed the answer.

What I did

I decided to adopt The FamilyRoots Organizer 

Color-Coding System for my paper files and wanted something similar for my digital files.

I also set up a simple card index to store the basic information with subscript numbering to track to the sources recorded in my family history software.

I initially went for a different system for my digital files but the consistency of keeping the same system will help when I am trying to find specific items.

Two tools I have used to help with this process have been Folder Marker and Bulk Rename Utility. There is also a duplicate finder tool but as this searches all the files and the free version is limited I have not used this recently.

I have not finished with the reorganization and I am picking up hints and tips as I go along. 
I just want a consistent way of keeping the information so that I can find it when needed.

Dealing with the "FAN CLUB"

How do you record unrelated individuals?

We all encounter this situation, but do we record these unrelated individuals, and if so, how do we record them and their contribution, to the stories of our family.

Those employers or fellow servants in the household, or the lodger, the census often has others associated with our family recorded in the household or nearby. If we ignore them we may find that they become a member of the family so to do so would weaken our record.

Do all family history software programs allow for the entry of others associated with a record?
If you are doing a one name or place study, the ability to record unrelated but associated individuals is imperative, if you are to build a true picture of the relationships of those concerned, to either each other or the place.


Family Historian allows media to be attached to individuals, families, sources and notes and also allows for unrelated individuals to be connected as an associated person. This can be useful as you can record their relationship to your relative and note more about their connection.

The above screenshot shows a query that I created in Family Historian it lists all individuals (excluding the living) who I have in my file with an associated person and the relationship of that person to the individual. Most of them are relations but 1 is the coroner and one is listed as witness. This type of query and the use of associated person field can help you record individuals who impacted on the lives of your family so that they are not overlooked when looking at research strategies.
I have not been using this field to its full potential and it is an area of the program that I need to explore more.

More about what I want to achieve

I have started my tree from scratch as I know some of my early work was poorly documented and I want to add proof statements or arguments and good source citations so that I leave a legacy that someone else can use and build upon.

What do I need?

Before I can get software to work for me how I want I need to look at what I need it to do for me.

This is a list of what I think it needs to do, you may have other ideas.

1. Record names
2. Record dates
3. Record places
4. Record relationships
5. Attach digital pictures and documents
6. Attach sources and/or proof statements or arguments
7. Enable me to define attributes and events
8. Enable me to view the family relationships

What are the capabilities of the programs

Family Historian allows me to view information in records windows which are built much like spreadsheets and charts which show a pictorial representation. It allows me to link to digital media and specify which part of the image contains the relevant information. This can help with group photographs and census pages as you still keep the full image but the section you specify links to the correct individual.

Custodian 4 is a program built to use spreadsheet format it holds records by the type of record but each entry can be linked to individuals and the database can be interrogated. It will build a rudimentary tree but is usually used as a repository for recording details from documents. I am still exploring its capabilities and would welcome comments from more experienced users.

Evidentia is not a tree building program. It allows researchers to analyse their sources to build their proof for the conclusions that they record in programs like Family Historian, Family Tree Maker, RootsMagic and Legacy Family Tree.

Whilst the more traditional software programs allow notes to be attached, these programs do not offer the same analysis capabilities that are seen in Evidentia. 
That ability to pull together the information from various sources to assemble a coherent proof document is why researchers are starting to use this program in conjunction with their existing more traditional tree building programs.

How can I get these programs to work in conjunction with each other?

Unless I am mistaken the answer for Custodian 4 and Family Historian must lie in the ability to transform information held as a spreadsheet or GEDCOM file. Family Historian uses GEDCOM as native and Custodian 4 has a GEDCOM capability which I need to explore.

I also need to explore the full capability of Evidentia to integrate with Family Historian.

This is going to be an ongoing journey to see how these programs can work together and one which I expect to involve a great deal of experimentation and possibly discussion with the developers of these programs. 

However I must say that as they are not owned by large companies I may find that the developers will be in a better position to advise me should I need to approach them.

Whilst this discussion has only touched on programs specifically aimed at genealogists that does not mean that they are the only software I am using.

In these days of increasingly paperless offices we could not do without our word processors, spreadsheets and other organisational tools so I will be discussing those both here and in my Google+ community.


Please feel free to request to join my community. If I do not approve you at first please review your profile, as I wish to keep the discussion in the community and will only allow those with a proven interest to join to avoid members getting off topic notifications.

I must not forget Ancestral Sources

What is Ancestral Sources?

I did not include this in my original discussion but this program works alongside Family Historian and has been written by one of the users Nick Walker.

There is a Family Historian User Group which has a website set up and run by Jane Taubman another one of the users. http://www.fhug.org.uk/ 

Ancestral Sources has a board on the Forum at the website for discussion of the program and its use alongside Family Historian.

Nick Walker has just released a new update for his program and as it is an aid for entering sources I will discuss on this blog how I will set it up to ensure I enter my sources so that I can easily cite them and transfer information between my software programs.

The program has been written so that individuals can adjust certain settings to their own preferences. 

I am not saying that the way I do things is the only way, but I want to ensure I have all the information needed to create my citations, readily at hand in the program.

If you use Family Historian you should look at Ancestral Sources as an easy way to consistently record some of the standard sources, and as it interacts with your file and keeps a log, you can be sure that as you add information it goes exactly where you want it to go.