Registered: 1217151204 Posts: 6,865
Reply with quote #1
I don’t know when I first became interested in royal genealogy. I asked my mother once and she just said I always had been. Certainly, I can remember already being fascinated by the subject while still a young schoolboy, in junior school even.
I was a good deal older than that when the forum owner Theodore Harvey published a chart of the relationships of the then current European monarchs, on 27
th February 2009. I loved the idea, and loved the chart of relationships between the reigning sovereigns at the outbreak of World War I that followed even more. It was nevertheless more than three years before I decided to have a go myself, posting my chart of relationships at the accession of Elizabeth I of England on 25 th March 2012 (somehow, it seems longer ago than that). This 1558 chart was followed, though not in chronological order, by charts for 1453, 1492, 1660, 1713, 1789, 1815, 1871 and 1939, then by extended and updated 1914 and current sovereigns charts.
All very well, but the thread by now had assumed monumental proportions, and anyone looking for a particular chart had a search ahead of them. The number of reads the thread continued to receive, even when there had been no recent posts, made me believe that there was interest, and I felt it would be worthwhile to post the charts again in a more accessible location.
This is that location, kindly provided by Theodore, and here each year will have its own thread (apart from 1914, which will have its own three). The charts also will not simply be repeat postings, but revisions and reworkings of the old charts. I naturally learned a lot about the process of compiling the charts as I went along, becoming more accurate (though unfortunately still not entirely error-free) and learning also how to show more information than just the basic relationships. The intention is to bring all charts up to the standard of the latest.
The tense is future because today when I post this it will be accompanied only by one charts thread, that for the present day, though it will be a rather large and ambitious one. Of the other years, 1939, 1914, 1871, 1815 and 1789 are all nearly ready. I expect to post them over the course of the next week or so, and will then begin reworking the 1713 chart, continuing to progress backwards until I reach 1453, which will be both end and beginning.
I hope the charts will be enjoyed by others; although at times compiling them has seemed a hard grind, I have overall greatly enjoyed the work, and learned much in the process. A few notes on individual aspects of the charts follow, which newcomers to the section are recommended to at least skim down to #5 before proceeding to whichever year they wish to look at first.
Registered: 1217151204 Posts: 6,865
Reply with quote #2
Although this cannot be considered a scholarly project, I have tried to take as much of a scholarly approach as I am capable of. In that context, I have to explain the definition of ‘nearest relationship’ used in these charts. The standard definition is to measure relationships by degrees of distance, as shown in the table below. The numerical notations shown by each relationship are the degree of distance from the person.
I follow this approach to an extent, but not entirely. In my charts, the nearest relationship between two individuals is considered to be that through the nearest common ancestor to either. Both systems work the same until you get to cousins two or more times removed; for example, in the table you can see that a first cousin once removed is at five degrees while a second cousin is at six. The former relationship is therefore nearer and that is what I would show. However, a first cousin twice removed is also at six degrees. I will nevertheless still show only that relationship, as it is through a grandparent of one of the parties, while second cousin is through a great-grandparent of both. Even a first cousin thrice removed at seven degrees would still be shown in preference to a second cousin at six.
Though I believe this to be a reasonable and valid approach, I admit its unorthodoxy. The reason for using it is strictly practical; where two individuals are only remotely related, you are looking at a
lot of different ancestors between them. Working in the lowest generation possible, which is what this methodology results in, reduces the number of individual ancestors to be kept in memory and compared, making the work both easier and more accurate.
The other thing I need to say in the context of an attempted scholarly approach is that Genealogics, to which I owe an enormous debt and which more or less made the project possible, does evidently see itself as a scholarly enterprise and on its pages for individuals gives sources for the information shown. All sovereigns in the charts are linked to Genealogics ancestries, and via these the individual pages can be accessed and the sources seen, if desired.
Registered: 1217151204 Posts: 6,865
Reply with quote #3
For similar practical reasons, the definition of ‘sovereign’ is also somewhat restricted, though not until we reach 1871. In this and the previous century all sovereigns are included, apart from the kings of Serbia and Montenegro in 1914, who were unrelated to other sovereigns (this still left the 1914 chart large enough to take up three threads in its various iterations). Before 1914 only kings and emperors and queens and empresses regnant feature. Prince-electors, grand dukes and grand princes, sovereign dukes and princes; none of these need apply, just to keep the task within some sort of bounds. The only exception is Grand Princes of Moscow; it somehow did not seem to be playing the game to exclude the hereditary rulers of the largest European country, though at times I wished I had had the nerve to, their relationships were not all easy to establish.
Registered: 1217151204 Posts: 6,865
Reply with quote #4
There was never much of a scheme to these, apart from aiming for a reasonable spread of coverage. When I felt like doing another chart I just hunted around for a suitable date that would also fit in to the developing series, excluding any (apart from 1914, which as the high point of European monarchism before the catastrophic collapse of 1918 was far too significant to be omitted for any reason) for which I could not link all the then reigning sovereigns; 1688 for example was overlooked due to Jan III Sobieski of Poland, who for all his greatness and merited fame was not of royal blood, or even the highest Polish noble blood (in which case connections, albeit remote, could undoubtedly have been found), and had no traceable relationship with his fellow sovereigns. Despite this limitation I think it will be agreed that I have not ducked any challenges.
It was though the limitation that brought the series to an end. I tried a number of pre-1453 dates, but for every one I looked at there were 1) so many sovereigns that the resulting charts would have been impracticably unwieldy and 2) several sovereigns whom I could not connect to all the others, or in some cases any of them. These were usually in the Balkans and/or Scandinavia, though when I looked at 1066 I could not even establish reliable connections for the King of France, or rather at that date of the Franks.
Although picked for their individual significance rather than to form any real pattern, the dates do in some cases form a pattern worth mentioning, even if it was not by plan. The 1789 and 1815 charts bookend the era of the French Revolutionary and Napoleonic Wars, a period of enormous change for European monarchy, as for much else. The 1453, 1492 and 1517 charts appear to fall excessively close together but are in fact all quite different, giving an interesting picture of changes over the course of just 64 years (105 years if the 1558 chart is thrown into this mini-series).
Other charts stand alone, though I think comparison between any one chart and its immediate predecessor and successor will always be of interest. I found it so, anyway, and hope readers of these threads will feel the same. Overall, the concluded series contains at least two charts for every century apart from the 17th, and the chosen date for that is fairly square in the middle. So even without careful planning having produced the result, the pattern of royal relationships over a span of 560 years can be observed.
Registered: 1217151204 Posts: 6,865
Reply with quote #5
How to read the charts
The stated relationships should be read such that (name on left) is the (relationship) of (name on top), and their nearest common ancestor is (see key). Relationships are most commonly as cousins, and here for example 1c1r means first cousin once removed. Other relationships occur, and fairly obvious abbreviations are used, B for brother, U for uncle and N for nephew/niece and so on. Parent/child relationships are spelled out.
Where names on the left and names on top are the same, the former are linked to Genealogics ancestries and the latter to Wikipedia articles. Where they are different, all are linked to Genealogics ancestries. Names in the key are linked to English Wikipedia articles, or if none exists for the individual in question to whatever I could find that seemed most suitable.
The figures in brackets after names in the key are the number of connections formed by the individual in that particular chart. A summary of these figures is shown after each key. Where charts have had to be split, which most often will be the case, a table or tables (sometimes they have to be split too) of combined statistics will follow the charts.
Finally, all or most of the relationships in each chart are shown in red and underlined. These are linked to a graphical depiction of the relationship from Genealogics, which will show the exact chain of descent of the two sovereigns concerned from their common ancestor, including multiple relationships and all multiple instances of the same relationship (unless there are more than ten relationships within the same degree, ten being the limit that can be shown). If the relationship is remote, and especially if it is remote and multiple, the graphic can take up to several minutes to load, though most will appear far more quickly than that. They never in my experience fail to load altogether, so if you are interested in such a relationship the detail of it will eventually appear. Go and make a cup of tea or something, it'll be done when you get back.
Registered: 1217151204 Posts: 6,865
Reply with quote #6
I know it will be dull, and there is certainly no obligation on anyone to read it, but I feel it necessary to describe something of the way I go about compiling the charts and associated matter. To start at the beginning, having selected a date I compile a table of the monarchs then reigning, showing their accession and end-of-reign dates. The table is then sorted from earliest to latest accession date and the list of monarchs thus produced pasted into column A of an Excel worksheet, starting at A3, and pasted again transposed into row 2, starting at B2. Genealogics links are then applied to the column and Wikipedia links to the row, additional titles added and the whole converted to a table and styled (Excel tables preserve much more of their appearance when pasted into this forum than normal ranges do).
I then start work on finding the closest relationships between all these monarchs. This is a simple matter of opening the Genealogics ancestries of the first two monarchs and looking until I find a relationship (though often I will already have a good idea what it is), then checking in that and lower generations to see that there is not a closer relationship I have missed, or a different one of the same degree (which will then also be shown), selecting a code for the ancestor (if the ancestor has already appeared in another chart the code will be obtained from a master list, otherwise I just make one up, trying to keep a consistent pattern with existing codes) and entering the relationship. I will also start a key on a second worksheet, entering codes in column A and names in Column B as I go along.
Once every monarch’s relationships have been found, in column C of the key worksheet I put in the number of connections for each ancestor. For the first one, this will be obtained by the formula =COUNTIF(Chart,"*"&A1&"*")/2. Chart is a named range covering that portion of the chart that contains relationships. A1 is the cell with the code for the first ancestor, and the asterisks are wildcards. The instruction to Excel is ‘count the number of cells in this range that contain these characters, ignoring anything before and after them, and divide the result by two’. The last instruction is because every relationship appears twice, once for each of the two sovereigns it links.
Having done that I can fill down, obtaining all the other numbers with one double-click. I then sum these and in the next column, D, put two more figures. The first is the number of sovereigns times the number of sovereigns less one (since sovereigns do not have relationships with themselves), divided by two for the same reason as given above. Going back to the chart worksheet, in each row I add up the number of extra relationships (i.e. two relationships between a pair of sovereigns equals one, three equals two and so on) and put the figure at the end of the row. These figures are then summed, divided by two and added to the first figure in column D to obtain the number of relationships in the chart. This obviously should be the same as the total of column C, and if it is not investigations ensue.
Then it is back to the chart to check every relationship, usually working from the opposite direction to that I began with. The first time round, after establishing a relationship I will have seen whether a ‘red’ link can be obtained, and as part of checking these are all clicked to see that they work correctly.
Checking complete, I go back to the key. First this is added to the master list and duplicates removed (there is an Excel tool for this purpose). Those left over are examined to make sure they are genuinely new, not an existing ancestor with a different code or a name expressed slightly differently. The master list can then be alpha sorted by code, as the key will already have been. Back to that and it is time to create the horizontally laid out key that will appear on the forum. This is done in column E by concatenation, the formula for the first name being =B1&" ("&C1&")". The instruction is ‘reproduce the name in column B and the figure in column C, with a space before and brackets around the latter’.
It might be asked why instead of typing all the gobbledegook I don’t just copy the name over and type the figure in brackets after it. The answer is that, say there are 20 names in the key, I would then have to do this 20 times and check to get rid of the inevitable errors. The concatenation I do once and fill down, one click-and-drag replacing a whole lot of pasting, typing and checking, with no possibility of error. Also, say I notice a typo in a name. I only have to correct it once; the second instance automatically changes to match, which it wouldn’t if the column had been done manually.
I can now put in the hyperlinks for the names. Next is to do a trial posting of the chart, to see whether it can fit in one piece. Assuming it can, search and replace is used to turn cell references in column E absolute, so that the example above becomes =$B$1&" ("&$C$1&")". Previously the references were relative, so that when the formula was copied to (say) 19 other cells by filling down they changed to refer to the cell in the same relation to the new location as the original cell had to the old. I wanted that, if the references had been absolute to begin with I would have got the same name and figure twenty times, instead of twenty different names and the figure relevant to each.
However now the concatenated, hyperlinked names are going to be copied into a horizontal layout, and unless the references are absolute, so that when moved to a new location the cell they point to does not change, the result really would be gobbledegook. Assembling the horizontal key is a simple matter of copying the codes, three at a time and with a row between each set of three, transposed into columns F-H, then the concatenated names three at a time into the rows left vacant between. A mouse-over to check that the links are all correct, and column E is no longer required and can be deleted, so the horizontal key is now in columns E-G.
Finally the statistical summary needs preparing. First stage is to sort the figures in column C from largest to smallest. This also sorts columns A and B and incidentally sends the horizontal key completely haywire. The codes in column A are copied in their new order, omitting those at the bottom with only one connection shown in column C, and pasted into column I (column H is left blank so that the summary is not contiguous and won’t be affected by further sorting operations in the rest of the key).
I then alpha sort column A, returning the horizontal key to sanity, and in cell J1 type (keeping to the example of 20 names in the key) =VLOOKUP(I1,$A$1:$C$20,3,FALSE). There are four parts to this formula, separated by commas. The first, I1 (i.e. the first code in numerical sort order), is what to look for. The second, $A$1:$C$20, is the range in which to look for it. It is in fact the first column of the range only which will be checked, because that is how the VLOOKUP function works. The third, 3, is the column in the range from which to return the figure that corresponds to the location of the search item in the first column. The fourth, FALSE, is an instruction to find exact matches only for the search item.
The end result is that say the code in I1 now happens to be located in A15. The figure in C15 appears in J1, which is of course the right figure for that code. Fill down, and the statistics are ready for concatenation. Almost ready, anyway; first I sum the figures in column J and then below that type =SUMIF(C1:C20,”>1”). This will add up all the figures over 1 in column C, which should be the same as the sum of column J. It always is, but I check anyway. Concatenation then takes the form, using the example of the top number of connections occurring twice, =I1&", "&I2&" ("&J2&")". Say the codes are ABC and DEF and the number of connections five, what appears is ABC, DEF (5). The rest of the codes are then concatenated with the figures in the same way, and chart, key and stats are ready for posting.
I could go on to describe what happens if, as is most often the case, the chart is too large for posting and has to be split, but I think I have already wearied any remaining readers sufficiently. Suffice to say that there is the same cross-checking and use as far as possible of automated processes (not just because I’m idle, but to reduce error) as described already. The only thing that is really different is that then a table of combined statistics is prepared, as well as the individual statistical summaries. This is done in the original key worksheet. The key is sorted by largest to smallest in column C and the codes copied over in the new order, then VLOOKUP as described already is used to obtain the names and the total figures. What is done to obtain the split chart figures is a little different, however.
A typical formula would be =IF(ISNA(MATCH(F3,'Key I'!$A$1:$A$12,0)),"-",VLOOKUP(F3,'Key I'!$A$1:$C$12,3,FALSE)). If you don't have any familiarity with Excel that probably looks terribly complicated, but all it is saying is ‘look for this code in column A of this worksheet, and if it is not there show a dash; if it is, show the corresponding figure from column C’. I do it this way rather than just use VLOOKUP by itself because in split chart keys the codes will often
not be there and I would get an ugly #N/A error message rather than a dash or blank. The considerably shorter =IFERROR(VLOOKUP(F3,'Key I'!$A$1:$C$12,3,FALSE),"-") would work just as well, but suppresses all errors; I prefer to suppress only the particular error type that is expected and harmless, in this case #N/A errors using ISNA, and not risk covering up an actual problem by suppressing other error types as well. All other things being equal shorter formulas are better, but in this case they are not quite equal. The latest version of Excel has an IFNA function which I would be happy to use in this situation, but mine is not the latest, alas.
Again, I only type the longer formula once, then fill down, and much of it I don’t type; 'Key I'!$A$1:$A$12 for example is produced by selecting the range A1:A12 and hitting F4. OK, that’s it. Sorry for the tedium to anyone who made it this far, but to me it was a necessary part of the project to describe how I get the relationships and figures in the charts and keys, as otherwise they are just my assertion and don’t really have a lot of credibility.
Registered: 1217151204 Posts: 6,865
Reply with quote #7
Although this thread’s first post is dated 21
st July 2013, the thread as a whole was actually created today, 3 rd July 2014. Before that, the discussion thread above was itself the introduction thread, and opened with the material now contained in this one. Why the change? Website Toolbox are very good in many ways, but do have an unfortunate habit of tinkering with the way their hosted forums work, not always resulting in any noticeable improvement and sometimes producing the reverse of that.
For example, until recently clicking on a thread took you to its first post. Now it takes you to its last. That seemed unnecessary since there was already a way of going to the last post, by clicking the icon to the right of the last post column. And it meant that, unless you were already familiar with forum navigation, in a multi-page thread such as the one above it was actually quite hard work getting to that first post. There is not much point in introductory material unless it is easy of access to newcomers, so I moved the actual introductory matter into this new thread, leaving the old one for discussion of and notices of changes made to the chart threads.
As with the chart threads, this introduction to them is for reference only and is locked. Of course anyone who wishes to comment on its content is welcome to do so in what is now the discussion thread, and is assured of a prompt and responsive reply. I don’t really expect any comments and nor do I many reads, but it is important that those who do want to read this material can readily get to it, as they once could and now can again.
Registered: 1217151204 Posts: 6,865
Reply with quote #8
I don’t want this thread to go to multiple pages, or even a second page, but still may make additions to it like this one from time to time. As there are 15 posts in a standard page view and this is only post #8, there is scope to do so.
Of the various changes to the section since I completed initial posting, the most drastic was due to another software change, this time to the Genealogics relationships calculator, used to produce the red links explained in post #5. But it was not so much a change as a transformation; think Bruce Banner and the Incredible Hulk. Previously, I managed to create links for perhaps one in three of even the single relationships in a chart. If relationships were multiple, only one of the set could possibly be linked. Where there were multiple instances of a relationship only one was displayed in any link I could obtain. (Multiple
involve two or more ancestors, multiple relationships only one; the links give an example of each.) instances
Once the calculator had had its gamma ray infusion I was able to link nearly all relationships. So long as there were not more than ten relationships in the same degree (possible but rare), I could show all of a set of multiple relationships and every instance of each, or of a single relationship. And, in some ways the biggest change of all, the links now constituted verification of my work, which they could not be said to before. Of course they failed to verify some of it, because it was wrong. My head told me that was inevitable in a solo project of this size, and that overall my level of accuracy had been quite creditable; my heart though found it a chastening experience publicly
the errors, as I felt obliged to. listing
But the point of this is not to recite the history, which I have supplied just to give context to what follows, but to explain how my approach to compiling charts, described in the second paragraph of post #6, has been affected by this powerful new tool, and also give some pointers to how people can use the tool themselves. On the first question, I still start by opening the ancestries of the individuals concerned and thinking what their nearest relationship might be. I’m not worried, though, about whether it actually is the nearest, since the calculator will take care of that.
Once I have a relationship I summon up the calculator (by clicking ‘relationship’ in the green bar below the person’s name) and set it to the appropriate number of generations and maximum relationships. If there are nearer relationships than the one I came up with they will be found, and I can then reduce the number of generations to display only them. If there are other relationships in the same degree, again the calculator will find them, and I can then adjust the number of relationships to exclude those more distant. It sometimes takes a few attempts and even a reversal of direction to get only the relationships I want and all of them displayed, but usually this can be accomplished and the link is then ready to paste into the worksheet.
It’s definitely less laborious and more accurate than my original method, with the only downside being that some of the challenge is removed. What is also less laborious is checking, since all I really have to check is that I have not mistyped the relationships found by the calculator. I combine this with checking that the links work by simply clicking them all and comparing what is in the link with what is on the worksheet. Anyone who suspects that I sometimes get bored doing this and take off into genealogical explorations of whatever appeared in the last link clicked is of course right.
And that sort of fun (well, I think it is) is not my prerogative alone. You can go anywhere in the vast Genealogics database using one of my links as a starting point. And do other things too. Say you want to see all relationships within the same degree, not just those I define as the nearest. Simply use the controls in the blue bar above the relationships graphic to reset the calculator however you want. Or just edit the URL, which I do all the time. Here is the URL from the second link above, omitting the unnecessary http etc.:
The variables in the URL are joined by ampersands and come after the question mark. The order they appear in doesn’t matter and can vary, and while the last three variables above must be present any or all of the first three can be omitted, as defaults will then be used.
is the kind of thing you would get; be warned though that the default 12 generations works here but can lead to the calculator grinding on for minutes on end before falling in a heap. This
For somewhat more finely tuned adjustments to the relationships displayed, instead of using the controls you can for example just change the maxrels figure and hit enter to get a different number of relationships displayed, or to see more distant relationships do the same with that for generations. Or to reverse direction simply swap the two person IDs, like
To edit a URL you have to first click its link but you can see a URL without that, by hovering over a link then looking at what appears in the bottom left corner of the screen. This can be handy; for example, if maxrels are 10 and generations 9 it’s probably time to go and make that cup of tea I mention in post #5. Just don’t forget to click the link first.
Registered: 1217151204 Posts: 6,865
Reply with quote #9
Despite my remarks in post #4’s second paragraph, I have over the last months added not merely
a pre-1453 thread, but five of them, concluding today with a thread for 1215 and the sealing of Magna Carta. As with the evolution of the original series, the reason for my change of mind was rather random; I just happened to wonder whether the date I eventually used for 1330 might work, found that it would and proceeded. That done, I felt the thread looked a little lonely stranded 123 years from its nearest neighbour, so did one for 1415. I then decided I needed something more in the middle of the two, so did 1371. As well, not instead of, posting 1371 first.
I then became intrigued by the idea of a ‘Scottish trilogy’; three Scottish kings, not so far apart in reign dates but of completely different dynasties, each undescended from the others, their relationships available to compare and contrast. I had already done 1330 with David II (Bruce) and 1371 with Robert II (Stewart), so 1286 with Alexander III (Dunkeld) was the obvious next choice, the chosen date allowing me to highlight the long-term significance of what might be seen as an unimportant event except to those directly concerned.
Finally, and quite separately, I got the idea of celebrating Magna Carta’s upcoming 800
th anniversary with a thread for that date. And so it was done. In order to create these threads I had to relax my rule that all sovereigns reigning on the chosen date must be 100% connectable, though not for 1415 when they were anyway, or 1330, even though I chose to omit one sovereign from the charts. The threads are a little different from those in the original series in other ways, particularly their two-part introductions and the tables analysing relationships that appear in the second parts, which I feel were a valuable addition. Incidentally if any aspect of the tables seems confusing I suggest taking a look at the 1330 thread; as this was the first of the extended series to be posted there is a very full explanation of the new feature there, but only an abbreviated one on other threads.
As explained in the 1215 thread posted today, I won’t be adding any more threads, and the series will grow only with new accessions (and, let us hope, a restoration or three). A span of eight centuries, with people such as William the Conqueror and Henry the Fowler featuring as connecting ancestors in the earlier threads, is though I feel pretty reasonable coverage of the evolution of royal relationships, and I am content that it will rest there.
Registered: 1217151204 Posts: 6,865
Reply with quote #10
I mentioned in post #7 above that this thread is for reference only and is locked, without really giving any reason for the latter. There are actually two reasons, both deriving from the eccentricities of Website Toolbox. As explained above, for no apparent reason they changed the result of clicking on a thread to being the display of its last post rather than its first, making the introductory material contained in this thread, which then was present at the beginning of the discussion thread, difficult to navigate to for forum newcomers and leading me to remove it to this separate thread.
If replies directly to this thread were allowed I doubt there would be any anyway, but if they did happen then eventually the thread would go to a second page, recreating the navigation difficulties, if not in quite such extreme form as before. If only I can reply then I can prevent that happening. That was the first reason for the thread being locked; the second, which applies more generally, is that pinned threads on this forum behave in relation to each other exactly like unpinned threads, that is they are ranked in order of most recent post. On every other forum I have been an administrator on pinned threads are ranked in reverse order of pinning, with the most recently pinned appearing first, and replies make no difference to their order.
This is obviously sensible, as if you pin threads in an order it is likely to be for a reason – here, so that threads can readily be found by their subject date, being arranged by that. Because of Website Toolbox’s different way of doing things, the only way I can keep that order is to lock all the threads. And, unfortunately, if I add a new post to any of them, as I have just done with the 1952 to the present day one, to get the threads back in order I have to then do a positioning post to all the threads that should be above the changed one.
A positioning post is exactly what this is, but I nevertheless should say something nice about Website Toolbox in it, which is that their HTML editor does do a very good job of converting the charts, far better than on any other forum I have tried on. So since it is they along with Genealogics and, of course, the generosity of Theodore Harvey that really make the whole project possible, grumbling about them without that addition really would have been out of place.