Hobnail spreadsheet software for gear trains

[Adam HarrisParticipant @adamharris13683]

My reason for wanting to see a gear train of 6 gears is that the large set of 64 gears belongs to a small hobbing machine I have (too small for the size of helical gear I am interested in making), whereas if at all possible I would like to use a much smaller set of Schaublin gears as my mill is already set up with quadrant/studs and dividing head for the bore of such gears. Hence a quick calculation of possible gear trains of 6 gears would be nice

[Adam HarrisParticipant @adamharris13683]

I have received authorisation from the program creator Gareth Evans to distribute Hobnail to those interested. PM me with your email address if you want a copy.

[Adam Harris]

On Adam Harris Said:

…a quick calculation of possible gear trains of 6 gears would be nice…

Before you go too far down that path, perhaps do some rudmentary calculations on the number of possiblities you have to consider, and thus the time it will take to perform them.

You can calculate the same numbers for your four gear train and see the factor by which the calculation time will be multiplied between the four gear time and the six gear time (given that four gears take two seconds).

Start with something simple, so you understand the maths. Look up combinatorics or ‘n choose r’ (https://www.hackmath.net/en/calculator/n-choose-k). If you have a bag with four gears in, there is only one way of pulling four gears from that bag. From those four gears, you have to select two of them (as the drivers). There are 4 choose 2 = 6 ways of doing this. Hence you have six possible ratios from four gears.

Now look at the 64 gears you have. There are 64 choose 4 = 635376 ways of choosing four gears and you get four choose two equals six possible ratios from each of those, so you have to consider 635376 x 6 = 3,812,256 total options. That takes two seconds.

Now move onto a six gear train. 64 choose six is 74974368. Six choose three is 20, for a total of 1,499,487, 360 possibilities.

1499487360 divided by 3812256 is 393, so what was a two second calculation is now a 787 second (13 minutes) calculation.

You can refine this number downwards (let us be optimistic and say you can halve it), but anything you do with a spreadsheet will be painfully slow.

[Adam HarrisParticipant @adamharris13683]

The program is written in Visual Basic for Applications. Using 58 gears it computes the best two combinations of 4 gears (combination more than and combination less than target ratio) in 1 second aprox. It would be interesting to see the speed of calculation for a 6 gear train if anyone can modify the program accordingly. As a hobbyist very happy to wait 5 -15 minutes for the calculation, no pain at all!

[Adam Harris]

On Adam Harris Said:

The program is written in Visual Basic for Applications. Using 58 gears it computes the best two combinations of 4 gears (combination more than and combination less than target ratio) in 1 second aprox. It would be interesting to see the speed of calculation for a 6 gear train if anyone can modify the program accordingly. As a hobbyist very happy to wait 5 -15 minutes for the calculation, no pain at all!

I’ll have go please, also interested to see how hobnail solves the problem.   I’ll PM my email to Adam.

Computers are very fast at this sort of job, though they might bog down if the number of gear combinations is large.    My python program for calculating lathe change gears, starts by generating all the 4-way permutations possible from the 11 gears provided with a metric WM280, or the 13 provided with the Imperial version.  Written to be obvious how it works, not optimised for speed.

Performance:

• For accuracy I convert the ratios into fractions, which are slow compared with floats, but even so generating and crunching all 17160 Imperial gear permutations is done in a smidge under 50milliseconds.
• Then I remove duplicate permutations, which can occur in gear sets containing duplicate gears, bringing possibles down to 12540, taking 10mS
• Finally, I sort the result (12540 records containing the tooth counts of gears a,b,c,d and the resulting ratio) into ratio order, taking another 50mS

In theory metric is less work for the poor old computer, because fewer change gears reduce the number of permutations to only 7940, but in practice the program runs in much the same time.

I’d expect VBA performance to be in the same ball-park.

In any language, it takes much, much longer to print the results!

Dave

[DC31kParticipant @dc31k]

In the spirit of enquiry, it might be useful if Adam could post the list of 64 gears he has. If he has this in the boot of his hobnail, that should be a simple copy ‘n paste.

Please also post some sample ratios that you have calculated or require calculating and the 4-gear results you have obtained.

This will verify that the software you have is behaving correctly and also check that it is indeed producing the closest possible solution to the desired ratio.

We can then run the same ratio through a 6-gear progam to see how long it takes and how much ‘better’ results are obtained.

However, this offer does come with conditions (quid pro quo). Choose one of the following options:

(1) present calculations showing the real world difference between the closest four-gear train and the closest six-gear train. If we were approximating a thread pitch, we would talk in terms of (for example) 1mm error in 437metres of thread.

(2) cut a gear using each train. Measure the difference between the two gears and tabulate the results.

[SillyOldDufferModerator @sillyoldduffer]

Adam kindly emailed me a copy of hobnail last night and I looked at the code.

I was hoping to find it written in a way making it easy to modify the calculation to accommodate a 6 gear rather than 4 gear compound,  and then allow me to patch in some timing statements to measure performance.    Unfortunately, more work than I care to do out of curiosity:

• the VBA code is event driven by changing various cells in the spreadsheet, so there isn’t a single simple interface to a number-crunching function.
• the number-crunching is done by ‘for’ looping through a deep list of nested if statements, ending in the 4 gear calculation.   Not obvious to me what new ‘if’ blocks would be needed to do 6 gears, or how the ‘for’ loops should be modified.

By default the program arrives set up on the assumption that the hobber has 71 change gears available in increments of 1 tooth, from 30 teeth to 100 teeth.   Having the full range available makes it much easier to find a close match, but real hobbers are likely to come with a much smaller gear selection.   The user is expected to delete gears he doesn’t have from hobnail’s “Preferred Gears” table.   Begs the question, “what’s the smallest range of change gears needed to hob most gears?”  My gut reaction is that this is more difficult than identifying the minimum set of change gears needed by a lathe to produce most pitches/TPI.

To reiterate what Adam and others have said, hobnail isn’t a lathe pitch calculator.   Instead it calculates change gears and other parameters for a hobbing machine, possibly one model in particular.   (I don’t know enough about hobbing to confirm that the code is suitable for all machines.)   Two compounds are needed, one for the indexer, the other for the feed.

Helpful to see what hobnail looks like:

Note that the program isn’t entirely deterministic – the operator is invited to play with the F starting value to get a better match.  Also, not shown for space reasons, it calculates different ratios for the other hand.   This is beyond my understanding of hobbing!

Although hobnail is implemented with VBA inside an Excel spreadsheet, implying it will only run on MS-Windows with Excel installed, I tried it first on Linux and LibreOffice Calc (also available free for Windows).   Pleased to report hobnail appears to work correctly without needing Microsoft.

Dave

 

 

 

 

[Adam HarrisParticipant @adamharris13683]

I haven’t tried out any of the buttons other than the top brown one “Free Hand Gear Calculator” for ratios and what I really like is that with the excel format you can have your various sets of change gears from different machines listed on different sheets (and any combinations you choose thereof or with theoretical additions to your sets, on different sheets) so you can simply cut and paste into the “Preferred Gears” sheet which is the one used for the calculation. Thus you can compare your different options for closeness to the target ratio you have set. You still need to calculate the target ratio manually for this, in the case of helical gears the how-to-do being spelt out very clearly in johnfsworkshop.org (“making helical gears on a vertical milling machine”). Furthermore your user defined set of gears do not have to be listed in any order and for duplicate gears you just enter the gear twice – simples. I am curious how much closer one could get with a 6-gear train calculation

[Adam HarrisParticipant @adamharris13683]

If it is of any interest Dave, he told me “I remember having to use double precision floating point because single precision was not accurate enough”, but other than that he couldn’t remember much about it that is not in the program notes

[Adam HarrisParticipant @adamharris13683]

I did copy and paste the entire code to ChatGPT requesting VBA amendment to a 6-gear train calculation  and I got a long list of code back within a couple of seconds , cut and pasted that code into Hobnail and nothing worked 🙂 . Of course I know nothing about software so almost certainly did it all wrong but I did think that coding was a strong point of AI / ChatGPT

[SillyOldDufferModerator @sillyoldduffer]

Gareth using double rather than single precision floats was sensible because it much reduces the risk of significant errors creeping in.    On my computer ⅓ is float  0.33333334, whereas double gets another 9 digits of precision = 0.3333333333333333.  The hobnail code is well-written and I guess Gareth has professional experience.  I used Python’s Fraction type because it’s easy to use and eliminates floating point error entirely.   Always a catch of course : it’s that calculating in fractions is much slower than floating point.

ChatGPT? Smart move, well worth trying!  I’ve used it on Python with mixed success.   When it didn’t get the answer by refining my question, I noticed it was learning from me. I tested suggestions and explained why the answer was wrong, and ChatGPT assimilated the improvements, getting a little closer each time.   Pretty sure human improvements are added to the neural-net, allowing ChatGPT to apply them to other problems later.

ChatGPT may have struggled with hobnail because there are three interlinked problems to solve : interfacing with the spread-sheet; understanding Gareth’s hobber set-up logic; and doing the maths.  Plus the human element – ChatGPT’s code has to be pasted back into the spreadsheet correctly, which is probably fiddly.

Dave

[Author]

Posts