Pen Kits and Bushings Excel Workbook

Signed-In Members Don't See This Ad

Jehster1

Member
Joined
Nov 11, 2019
Messages
64
Location
Washington DC
So, because I now have free time and idle hands are the devil's playground :) ... I created an Excel workbook based on the 20 company chart Wayne put out in 2016 (found in the IAP Library). I imagine Wayne and others took an extraordinary amount of time to gather the information and make the chart. I just used that info to create a format that worked better for me. I imagine some others may find it helpful as well.
I did this originally to track the different pens I was working on and was tired of having random notebook pages scattered about. I needed a way to stay organized and a quick reference sheet to return to when I invariably got sidetracked. I came up with a form I could quickly print out that had the info I needed on it. That made me wonder if I could add some automation to the process which led me to the IAP chart which led me to over-engineering this whole thing lol.
All of this information (and more) can be found on the IAP App but since I do most of my research/thinking on my desktop, I wanted a way I could print up a working ticket and take it into the shop where I can write notes or make adjustments as necessary. I don't use bushings, everything is turned between centers directly on the live/dead centers, and I micro it when I get close so I just hang the ticket above my lathe for a quick reference sheet on measurements. I also included the pen ticket blanks I use while working on a pen.

Breakdown:

Info Sheet
- Just a blow-up of the graphics used on the Master List sheet.

Master List - This is the main info dump. This is a sortable version of the complete chart. To sort, just use the drop-down arrows in the table header cells. For example, you can use it to find compatible bushings. You want to know what other pens you can make with your 0.331" bushing, sort one of the bushing columns numerically and it will sort everything by bushing size. You can also see what bushing might be "close enough". Another Excel trick would be to use the drop-down arrow, uncheck the 'Select All' button, and then only check the size you want. It will then only display results from that selection instead of the whole list. To get all the results back again, just use the same drop-down arrow and check 'Select All'.
Every column can be sorted.
The list begins sorted by Supplier (alphabetical) and Kit Name (alphabetical). To return to this default state, first drop-down Kit Name and Sort A to Z THEN drop-down Supplier and sort A to Z.

Pen Search - You can pull up the info for a single pen or a couple of them instead of looking at the whole list. Click on any cell in the Kit Name column and you will see a drop-down arrow that will open a list of kit names, choose what you want and the rest of the fields will auto-populate.

Bushing Finder - 2 tables that give quick info on bushings. First one by Supplier - Pen Kit - Bushing part number (if available). Second table by Supplier - Bushing Part Number - Which pens they fit.

Pen Ticket Full - A working ticket that you can print up and put wherever it is convenient for reference as you work on a pen. If you use the drop-down box to fill in the Body Kit, it will auto-populate the Drill Size Body, Drill Size Cap, and all the measurements as listed in the chart in the Listed Measurements section. A good habit is to verify those measurements yourself and write the info in the Actual Measurements section.

Pen Ticket 2X - This is originally all this workbook was for. Simple ticket 2 per page, print up an cut in half, fill out as you measure. I did add the ability to use the Body Kit drop-down to fill in the kit name and it will auto-populate the Drill sizes from the chart. I mainly just print up a bunch of these blanks and keep a pile in the shed-shop.

Disclaimers: This info is only current as of 8/2016 when the chart came out. If anyone has a more updated chart, I could add it. If there's interest I may set up a GoogleDoc so people can add the relevant info and I can update and give out that version. I am by NO means an Excel expert but taught myself a few things when I re-wrote the Check-In/Out process and Payroll spreadsheets at work (long story lol). Gotta love Google!

Possible Future Ideas: Updates? Inventory and sales tracking?



Charlie

Files deleted - updated versions coming soon...
 
Last edited by a moderator:
Signed-In Members Don't See This Ad

henry1164

Member
Joined
Jan 3, 2020
Messages
97
Location
Webster, NY
Wow! Very nice. I'm no database expert but this looks like it will be everything I would ever need....in one document. I appreciate the effort. Thank you.
 

magpens

Member
Joined
Feb 2, 2011
Messages
13,300
Location
Coquitlam, BC, Canada
@Jehster1

I applaud you for doing this, Charlie, even tho' I haven't tried it out yet !!! . Thank you very much !!

Now how about this idea .... you give a podcast on how to use this, eh ?

Somebody else .... see today's threads .... is starting a podcast for pen turners. . So you could be one of the first presenters.

Also, I have a question. . Is there a freebee version of Excel that this would work on ? . I don't have the Excel software.

Or, are there alternative software packages that this would work on ?

I have to confess that I am not much for learning new software .... not much for managing data, emails, etc., either.

But I am very much into pen making, so if this can help in any way, I would be very interested in learning how !

For someone who knows nadda about the nitty-gritty of computers and software, what would be the first thing to do ?

First off .... what's a "Ticket" ? .

And what do I click on to get a very, very basic idea, knowing only that Excel exists but never used it or any other ..... spreadsheet ? ..... is that what you call it ?

When I click on the first thinga-ma-jig (of the three above), I don't seem to get anywhere. . Oh, I get asked how I want to open it, and I choose Firefox, but after that repeats twice ... nothing much happens ... yeah, I get asked if I want to select an "App" ... and I don't know what to do. . Sorry that I'm so dense ! . I don't think I even know what an "App" is. . You see, I don't even have a cell phone ... and don't even know if one would be relevant to this. . I guess "App" just means some sort of "Application" .... but not even sure if that is correct. . After that I am stuck.
 
Last edited:

Curly

Member
Joined
Nov 20, 2010
Messages
3,963
Location
Saskatoon SK., Canada.
Mal Apache Open Office is a free suite of programs including a spreed sheet.


It might also work for Dan's way of designing pens in Powerpoint in another thread.

 

its_virgil

Member
Joined
Jan 1, 2004
Messages
7,252
Location
Wichita Falls, TX, USA.
Curly mentioned Apache Open Office. I will add that it is a MS office work alike and is totally compatible with MS Office. I switch between the two often since I do not have MS office on all of my computers. I mostly use the word processor (WORD), spreadsheet( XL) and presentation (POWERPOINT) parts of both interchangeably.

Also, GIMP is a PhotoShop workalike. I also use both with PS on one computer and GIMP on the others.
Do a good turn daily!
Don
 

jeff

Administrator
Staff member
Joined
Dec 5, 2003
Messages
8,358
Location
Westlake, OH, USA.
Just a couple thoughts on this... Wayne compiled these over many years and resisted calls to release them as spreadsheets, instead opting for PDFs. The reason was to maintain one authoritative source of the data. To provide more convenience than PDFs provide, apps were developed that use the same underlying data. I believe that once these are widely distributed as spreadsheets, many versions will evolve as various people make their own additions and corrections. Lacking version control, I don't see how the integrity of data will be maintained. I (nor IAP) doesn't have a dog in the fight, we certainly don't own the data. I'm just interested in providing useful, accurate data, and this is more a caution to users of the data to make sure that what you're using comes from a reliable source. Of course, we could provide an 'official' excel version, much the way open source software is managed, but that's a decision for Wayne to make.

Comments welcome!
 

Curly

Member
Joined
Nov 20, 2010
Messages
3,963
Location
Saskatoon SK., Canada.
Jeff I thought that was the case and have seen people posting excel versions on Facebook without giving credit to the author or the origins and don't care if the work was poached. There is a lot of that out there unfortunately. I have the app for my iPhone and use it occasionally. Is there anyway to easily make a desktop program that can't be changed or added to? That would seem to be the best way to maintain the integrity and control over the content. I'll continue to use the app until such time as the IAP have an official spreadsheet/program for my iMac.
 

Edgar

New Member Advocate
Joined
Feb 6, 2013
Messages
5,921
Location
Alvin, TX 77511
Jeff I thought that was the case and have seen people posting excel versions on Facebook without giving credit to the author or the origins and don't care if the work was poached. There is a lot of that out there unfortunately. I have the app for my iPhone and use it occasionally. Is there anyway to easily make a desktop program that can't be changed or added to? That would seem to be the best way to maintain the integrity and control over the content. I'll continue to use the app until such time as the IAP have an official spreadsheet/program for my iMac.

The most recent and official data is stored on the IAP server. The iPhone & Android apps access that database and automatically download updates. At the same time, Wayne manually produces pdf files for the library.

Ideally, desktop access should be via executable apps that also access the database files on our server. I don’t do desktop apps, but if someone does, I will be glad to assist them in the app design. Naturally, we would want both a Windows and a MAC version.

Edgar
 

Jehster1

Member
Joined
Nov 11, 2019
Messages
64
Location
Washington DC
Hey everyone, thanks for the positive response to this. I actually just had a GREAT conversation with Wayne and Jeff of IAP regarding the spreadsheet I made. They support the idea and would like me to develop it further with some more enhanced functionality with a FAQ sheet on usage and I'm more than happy to do so. That said, I'd ask that Jeff (or someone who can) pull this version and I will work on releasing an updated one with the capabilities we brainstormed. I look forward to it!
 

MyDadsPens

Member
Joined
Nov 10, 2019
Messages
41
Location
Corning, New York
Couple of thoughts...
A free Google email account gives a person free use of Google sheets which is essentially Microsoft excel, it can even read and save an excel file. A master file can be stored on Goolge drive. You probably could protect the master a little (nothing can truly be protected these days) or have watermarks or such to indicate a sourced version. obviously because Google is web based it will work on windows, mac and android.

One thing I always thought missing from the original database was a list of known doppelgangers - for example a woodturnigz Saturn is a PSI Trimline; a Woodcraft wall street is a mesa, sierra, etc (in fact I was just about to start a new thread for this - unless someone already knows of an old thread or resource with this info - the main purpose of this twin info is to salvage parts, for example do I already own bushings I can use for a new kit I just came across; or I just came from a yard sale where I picked up 50 brass tubes for a Franklin pen kit but can those tubes be used for a different kit I might already own
 

Jtvinny

Member
Joined
Mar 30, 2015
Messages
5
Location
NC
I can't wait for the update! Now that I have more time I was starting to go thru all my rogue bushings and pair them up.
 

hmblaisdell

Member
Joined
Jun 30, 2018
Messages
6
Location
Pa
Any reason since you are using an iPad you aren't using the Bushing and Tube app? I think it is a buck or so.
Yes there is, I have several sets of bushings that I do not know what they belong to. I would like to get the Excel file so I could sort it by size. I can then check the sizes of my bushing against the spreed sheet. I would like to think I could then determine what my extra bushing are for.

Harold
 

Curly

Member
Joined
Nov 20, 2010
Messages
3,963
Location
Saskatoon SK., Canada.
You can do that with the app by typing in one or both of the bushing diameters and it will list all pens with that diameter.
For example. Type .480 .563 .429 .357 and the Tycoon Screw Cap RB or FP shows up. A little harder if your measurements are a thou or two different( because of bushing wear) from the info in the app but it narrows the search a lot.

I'm not a great spreadsheet user so I don't know if you can apply a tolerance to the search but the app will help sort most at least.
 

hmblaisdell

Member
Joined
Jun 30, 2018
Messages
6
Location
Pa
You can do that with the app by typing in one or both of the bushing diameters and it will list all pens with that diameter.
For example. Type .480 .563 .429 .357 and the Tycoon Screw Cap RB or FP shows up. A little harder if your measurements are a thou or two different( because of bushing wear) from the info in the app but it narrows the search a lot.

I'm not a great spreadsheet user so I don't know if you can apply a tolerance to the search but the app will help sort most at least.
In excel you can sort by any column Or multiple columns. I could sort by the drill diameter. You could then sort by the ’Upper Cap Bushing Diameter etc. Each time you sort would get you to a point you see where your bushing size fits within the sorted list. If the list is quite large you can take this smaller list and sort it by one of the ‘Lower Bushing Sizes’.
Harold
 

edman61

Member
Joined
Aug 16, 2020
Messages
7
Location
Mesa, AZ
I was given a variety of WoodCraft pen kits that are about 20 years old. I have been unable to find bushings sets 06R07 or 06S89 which many of the kits require. Do you know if it possible for me to search the IAP app to find equivalent sized bushings that might belong to other kits? I downloaded the App, but have not been able to figure out how to search by bushing diameters. Thanks in advance.
 

edman61

Member
Joined
Aug 16, 2020
Messages
7
Location
Mesa, AZ
I was given a variety of WoodCraft pen kits that are about 20 years old. I have been unable to find bushings sets 06R07 or 06S89 which many of the kits require. Do you know if it possible for me to search the IAP app to find equivalent sized bushings that might belong to other kits? I downloaded the App, but have not been able to figure out how to search by bushing diameters. Thanks in advance.

I figured out how to search by bushing diameter, so will keep at it.
 

edman61

Member
Joined
Aug 16, 2020
Messages
7
Location
Mesa, AZ

Jehster1

Member
Joined
Nov 11, 2019
Messages
64
Location
Washington DC
Well, I figured

Thank you. It’s now on order!

As for your 06S89 issue, you could always just TBC (Turn Between Centers) no bushings needed. I don't know if you are familiar with that so I'll give a quick rundown:
Cut blank to size
Drill hole for tubes
Glue in tubes
Square blank to tubes
Measure the kit where the blank will mate with it using digital calipers. I use these because they have an auto-off feature:
Write those numbers down.
Mount tubes directly on the 60 degree live and dead center and turn until you hit those measurements. If applying a CA finish, I subtract @.12mm from the measurement and aim for that to account for CA buildup.
Tip - Start with the biggest measurement first that way if you shoot past it, you just make that end the smaller one :) (don't forget to subtract the CA buildup if applicable) and just lock your calipers at that measurement to check when needed. Calipers should just barely slid over the end when finished.

This is how I do all of my pens now, I haven't bought bushings in a while.
 
Top Bottom