Excel: Basic Tips and How To's

DTRM28

Hi,

I have used the prefix [n00b] as it is my first thread and not too sure whether it will hold much relevance in this Forum so bare with me.

I enjoy using Microsoft Excel and interested to further develop my understanding and skills, as well as help others in the process.

So, if you have questions around how to add sheets, change sheet names, working with CountIf, IF logics, tailor the ribbon to suit your needs, or the use of Conditional Formatting, please post and I will do my best to address your query.

I also have some experience with Macros and working on developing my use of VBA within Excel and have already used VBA to generate UserForms for data entry into Excel Sheets.

DTRM28

imp1

Hi,
Nice idea for a thread.

I am trying to graph some time averaged data, some of the data is averaged over 10 minutes, while other data is averaged over 15 minutes, so in every hour there are 4 points of 'data x' and 6 points of 'data y' . The x axis only needs the hour marks, y axis easy enough to configure, I have done something similar before, just can't remember how, any ideas?

FluffyTowel

imp1 wrote: »

Hi,
Nice idea for a thread.

I am trying to graph some time averaged data, some of the data is averaged over 10 minutes, while other data is averaged over 15 minutes, so in every hour there are 4 points of 'data x' and 6 points of 'data y' . The x axis only needs the hour marks, y axis easy enough to configure, I have done something similar before, just can't remember how, any ideas?

You could add in an extra column which divides the 15 minute data by 3 and multiplies by 2.

DTRM28

imp1 wrote: »

Hi,
Nice idea for a thread.

I am trying to graph some time averaged data, some of the data is averaged over 10 minutes, while other data is averaged over 15 minutes, so in every hour there are 4 points of 'data x' and 6 points of 'data y' . The x axis only needs the hour marks, y axis easy enough to configure, I have done something similar before, just can't remember how, any ideas?

Hi Imp1,
Thanks for the response to my post and thanks FluffyTowel for your input.
Trying to understand how your data looks first. Are you doing total average for the input data; Is it that in Column A for example you have time HH:MM. Then in Column B you are summing Column A and dividing by 10, and in Column C by 15? i.e. =(SUM(A1:A10))/10 and =(SUM(A1:A10))/15

OR is it that you have an average running every 10min interval, and 15min interval, and then graphing the data. So for example.. going down Column A you have your input data, Column B has an average at each 10min interval, and Column C has an average every 15min interval? e.g. 2min, 4min, 6min, 8min, 10min (Average), 12min, 14min, 16min etc..

Am I understanding you correctly or could you please clarify, when I understand how the data looks then I can start working on the graph.


Thanks

imp1

Hi,
The data is already averaged, in hour 1 the data is
x data = 12, 15, 14, 13
and y data is 6, 5, 4, 6, 3, 4

Both sets of data start at 12:00, and finish at 13:00

I want to compare the trend in the 2 sets of data, adding another x axis should do it, I think, I have done something similar before, just trying to remember what I did!

DTRM28

If you have your values in rows. x Data = A1:A10, y Data = B1:B10. Start time (12:00) = C1, End Time (13:00) = C10. Generate a blank line graph. Then right click on the graph click select data, then under Legend Entries (series) click Add. Series Name = x Data, Series value = A1:A10 values. When you've created this series, edit the horizontal axis labels = Axis Label Range = C1:C10. Then to add your y Data values, do the same as with the x Data but leave the horizontal axis labels as is. Then click Ok. You should have values along the Y axis, 12:00 & 13:00 on X axis, and two lines for your two datasets.

Is this is what you were trying to accomplish?

DTRM28

imp1

That works well enough if each data set has the same number of values, what is tripping me up is one data set has 6 values per hour, the other has 4, so i need to stretch the x axis for one data set to be the same length as the other, or have 2 seperate x axis

DTRM28

imp1 wrote: »

That works well enough if each data set has the same number of values, what is tripping me up is one data set has 6 values per hour, the other has 4, so i need to stretch the x axis for one data set to be the same length as the other, or have 2 seperate x axis

Sorry didn't see your message until now. Maybe then use the data set that generates the longer X axis as your set value for the X axis, and maybe adjust your tick mark increments in the Axis settings? Never liked working with time as a value, so sorry I'm not quicker to a solution. I will have another crack at it to see if I can get something closer to your needs.

Doyler92

I appreciate your help. I use excel a lot in work and find some parts confusing so I will be coming back to this thread in the near future I’m sure.

Thank you!

DTRM28

Hi All,

I've not posted in a while and wanted to give some tips which I find useful; some may be very obvious others may stand out.
Do you find yourself resizing columns by clicking on the divider and sliding it left and right. If you double click it will readjust itself. Alternatively, if you click the uppermost left part of the sheet, above Row A and left of Column 1, to highlight the whole sheet, then double click the column divider. It will resize all columns respective to the data within them. The same when you double click the row divider.

Also the use of wrap text is great, especially when you want to keep columns tight but the wording runs into the next cell.
You can turn this on using right click, format cells, alignment, and will see a tickbox for wrap text. By default it is also in the ribbon under Home on your toolbar, Home>Alignment>Wrap text. It doesn't appear in a 'Restored' view, need the workbook 'Maximised'. A quick way to re-scale your workbook with the keyboard is Alt-Shift-X (maximise), Alt-Shift-N (minimise) and Alt-Shift-R (restore).

If you want to highlight and copy a range of data; use the combination of shift-ctrl-down arrow. It saves having to drag the cursor over the data you want. Or holding shift and repeatedly hitting the down arrow.
The other nice one is if you want to copy certain values from a range. Hold ctrl for each click. For example; you can select A1, then hold ctrl, select A3, A5, A10 and proceed to Copy (Ctrl-C) and paste them (Ctrl-V). When you paste, the values will reside under one another; so when we paste A1, A3, A5, A10 into column B the paste into B1, B2, B3, B4.


Hope you find some of these little tips useful if you didn't already know them, and I will try get posting again soon with some more.

Cheers,
DTRM

Jackaroe

Hi there, could use a bit of help/advice on an issue if you could?
I’m currently working on an excel template that will have about 80 or 90 sheets, with a dashboard linked to them. The sheets are updated from system generated csv files so I will need to copy and paste from 80 or 90 files to populate the template. I will be running this once a week so want to automate it to pull in the files itself. Looking at doing something in VBA but haven’t quite figured out how to get this to work yet.
So for example in week 47 I will have my 90 csv files output to “week 47” folder, will drop my template into that folder and want it to import file 1 to cell A2 of sheet1, file 2 to cell A2 of sheet2 and so on. How would you specify this in VBA so it looks for the files in the directory that the template is dropped into and loops through each file and pasted fileX to sheetX?
Thanks a lot!

DTRM28

Jackaroe wrote: »

Hi there, could use a bit of help/advice on an issue if you could?
I’m currently working on an excel template that will have about 80 or 90 sheets, with a dashboard linked to them. The sheets are updated from system generated csv files so I will need to copy and paste from 80 or 90 files to populate the template. I will be running this once a week so want to automate it to pull in the files itself. Looking at doing something in VBA but haven’t quite figured out how to get this to work yet.
So for example in week 47 I will have my 90 csv files output to “week 47” folder, will drop my template into that folder and want it to import file 1 to cell A2 of sheet1, file 2 to cell A2 of sheet2 and so on. How would you specify this in VBA so it looks for the files in the directory that the template is dropped into and loops through each file and pasted fileX to sheetX?
Thanks a lot!

Sounds a big workbook... Could you have the data on one worksheet. And each column being respective to each csv file i.e. Column A = csv file 1, Column B = csv file 2 etc...? Just so you can paste into the one sheet rather than 80 sheets. Will you be overwriting the csv files each week, and hence overwriting the data in Excel as a dynamic ongoing tracker. Or do you have need to retain the previous weeks data?
I have a file I use that downloads a csv file, and then pulls in the data to excel and delimits it and reformats as required. Under Data>Connections>From text. You can use this to create connections to the individual csv files as .txt. Are you familiar with this? This would help save you doing copy and past for each file. Once you have the 80 connections made up, then you can use VBA to place a button on the file that when you click it will pull in the data from each source. Or get the file to grab the data upon opening.

Jackaroe

It can’t be on one worksheet as the files are big, some will be 10,000 plus lines with 20 columns.
A new file will be created each week from the blank template, previous week template retained as is.
I was playing with VBA with some success but was a bit stuck on getting it to look into the folder the template was dropped into.
Will have a look into the data>connections and see if I can use that.
Thanks

DTRM28

Jackaroe wrote: »

It can’t be on one worksheet as the files are big, some will be 10,000 plus lines with 20 columns.
A new file will be created each week from the blank template, previous week template retained as is.
I was playing with VBA with some success but was a bit stuck on getting it to look into the folder the template was dropped into.
Will have a look into the data>connections and see if I can use that.
Thanks

I will try have a play around with VBA and see if I can get it to look into a csv file to copy across the data that way.

The data connection to a csv file works well if the file is overwriting. You can get the Excel to call out to the csv with a specified time interval or each time the sheet is launched. But it doesn't sound like it will do the job for you if it is a new file each time. Unless you built them ahead of time.. i.e. created a csv file in Notepad called Week47, Week48, Week49 etc... this will get overwritten when you actually do your download for those weeks. And you will already have the connection built to your template so it will pull the data in which your dashboard can look at. Would be ongoing maintenance but may just work.
I think there are a few steps i.e. download, then paste to template, then get the dashboard to view the data. I always like creating a "dummy" workbook to try new things.

DTRM28

Jackaroe,

I have worked on a piece of vba code to launch your file grab the data and close it again.
See if you can work it for your file:

Private Sub CommandButton1_Click() -> I placed a command button on the sheet that I can click to run the script. You could alternatively put code under the 'ThisWorkbook' in the VBA editor. Private Sub ThisWorkbook_Open() -> this runs the script upon the workbook being opened. You could put in an IF function to tell it not to Run if the file is opened as read only.

Workbooks.Open ("C:\**********\test1") -> put in your file location. If you file extension visible i.e. .doc, .csv when you look at the file, you may need to specify this.

With Workbooks("Book1").Worksheets("Sheet3").Range("A1") -> this is the location where you want the data to end up.

.Value = Workbooks("test1").Sheets("test1").Range("A1").Value -> this is our csv file, and the data in the file we want to pull. I kept it simple by specifying a Cell but you could say Sheets("test1").SelectAll or .SelectAllData. one of them.

End With -> this closes the statement

Workbooks("test1").Close savechanges = False -> this closes are csv file.


End Sub -> end the script

Trial it out with a made up sheet and csv file. Then you can try develop it further to meet your needs, but I think this will give you the ground work you need.

Please keep me posted on how you get on and can PM if need further support.

DTRM

namenotavailablE

In VBA, the code "ThisWorkbook.path" (without the quotation marks) gives the directory of the location of the current workbook. This might be useful in the code you're using to import the CSV files.

Given the number of CSV files, I'd also suggest a for/ next loop to iterate through the 'Adding connections' method. Perhaps, an Input box at the start could be used to ask 'How many CSV files to process this week?' which would provide the upper limit of the loop?

namenotavailablE

I've created a test workbook and 2 test CSV files which may do what is being requested.

The test workbook link is: https://app.box.com/s/4eugyz8y3ugdfofl3qcbiyz0s4ejgm80

The first CSV file link is: https://app.box.com/s/qbgprdv9xk9bxpncutel1nhmru4yeyfg

The second test CSV file link is: https://app.box.com/s/34f4l0yqugqkwllh168j3os1jk5tph8w

To test, download all 3 files to the same directory and then open the test workbook. You'll likely need to enable macros if your security settings are typical for Excel. Once opened, there are basic instructions on the main sheet (should be straightforward).

Please note: there is minimal 'error trapping' included in the test workbook so if you enter an invalid value for the number of CSV files to process (asked by an input box at the start of the code routine) it will likely break the code.

Leave a message here if any tweaks are needed.

Jackaroe

Thanks guys, really appreciate your help here. I will have a play around with those and see if I can get it to work!

The Tetrarch

Ok, I have strayed from the Horseracing forum for a reason.

The above is the six generation pedigree chart of the thoroughbred horse Nearco.
That is a screenprint from the commercial pedigree software package, TesioPower, I bought in the mid-1990s.

Since the early 1990s I have worked on pedigree analysis, comparing inbreeding duplications to horse ratings.
Inbreeding duplications: see St Simon in the above pedigree. He appears four times. That is an inbreeding duplication.
Horse ratings: these are from 0 to 147 in flat racing, the higher the horse rating, the better the horse.
My old database program, MicroSoft Visual FoxPro, using a few programs I wrote, generates a pedigree into a database file, identifies inbreeding duplications, counts them, outputs into a results file.
About two years ago the program analysed 159k pedigrees, working at 40 pedigrees a second.
There are about 27 different inbreeding duplication group types, some favourable to performance, others not.

Above there are four inbreeding groups:
St Simon; Isonomy; Toxophilite; Hermit.
The inbreeding groups are sires (male).
The group sizes are 4, 2, 2, 3.
The offspring from these groups vary, males and females e.g. from St Simon they are Cheery (female); Chaucer (male); Rabelais (male); Simona (female)
The generations vary e.g. for St Simon 5th, 4th, 4th, 5th.

You can see the variety can be large:
male / female (2) ... 2
group size (3) ... 2, 3, 4+
offspring sex (3) ... male/male; male/female; female/female
those simple variations (2)x(3)x(3) give 18 different groups, and there are more.

What I want to do is lay out a six generation pedigree in Excel cells.
I could use Conditional Formatting with Countif() to identify and colour duplications.
My old Excel 2003 only allows three Conditional Formatting options, but recently I bought Excel Office 365.

If I used Conditional Formatting with Countif() on the above chart (in Excel) it would also highlight St Simon's sire, Galopin, as he also appears more than once (four times).
The pedigree database generated in FoxPro for each horse has 127 lines, the horses and 126 ancestors (1+2+4+8+16+32+64 = 127).
A few small programs identify duplications, wipe their ancestors (in above example: wipe ... Galopin; St Angela; Vedette; Flying Duchess; King Tom; Adeline).
It is easy to copy the .dbf database file to Excel and it has database fields identifying the duplication groups.

What I would like:
(1) Highlight St Simon, dim all other horses, but leave Cheery, Chaucer, Rabelais, Simona undimmed.
(2) Highlight lines of horses e.g. Pharos; Phalaris; Polymelus; Cyllene; Bona Vista, Bend Or ... perhaps two lines at a time, say that line and also Havresac, Rabelais, St Simon, Galopin, Vedette.
(3) selectively highlight horses, probably with the database fields (now in Excel) beside the chart, manually inputting into a column, and with the ability to choose colours.
TesioPower has in the menus twenty "Windows color numbers", 255 (bright red), 16711680 (royal blue), 32768 (moss green) 8388608 (navy blue) and so on.

What I want to do is explain the inbreeding results from the 159k horses by showing in pictures (pedigree charts like the one above) the inbreeding that is desirable.

namenotavailablE

For someone to assist, they will need to fully understand the underlying logic of the activity. Unfortunately, the screenshot is very difficult to follow between clutter, coloured lines and partially illegible text!

Taking the line diagram: I think I'm to read it by starting from the middle and read right to left e.g. using the top 2 horses (as shown in the middle of the screen) 'Bona Vista' (blue line so I'm guessing a male) and 'Arcadia': did they mate and produce 'Cyllene'? If that is correct, how can 'Galopin' and 'St Angela' mate and produce St Simon twice (the 5th pairing and 9th pairing of the middle of the screen)?

Similarly, what is the data shown on the right of the screen- it doesn't seem to relate to the data on the left.

Having said that, I understand that the following is your objective:

What I would like:
(1) Highlight St Simon, dim all other horses, but leave Cheery, Chaucer, Rabelais, Simona undimmed.
(2) Highlight lines of horses e.g. Pharos; Phalaris; Polymelus; Cyllene; Bona Vista, Bend Or ... perhaps two lines at a time, say that line and also Havresac, Rabelais, St Simon, Galopin, Vedette.
(3) selectively highlight horses, probably with the database fields (now in Excel) beside the chart, manually inputting into a column, and with the ability to choose colours.

So:
->item 1 is that you want to be able to highlight the sire plus all offspring regardless of the gender of the offspring
-> item 2 is that you want to be able to highlight the sire and all male only offspring. The ability to do this for a multi-selection of 'original sires' is also required.
-> item 3 is to be able to associate a specified colour with a specified horse. Is this like the above screenshot where 'Isonomy' = blue, 'Hermit' = red etc?

I reckon it's a big task so am not sure if I can help at all but if I can get a handle on the data it might prompt me and others to have a look

The Tetrarch

Taking the line diagram: I think I'm to read it by starting from the middle and read right to left e.g. using the top 2 horses (as shown in the middle of the screen) 'Bona Vista' (blue line so I'm guessing a male) and 'Arcadia': did they mate and produce 'Cyllene'?
Yes. The stallion is always shown above, the mare below.
Bona Vista was a male; Arcadia was a female; they produced a male, Cyllene.
In the screenshot the first five generations are shown "properly".
The 6th generation is shown as a list of sires (left column) and dams (right column) for space saving reasons e.g. Bona Vista was by the sire Bend Or out of the dam Vista

If that is correct, how can 'Galopin' and 'St Angela' mate and produce St Simon twice (the 5th pairing and 9th pairing of the middle of the screen)?
Galopin and St Angela produced St Simon once. Just as your parents produced you once, you can produce many children.
Centuries back before canals (1790s), railways (1830s), motor vehicles (1890s), aircraft (1900s) there must have been much human inbreeding, probably not obvious because of poor records.
"Prince Charles in inbred 4 x 4 to a full brother and a full sister. King Edward VII is the full brother and Alice of Hess his sister, both being children of Queen Victoria and Prince Albert. Edward comes through George V and George Vi and the present Queen Elizabeth II. Alice of Hesse comes through an all female channel to Prince Phillip"

My records of the female line on Nearco go back 23 generations to "daughter (unnamed) of the Layton Barb". There are better records for thoroughbreds than for humans.
St Simon is in the pedigree four times. In my data he produced 264 offspring, and the four shown: Cheery (f); Chaucer (m); Rabelais (m); Simona (f) are four of that 264.

Similarly, what is the data shown on the right of the screen- it doesn't seem to relate to the data on the left.
The two lines of numbers on the right of the screen are Bruce Lowe family numbers. Ignore them.
Bruce Lowe traced all thoroughbreds through their mothers back to the first females in the breed around 1700.
He found 43 founding females and numbered them 1 to 43. Before that there were no records.
Nearco (1935) is family number 4, his dam Nogara (1928) family 4, her dam Catnip (1910) is family 4, Sibola (1896) is family 4, Saluda (1883) is family 4, Perfection (1875) is family 4, Maiden (1862) is family 4

Having said that, I understand that the following is your objective:

Quote:
What I would like:
(1) Highlight St Simon, dim all other horses, but leave Cheery, Chaucer, Rabelais, Simona undimmed.
(2) Highlight lines of horses e.g. Pharos; Phalaris; Polymelus; Cyllene; Bona Vista, Bend Or ... perhaps two lines at a time, say that line and also Havresac, Rabelais, St Simon, Galopin, Vedette.
(3) selectively highlight horses, probably with the database fields (now in Excel) beside the chart, manually inputting into a column, and with the ability to choose colours.
So:
->item 1 is that you want to be able to highlight the sire plus all offspring regardless of the gender of the offspring
Yes.
-> item 2 is that you want to be able to highlight the sire and all male only offspring. The ability to do this for a multi-selection of 'original sires' is also required.
This is a minor requirement.
There are in my opinion four sire lines in a pedigree, and four dam lines.
I think if they match well (closely related horses on the lines) the horse is better.
-> item 3 is to be able to associate a specified colour with a specified horse. Is this like the above screenshot where 'Isonomy' = blue, 'Hermit' = red etc?
Yes. In the screeshot the colours are allocated randomly, or more likely the first colour in a colour list is applied first, then the second colour.
The reason I want to pick colours is to emphasise features.
In the screenshot:
St Simon, a male, produces two males and two females, sire in the 5th, 4th, 5th, 4th generations.
Isonomy, a male, produces a female and a male, sire in the 6th generation twice
Toxophilite, a male, produces a female and a male, sire in the 6th generation twice
Hermit, a male, produces two males and a female , sire in the 6th generation three times
Nearco was unbeaten in a 14 race career.
Note that in his pedigree all the duplicated males produce both sexes, a male and female, and there is balance in generations.
It is also possible for a duplicate sire to produce two sons (not good imo), or two daughters.
There are many possiblities, as many as 27 inbreeding types.


I reckon it's a big task so am not sure if I can help at all but if I can get a handle on the data it might prompt me and others to have a look

DTRM28

I'm not going to qoute the whole message on this It does look like a lot of work and hard to get an understanding to know where to start... But if read your messages a couple times over I'm sure it'll begin to make sense. I would suggest working on one or two aspects of your request and build from there once they are resolved. Or it may be a case you managed the horse data in one sheet as your raw data and working in different sheets for your analysis. If you've multiple conditional formatting it could get a bit messy. Maybe sheet 1 offspring, sheet 2 high winners, sheet 3 raw data. Follow me?

One thought that came to mind when you mentioned highlighting a horse and fading out others was to use splicers, it would require all the data compiled vertically like a data base. If you're not familiar with this; It identifies the data as a table and can generate interactive fields. For example: If we had mare in one field and offspring in another. When you click on a mare's name it would filter the other to show the offspring for that mare. Perhaps this could be of use?

I'll try and put ur screen shot into my Excel and see if I can come up with anything.

Thanks All for keeping this thread going and a lot of interesting chat. I'm hoping to try be on more frequently and with a regular Tip and Trick piece, feel free to do the same. If you've any little tips that could help.

The Tetrarch

DTRM28
Thanks for your reply.
For the last few weeks I have been working on 130,000 horses that I want to add to my main database of 620,000 horses.
The problem with the 130,000 horse names (actually the 130,000 names and their sires and dams = 390,000 names) is punctuation.
For example, a horse named THATS A SURPRISE in the new data needs to be manually adjusted to THAT'S A SURPRISE to link to ancestors.

....................................................................................................

This is a sample of the summary results from my pedigree analysis of 159,000 horses (37,057 colts; 50,335 geldings; 71,830 fillies).
You can see that some inbreeding types are positive (AAA, CCC, EEE) i.e. the greater the occurrence (OCC) the higher the average running rating.
Inbreeding types BBB and DDD are negative.
Inbreeding BBB is in almost every horse, often the only type in most horses, and imo is why many are slow.

For inbreeding type EEE this inbreeding is very positive for running ability.
You can see that 65% of colt pedigrees analysed (24,008 of 37,057) have no EEE.
The higher the number of occurrence (OCC) the fewer the horses (and more unreliable the average)
e.g. for AAA OCC = 0 is 15,847 horses, average 93.1; AAA OCC = 6 is 3 horses average 91.0.

It takes careful selection to build good inbreedings into a pedigree i.e. do not buy a mare unless there is a stallion available that will provide the EEE and other positive matching with the mare.
(AAA, BBB, CCC, DDD, EEE are meaningless headings I substituted for the real column headings / inbreeding types).

occ AAA BBB CCC DDD EEE EEE count
0 93.1 93.6 93.1 93.8 92.8 24,008
1 93.8 94.0 94.8 93.5 92.4 4,121
2 94.8 94.0 97.7 95.8 95.5 4,747
3 96.7 93.9 98.6 91.4 96.9 2,201
4 96.7 92.5 115.5 99.6 1,121
5 107.6 101.9 532
6 91.0 105.7 195
7 106.5 95
8 116.2 19
9 124.5 11
10 112.3 4
11 131.5 2
12 134.0 1
--
positive negative positive negative very pos 37,057


Tasks are:

• Complete the punctuation corrections of the 130,000 horses.
• Finish the re-write of the pedigree analysis program that I almost completed in November.
• Have a go at designing a six or seven generation pedigree chart in Excel (or other software) where I can highlight inbreeding easily.
  

I have time to experiment. My aim is to show visually what works in pedigrees.

rock22

Hi Tetrarch,
I came across your post by accident. I have no great knowledge of any of the software you are talking about so probably cannot help much but I find your 'project' very interesting.

Firstly , are you looking for help in the analysis of the data in Excel or do you just require a chart/diagram to show others?

Is there a reason you cannot use TesioPower to expand the chart you have posted already?

Are you entering the data on additional horses into FoxPro or into TesioPower/ If the former, can you write short program to match entries while ignoring punctuation marks? Or , if data is in a text file such as csv, could you run a simple windows program to add appropriate punctuation?

Finally, Can FoxPro help at all in producing the chart you need?

Sorry, more questions than answers but, as you have experience writing for FoxPro, that might be a better tool than Excel.

regards

The Tetrarch

rock22 wrote: »

Hi Tetrarch,
I came across your post by accident. I have no great knowledge of any of the software you are talking about so probably cannot help much but I find your 'project' very interesting.
(1) Firstly , are you looking for help in the analysis of the data in Excel or do you just require a chart/diagram to show others?
(2) Is there a reason you cannot use TesioPower to expand the chart you have posted already?
(3) Are you entering the data on additional horses into FoxPro or into TesioPower/ If the former, can you write short program to match entries while ignoring punctuation marks? Or , if data is in a text file such as csv, could you run a simple windows program to add appropriate punctuation?
(4) Finally, Can FoxPro help at all in producing the chart you need?
Sorry, more questions than answers but, as you have experience writing for FoxPro, that might be a better tool than Excel.

regards

(1) are you looking for help in the analysis of the data in Excel? No
or do you just require a chart/diagram to show others? Yes

I would like to design a pedigree chart, probably six generations (or seven) that shows the 126 ancestors of a horse (2+4+8+16+32+64=126), but also to be able to highlight inbreeding groups.
......................................................................................................

(2) Are you entering the data on additional horses into FoxPro or into TesioPower
The data is in Excel, but I am moving it between Excel and FoxPro back and forth.
It will end up in FoxPro, then I will test each of 680,000 horses to see if I can generate a mini-pedigree for each (say 1+2+4+8+16=31 horses).
If I get a number less than 31 I know there is a bit of the pedigree missing. Then I research and fix that.

What is appropriate punctuation is a problem. You can not globally replace things.
DONT TELL NAN - DON'T TELL NAN
SLEW OGOLD - SLEW O' GOLD
AVERTI - AVERTI (3)
MARGRETS GIFT - MARGRET'S GIFT
TOBOGGANS GIFT - TOBOGGAN'S GIFT
CHEAP N CHIC - CHEAP 'N CHIC
CEST LA VIE - C'EST LA VIE
The corrected version is on the right, but it may not be the correct name.
Often the name is registered without punctuation.

Why Averti (3) ? There were two horses named Averti in 1991. The name in the database i.e. the full name / recognised name, is the name + year of birth, so to differentiate between AVERTI1991 (female, born in the USA) and AVERTI1991 (male, born in Ireland) I use AVERTI (2) AND AVERTI (3). The (2) and (3) are the Bruce Lowe female family numbers (don’t ask).

Problems with abbreviations:
People love to abbreviate: ………… A TIME 'N A PLACE; ABILITY 'N DELIVERY; ACES 'N KINGS; ADVENTURE 'N SPEED; AGAIN 'N AGAIN; AGES 'N STAGES; AHS 'N UMS …… A LITTLE LOVIN; AINT MISBEHAVIN;ALS DARLIN ...
I do not have the time to look up each name in the American, GB/IRE, Australian, French stud books so have standardised the N problem with 'N (not 'N', N, N'.)

A portion of the “punctuation” correction is adding Bruce Lowe family numbers to names – the existing database is “correct”, the data coming in is not.

If I allowed two horses named AVERTI with year of birth 1991 in my data every pedigree I run could pick up the incorrect set of parents, grandparents, and older ancestors.
......................................................................................................

(2) Is there a reason you cannot use TesioPower to expand the chart you have posted already?
TesioPower is a commercial software package. I can not modify it.
You can move left and right on the pedigree chart above in TesioPower, back twenty generations if you like.

The TesioPower version I use dates from 2000. I have not upgraded because during an upgrade they moved from .dbf database files.
I was (and am) using FoxPro for analysis and that uses .dbf files.
Another reason I did not upgrade was my data was better than their data.
I never took their data updates after my first experience of that.
In the late 1990s I sent them a disk with 26,000 horses and fifty years of IRE, GB, FR, GER, ITY Group race winners with six generation pedigrees to help their product. Their data was very poor, duplicated names, misspellings, gaps in pedigrees. My data today is 620,000 horses, very clean.
I almost never use TesioPower now, except to look at a pedigree. Almost all my data gathering and checking is done in FoxPro.
I have written programs to identify gaps in pedigrees, females as sires and males as dams, wrong Bruce Lowe numbers, duplicate records ...

Designing a chart in Excel is not a big task, but producing an aesthetically pleasing chart is the aim with good control over highlighting, font, font size, underlining.
It would have to fit an A4 page, with a header, and explanations below.

The programs I wrote to analyse pedigrees in FoxPro fills a database to 6 generations (also have 7,8,9,10,11,12 generation programs but not using them), marks duplicated horses, identifies inbreeding groups, categorises these groups, puts the analysis to a results file.
Writing a pedigree generation program took two nights back in 1993.
Writing programs to analyse the features/nuances/subleties in pedigrees is a bigger task.
My idea is to save the generated pedigree database file to Excel and use that as the pedigree chart source.

The commercial pedigree package I use, TesioPower, highlights duplicated horses, but it highlights all duplication groups.
......................................................................................................

(3) Finally, Can FoxPro help at all in producing the chart you need?
I have never used the menu system on FoxPro, or designed input screen or reports. I probably did about twenty years ago, but a FoxPro report would be too inflexible.
I want to show a full pedigree, highlight one or two inbreeding groups, not all groups. And then I would prepare a mini-pedigree of those two groups showing how they have ancestors in common (i.e. back in the 7th, 8th, 9th generations they are connected).
An example of these hidden connection is in the pedigree chart of Nearco above.
The mare Quiver (by Toxophilite (light green)) and the sire Musket (by Toxophilite (light green)) are actually 7/8 siblings, a very desirable feature.
If I did a mini-pedigree with these two horses, one as the sire, one as the dam, it would be visually obvious that they are very closely related.

I would like to write a book explaining the work.
To do that it would be best to explain the different inbreeding types, their effects on running ability.
I used horse ratings to do this, but I do not own the ratings. I would need permission from about ten sources to publish.
The work follows the work of two New Zealand pedigree analysers, both now deceased, who published books in the 1950s and 1990s.
My data is between 100 and 800 times the volumes they used, and I think would be of interest to a few breeders.
Books on the subject have print runs of perhaps 500 or 1,000, probably self-published.
Last week I bought a horse book that is on the market for months. My copy is 15 of 250!
......................................................................................................

At present I am trying to prove by breeding and racing a horse the lessons I learned from analysing inbreeding in pedigrees.
It is not going well. The first mare I bought has been to stallions seven times in two years without getting pregnant.
The second mare I bought is in France, has visited a sire, and may/may not be pregnant. If she has a foal in 2020 it could first run in 2022 or 2023.
https://www.boards.ie/vbulletin/showthread.php?t=2057770421&page=13

rock22

Beauf has a lot more experience than I have and makes some very good points.

In relation to adding punctuation to names, is the issue to to ensure the new name is added to records with the old name? In entering your data, from excel to FoxPro, could you have a routine that strips out punctuation and then makes comparison to see of the same.
This would only be called on names in FoxPro that contain punctuation

something like (pseudo code) below

strippedName- = strippunctuation(nameinFoxpro);

if (newentryName == strippedname)

{
// ask user to confirm identity
// display names and ok button to confirm
}

If accepted then the name newEntryName is added as is but is linked to the correct records?


I don't know enough about FoxPro unfortunately. Perhaps, in line with Beauf's input above, you would consider moving to a more up to date database product with better reporting /charting capabilities? I don't know enough to recommend anything.


Finally, could you contact one of the mods here and ask then to move this topic to it's own thread? It is lost here in the middle of another thread about excel - if it was on its own thread I feel you might get much more help.
Good luck with it all

The Tetrarch

beauf wrote: »

The system shouldn't be relying on names to match up relationships. You kinda don't but do...
I'm thinking you need to re-design of the entity–relationship model (or parts of it) to better define relationships adhering to good database principles.
Use of unique identifiers, numeric and alpha numeric codes. Also better normalisation of tables etc. Basically a more robust than your "Bruce Lowe" method which I'm thinking doesn't go deep enough.

The thoroughbred breed started in the late 1600s.
About a century later, in 1791, James Weatherby produced the first General Stud Book. He visited owners and gathered the details from their private stud books. The reason for a general stud book was to prevent claims to parentage that were incorrect.

At that time and from then into the mid 20th century there were no unique identifiers, numeric or alpha numeric codes. There were no computers.
It was common practice not to name horses, and those not named would be known as for example "Beningbrough mare".
I have fourteen unnamed Beningbrough mares in the database. I have 26 mares in the database named Maria.
It was well into the 20th century before it became a requirement to name a horse. A horse running in a race could be known as "Lord Derby's colt".

I have four horses, two are named, two are not named.
Why are they not named? One is a yearling who will go to the yearling sales in September and her new owner will name her before she races.
The foal is only three weeks old and is unnamed for the same reason.
All have passports, identity documents in Weatherbys (white markings, hair whorls), and DNA recorded.

The average age of the parents of a newborn foal is 11 years.
The sixth generation ancestors of a foal born today will have 6th generation ancestors with average year of birth 2019-66 = 1953, but of course some parents can be in their 20s when mated.
My mare born in 2012 has one 6th generation ancestor born in 1931, 81 years earlier, well before computers, databases, Codd's relational database rules 1970.
You use names to identify horses, and their parent names.
The date of birth of the horse is a tie breaker, and if that does not work then the Bruce Lowe number is useful.
The official way to differentiate horses is by a country code suffix.
This helps if the same name is used in two countries.
Many original thinkers named their mare LADY DI, LADY DIANA, PRINCESS DIANA, THE PEOPLE'S PRINCESS.
You get waves of identical names. The same name can not be registered twice in one stud book (IRE/GB) in a short time span, but that name could be registered in GB/IRE, USA, AUS, NZ.

There are 86 country codes (IRE, GB, USA, CHI, CHN ...) but these are often omitted in common media sources.
Again, country suffix codes are relatively recent.
Previously if a foreign horse ran in IRE/GB if was given a suffix II e.g. Sea-Bird II, although he was not registered in any stud book as Sea-Bird II.

There are other difficulties with pedigree analysis.
An old example is the 1880 English Derby winner Bend Or.
A groom claimed that by accident Tadcaster and Bend Or were mixed up, and Tadcaster won the Derby.
But the owner was the richest man in England, and the investigation concluded Bend Or won the Derby.
DNA testing in the 21st century proved it was Tadcaster.

That might seem irrelevant, but there is doubt over the parentage of many famous horses due to mares sent to studs where more than one stallion was in residence.
If the owner is away then you are relying on poorly paid staff.
And then it was (and is) common practice to cover a mare weeks after she was covered by a stallion if it appears she is not pregnant. Often the second cover is by a different stallion.

The reason I based my analysis on 159,000 horse ratings was to rise above the errors common in pedigrees and ratings.
Years back I give every horse a unique four digit identifier in an attempt to speed up programs, not to improve identification. The advantage was miniscule.

The Tetrarch

It is a bit strange that a few lines at the start of post #23 where I mentioned as an aside that I was doing a clean up of punctuation has resulted in everyone ignoring the reason I came to the thread, a better way to visually display inbreeding.

rock22

The Tetrarch wrote: »

It is a bit strange that a few lines at the start of post #23 where I mentioned as an aside that I was doing a clean up of punctuation has resulted in everyone ignoring the reason I came to the thread, a better way to visually display inbreeding.

Hi Tetrarch

Sorry you have found my posts unhelpful.
Perhaps you might get more acceptable responses from others