The "reboot" seems to be something of a thing in Hollywood these days, and so it only follows that Life would imitate "art" as I reboot my Lego database.
For those who have been following this blog for some time, I think you may be aware of my Lego database project. It is one component of "42", my personal, ultimate repository of those possessions of mine which I have chosen to catalog.
I've just started using Microsoft Office 365, and the Lego data came from rebrickable.com. Their dataset is very comprehensive, but I don't think I've ever downloaded ANY Lego dataset that was usable by me as downloaded... and this data is no exception.
I mentioned that the data is quite comprehensive, which means it contains things which I don't necessarily need or want. For example, I do not care about any decals, paper or cardboard items, books, or even certain parts of the Lego product line. So, these need to be removed. The data also appears to come from more than one source, and formatting is necessary. Punctuation needs to be removed from many entries, and part names need to be standardized. Some data needs spelling changes- there are cases of the Queen's English being used, so "windscreen" and "tyre" need to be changed to "windshield" and "tire". These are the major changes that need to happen before I can even think about exporting to an Access database. And these are just a few examples.
But, I digress. Here are the numbers as of December 4th.
When I first started out, there were approximately 29,000 records. As of last night, after culling out items which I knew I would not be inventorying, I had 27,164 unique items. When I deduped the group to include ONLY the base part numbers- excluding all decoration variants, I ended up with a working list of 8,747 unique base part numbers. As I copy the part descriptions to the new inventory list, they are being further culled.
I am now at the point where everything must be done by hand. I find myself going back and forth between rebrickable and my flat database, verifying that the part number in the list is a part that I (may) actually own... and want to count. At a certain point, some of the data is subjective, and even though it is valid, I will count a complete assembly rather than, say, a special tile, wheels and tires as separate parts.
As always, I am hochspeyer, blogging data analysis and management so you don't have to.
Showing posts with label Excel. Show all posts
Showing posts with label Excel. Show all posts
Monday, November 19, 2018
Tuesday, November 8, 2016
Don't just do well... Excel!
I'm going to talk a bit about some of my favorite Excel tips and tricks in a bit, but first I'd like to explore a certain fantasy of mine.
In another, more perfect world, I'd be an acclaimed, excruciatingly well-paid (and well-off) director of cinema with such an impeccable track record, I could write my own ticket for ANY movie. In this "perfect" world, I would have unlimited finances for my signature movie: a fantasy... or possibly sci-fi epic. This movie would be so epic that highly regarded movies such as "Gone With The Wind", "Citizen Kane" or "The Godfather" would pale in comparison to my movie. Sci-fi and fantasy standards such as Harry Potter (all of them), Star Wars (the complete series), Star Trek (the original TV series, every TV variant, and ALL of the movies), Blade Runner, the Peter Jackson LOTR epic or any other classic or standard one could think of would be mere footnotes in the story of the greatest fantasy epic ever filmed.
It would be so awe-inspiring, in fact, that, that Congress would unanimously pass the "hochspeyer Cinematic Protection Act" which would make the mere mention of Sting or Dune on my property a Class A misdemeanor, as this aberration of a movie could seriously undermine the morale of everyone associated with my movie, which in the law would be defined in the wording of the law as "property theft over 1000USD" (Legalese, in my own words).
In fact. my movie would be so perfect that I'd have to pay the leading actors and actresses of the day NOT to come and waste everyone's time by coming to my casting call, because I'd already have hand-picked my cast. I do know one thing, though: the soundtrack has already been recorded... in my mind. All of these songs are already in my collection. These songs would be so perfect for my movie that when I'd announce them, the folks who own the rights to the songs would would give me 75% of the royalties, and record companies would gift me their entire catalogs just to get one or two more tracks included. In my mind, ...
(hint: read the following in Superhero Speak)- that's Just. How. AWESOME (my movie will be). But... back to the soundtrack.
I'm actually a bit conflicted as to exactly what I should use as the main theme orchestral music, although I'm leaning pretty heavily towards Sibelius' Symphony #1. Why Sibelius... or, probably better, who is Sibelius?
Jean Sibelius is, from the narrative on the linked site above, a hero of Finland. I'm not an expert on classical music- and I'm using the term "classical music" in the popular, rather than the technical sense- but I think of his works as a bit "lighter" than German composers, but not as "sweet" as French or Italian folks. There would be other classical pieces, of course. Orff's intense "O Fortuna" would be prominent, as would several other easily recognizable pieces.
There would also be rock... symphonic, metal, epic, the very best of the best, curated, so to speak, by me- lots of it. There would be some obvious choices (to me, anyway). Uriah Heep would contribute The Wizard, Lady In Black, Stealin', and Easy Livin', at least. From Styx, I'd have Lords of the Ring, Castle Walls, Born For Adventure and Snowblind. Scorpions add Send Me An Angel, No Pain No Gain. Nazareth provides Please Don't Judas Me. Axe gives us Silent Soldiers, and Jennifer. Moody Blues? Nights In White Satin and For My Lady. Pink Floyd gives us Breathe and One of These Days.
At this point, some purists may take offense, but I'm going with Guns N' Roses' version of Hair of the Dog. I've got nothing against the original- it's just that I like Slash's Day Tripper riff and Axl's laugh at the end of their cover. Speaking of GNR, November Rain and You Could Be Mine are on the list. Poison contributes Every Rose Has Its Thorn. Cinderella gives me Don't Know What You've Got and Nobody's Fool. From Bad Company, Bad Company and Seagull. Bruce Springsteen has Badlands. Deep Purple provides Burn. Kix' Don't Close Your Eyes is a must. Queen, of course, gives us '39, Don't Lose Your Head. There are probably several few dozen more, at least.
You're probably wondering how one (exceedingly long) movie could have so much music. Pretty simple, really: technology. On current blu-ray discs, the viewer can select the language, subtitles or not, and in some cases even the camera angle. My movie will probably be a direct to video release, and will allow the viewer to select the soundtrack based upon their preferences and my impeccable offerings for them to choose from- and they'll choose from a list of songs for any given scene, if they so choose.
Whew! That was a bit more lengthy than planned. And now, a few of my favorite Excel Tips and Tricks.
I don't know about you, but I use the Microsoft Office suite quite a bit... primarily Excel.
I'm not trying to be a Microsoft pitch guy, but their Office suite does offer a lot of useful business tools. I regularly use Excel, Word, Access and Powerpoint, in that order. Here are a few of my favorite Excel tips and tricks.
White text- I'm starting off with a weird "in plain sight" trick. I have a spreadsheet that I use fairly often, and I need to track a quantity manually. As this quantity is unimportant to anyone but me, I don't want to draw attention to it so I've made the text in the cell the same color as the background- white.
"CTRL + ~"- If you know nothing about Excel except for constructing simple calculations, this is a vital shortcut. For those readers who are fairly new to Excel users, when you're working in a cell, you can see what you're doing in a cell by looking at the formula bar. When you type "CTRL + ~", you will see what is in every cell in your active spreadsheet- this is great for troubleshoooting.
Conditional formatting- a very sweet tool. It can be used in any number of ways. In a spreadsheet I currently co-manage, I use conditional formatting to alert me to when I need to reorder printer supplies. I also use it to manage my personal overtime.
Lastly, a just for fun bit of formatting.
You can make even a boring spreadsheet look a bit nicer by getting rid of the grid in some places, "simply" by changing the fill color.
Lastly, the most important trick is NEVER, ever do this in a production spreadsheet... that's tech talk for a spreadsheet which you really care about or would be lost without: ALWAYS MAKE A COPY, and work in the copy. I often need to use the data in a .csv or .xls/.xlsx file, and the first thing I do is copy the file to my desktop, rename it as a COPY_currentdata.xlxs, and then work from the copy. Never use the source file, unless your organization prohibits file copying. In that case, seek guidance from I.T. For all others, make that copy, and then delete it when you're done. If you have hardcopies (as I do), please be kind and deposit them in the shred bin.
That's all for now. I hope you've enjoyed my little movie fantasy, and can use one or two of my Excel "tricks". As always, I am hochspeyer, blogging data analysis and management do you don't have to.
In another, more perfect world, I'd be an acclaimed, excruciatingly well-paid (and well-off) director of cinema with such an impeccable track record, I could write my own ticket for ANY movie. In this "perfect" world, I would have unlimited finances for my signature movie: a fantasy... or possibly sci-fi epic. This movie would be so epic that highly regarded movies such as "Gone With The Wind", "Citizen Kane" or "The Godfather" would pale in comparison to my movie. Sci-fi and fantasy standards such as Harry Potter (all of them), Star Wars (the complete series), Star Trek (the original TV series, every TV variant, and ALL of the movies), Blade Runner, the Peter Jackson LOTR epic or any other classic or standard one could think of would be mere footnotes in the story of the greatest fantasy epic ever filmed.
It would be so awe-inspiring, in fact, that, that Congress would unanimously pass the "hochspeyer Cinematic Protection Act" which would make the mere mention of Sting or Dune on my property a Class A misdemeanor, as this aberration of a movie could seriously undermine the morale of everyone associated with my movie, which in the law would be defined in the wording of the law as "property theft over 1000USD" (Legalese, in my own words).
In fact. my movie would be so perfect that I'd have to pay the leading actors and actresses of the day NOT to come and waste everyone's time by coming to my casting call, because I'd already have hand-picked my cast. I do know one thing, though: the soundtrack has already been recorded... in my mind. All of these songs are already in my collection. These songs would be so perfect for my movie that when I'd announce them, the folks who own the rights to the songs would would give me 75% of the royalties, and record companies would gift me their entire catalogs just to get one or two more tracks included. In my mind, ...
(hint: read the following in Superhero Speak)- that's Just. How. AWESOME (my movie will be). But... back to the soundtrack.
I'm actually a bit conflicted as to exactly what I should use as the main theme orchestral music, although I'm leaning pretty heavily towards Sibelius' Symphony #1. Why Sibelius... or, probably better, who is Sibelius?
Jean Sibelius is, from the narrative on the linked site above, a hero of Finland. I'm not an expert on classical music- and I'm using the term "classical music" in the popular, rather than the technical sense- but I think of his works as a bit "lighter" than German composers, but not as "sweet" as French or Italian folks. There would be other classical pieces, of course. Orff's intense "O Fortuna" would be prominent, as would several other easily recognizable pieces.
There would also be rock... symphonic, metal, epic, the very best of the best, curated, so to speak, by me- lots of it. There would be some obvious choices (to me, anyway). Uriah Heep would contribute The Wizard, Lady In Black, Stealin', and Easy Livin', at least. From Styx, I'd have Lords of the Ring, Castle Walls, Born For Adventure and Snowblind. Scorpions add Send Me An Angel, No Pain No Gain. Nazareth provides Please Don't Judas Me. Axe gives us Silent Soldiers, and Jennifer. Moody Blues? Nights In White Satin and For My Lady. Pink Floyd gives us Breathe and One of These Days.
At this point, some purists may take offense, but I'm going with Guns N' Roses' version of Hair of the Dog. I've got nothing against the original- it's just that I like Slash's Day Tripper riff and Axl's laugh at the end of their cover. Speaking of GNR, November Rain and You Could Be Mine are on the list. Poison contributes Every Rose Has Its Thorn. Cinderella gives me Don't Know What You've Got and Nobody's Fool. From Bad Company, Bad Company and Seagull. Bruce Springsteen has Badlands. Deep Purple provides Burn. Kix' Don't Close Your Eyes is a must. Queen, of course, gives us '39, Don't Lose Your Head. There are probably several few dozen more, at least.
You're probably wondering how one (exceedingly long) movie could have so much music. Pretty simple, really: technology. On current blu-ray discs, the viewer can select the language, subtitles or not, and in some cases even the camera angle. My movie will probably be a direct to video release, and will allow the viewer to select the soundtrack based upon their preferences and my impeccable offerings for them to choose from- and they'll choose from a list of songs for any given scene, if they so choose.
Whew! That was a bit more lengthy than planned. And now, a few of my favorite Excel Tips and Tricks.
I don't know about you, but I use the Microsoft Office suite quite a bit... primarily Excel.
I'm not trying to be a Microsoft pitch guy, but their Office suite does offer a lot of useful business tools. I regularly use Excel, Word, Access and Powerpoint, in that order. Here are a few of my favorite Excel tips and tricks.
White text- I'm starting off with a weird "in plain sight" trick. I have a spreadsheet that I use fairly often, and I need to track a quantity manually. As this quantity is unimportant to anyone but me, I don't want to draw attention to it so I've made the text in the cell the same color as the background- white.
"CTRL + ~"- If you know nothing about Excel except for constructing simple calculations, this is a vital shortcut. For those readers who are fairly new to Excel users, when you're working in a cell, you can see what you're doing in a cell by looking at the formula bar. When you type "CTRL + ~", you will see what is in every cell in your active spreadsheet- this is great for troubleshoooting.
Conditional formatting- a very sweet tool. It can be used in any number of ways. In a spreadsheet I currently co-manage, I use conditional formatting to alert me to when I need to reorder printer supplies. I also use it to manage my personal overtime.
Lastly, a just for fun bit of formatting.
You can make even a boring spreadsheet look a bit nicer by getting rid of the grid in some places, "simply" by changing the fill color.
Lastly, the most important trick is NEVER, ever do this in a production spreadsheet... that's tech talk for a spreadsheet which you really care about or would be lost without: ALWAYS MAKE A COPY, and work in the copy. I often need to use the data in a .csv or .xls/.xlsx file, and the first thing I do is copy the file to my desktop, rename it as a COPY_currentdata.xlxs, and then work from the copy. Never use the source file, unless your organization prohibits file copying. In that case, seek guidance from I.T. For all others, make that copy, and then delete it when you're done. If you have hardcopies (as I do), please be kind and deposit them in the shred bin.
That's all for now. I hope you've enjoyed my little movie fantasy, and can use one or two of my Excel "tricks". As always, I am hochspeyer, blogging data analysis and management do you don't have to.
Friday, May 20, 2016
Richard Strauss, and Data.
This past week, I've managed to get in to work fairly early almost every day so far (Monday- Thursday); I think Wednesday was the only day I came in later, and that was intentional. I've also been getting up earlier, and the time before work has been used to work on the database. I've made some good progress on the few boxed sets of audio CDs that we own, which is where Richard Strauss comes into the picture.
Before I go any further, I should give you a bit of my musical backstory, kind reader. You may have surmised from previous posts (especially the most recent one, Axl Rose, and Data,) that I'm more Rocker than Opera-Goer. This is 100% correct. However, my musical tastes are fairly diverse... Yo-Yo Ma, The Beatles, Chevelle, C.W. McCall, Air Supply, Deep Purple, Newsboys, Sibelius, 80's hair bands, choral, etc. I firmly believe that any music that is good should be played loud when possible. I like some blues, classic Motown, and a smattering of jazz. I'm okay with the various forms of trance, and dance music- if it's something that catches my ear. I can even deal with disco these days. Things I pretty much have no interest in are rap, hip hop, opera and death metal.
So, what's with Strauss?
Well, the album pictured above is a boxed set I picked up at a library sale for a solitary U.S. dollar. Cheap-value-SCORE! It's a three disc set, with a booklet nearly as thick as the CD case. The 330 page "booklet" has all sorts of details, not merely about this opera, but about this particular recording, its cast and conductor. It also has the complete text and lyrics of the opera in French, English and German. When I purchased it, I had decided that the weight of the boxed set alone made it a good buy... little did I know that this was a "reference" recording, one by which all others are to be judged. Its also conducted by Herbert von Karajan, a legendary conductor.
Still, what's with Strauss?
Data entry, pure and simple. As this past week has seen a renaissance of Forty-Two, I decided to tackled boxed CD sets. The problem is that this particular set has sixty-two tracks, all of which have German titles. I'm slow enough at data entry without having to import special characters, so I did what any reasonable human being would do: I looked up the recording on Amazon, copied the track list and pasted it into Excel. From there, I copied and pasted each track into Access. That's where I stopped with music- I still have three boxed sets to go, and then it's on to albums.
In other data news, I've got what appears to be a workable solution for my internal Lego part number. It's fairly lengthy at seventeen characters, and from all appearances, this should be sufficient. I've begun the data entry on this, and tried a few trial sorts. So far, everything looks good, and this is officially stage 2 of the Peeron normalization. I still need to add dimensions and clean up the text descriptions before importing it into a table.
As always, I am hochspeyer, blogging data analysis and management so you don't have to.
So, what's with Strauss?
Well, the album pictured above is a boxed set I picked up at a library sale for a solitary U.S. dollar. Cheap-value-SCORE! It's a three disc set, with a booklet nearly as thick as the CD case. The 330 page "booklet" has all sorts of details, not merely about this opera, but about this particular recording, its cast and conductor. It also has the complete text and lyrics of the opera in French, English and German. When I purchased it, I had decided that the weight of the boxed set alone made it a good buy... little did I know that this was a "reference" recording, one by which all others are to be judged. Its also conducted by Herbert von Karajan, a legendary conductor.
Still, what's with Strauss?
Data entry, pure and simple. As this past week has seen a renaissance of Forty-Two, I decided to tackled boxed CD sets. The problem is that this particular set has sixty-two tracks, all of which have German titles. I'm slow enough at data entry without having to import special characters, so I did what any reasonable human being would do: I looked up the recording on Amazon, copied the track list and pasted it into Excel. From there, I copied and pasted each track into Access. That's where I stopped with music- I still have three boxed sets to go, and then it's on to albums.
In other data news, I've got what appears to be a workable solution for my internal Lego part number. It's fairly lengthy at seventeen characters, and from all appearances, this should be sufficient. I've begun the data entry on this, and tried a few trial sorts. So far, everything looks good, and this is officially stage 2 of the Peeron normalization. I still need to add dimensions and clean up the text descriptions before importing it into a table.
As always, I am hochspeyer, blogging data analysis and management so you don't have to.
Sunday, May 15, 2016
Axl Rose, and Data.
It's been busy at work lately. And the rain has been frequent. And... it's Springtime in my little corner of the world. Consequently, our lawn got a "little" out of control.
Jennifer was kind enough to help out and mow the front lawn, which is what everyone passing our home sees. The back... well, that's another story- and my bailiwick.
My time had arrived. It had not rained for over a day. I looked out the back window and saw the grass blowing like Dust in the Wind. As my gaze landed upon the compost bins, I had to rub my eyes because I thought I saw a black tophat sitting on top of one of the compost bins, and a bandana on top of the other. I shook my head, blinked and looked again. Without warning, the yard had gone from broad daylight to a starless night. The yard's verdancy had turned to a monochrome with rough-cut video quality- I kid you not!. Our neighbor's white fence had been replaced by a gaggle of Marshall stacks, spotlights were illuminating the compost, and Slash, with trademark shades, ciggie and Les Paul, and partially obscured by the output of several smoke machines was furiously spewing out riff after riff, while Axl crooned, "You're in the jungle, baby" as he seemed to float over the grass. I covered my eyes, and shook my head. When I looked again, the moment was over. My yard was green, and back.
Sheesh! Less Monster, more cowbell... maybe.
So, I cut the grass. Yes, it was long- long overdue for cutting. I'm not certain how long it took to complete the task, but I had to make several trips to the compost to empty the mower's bag. When that was done, I decided to tackle another yard task: the ivy. And, to be fair to the myriad of horticulturalists, botanists and assorted green thumbs in my audience, I'm not really certain what this plant is. It has dark green leaves, a woody stalk, and will sprout roots along the stalk. It's also quite capable of climbing. So, we've decide that it must go.
I got most of it off of the red mulberry. Now, technically the mulberry should be a bush, as it has multiple trunks. This one, though, is ~30' (over 9 meters) tall, so I call it a tree. Many websites also call it a tree.
I put the mower back in the garage, and grabbed a pair of leather gloves, a few yard waste bags and some clippers, and proceeded to tear into the ivy. For reference, the bags are constructed of a double walled heavy brown paper, and are approximately 16" x 12" x 35" (40.6cm x 30.5cm x 88.9cm). I filled five of them, and I'm probably only about one-third of the way done. This was sometime last week, and since then, we've had rain at least four times, so the next time I tackle this project, the stubble from the first round should be visible, which should make the next phase a bit easier.
(Spoiler alert: this post contains references to Guns 'N' Roses and their songs...
and a few other musical references!)
Jennifer was kind enough to help out and mow the front lawn, which is what everyone passing our home sees. The back... well, that's another story- and my bailiwick.
My time had arrived. It had not rained for over a day. I looked out the back window and saw the grass blowing like Dust in the Wind. As my gaze landed upon the compost bins, I had to rub my eyes because I thought I saw a black tophat sitting on top of one of the compost bins, and a bandana on top of the other. I shook my head, blinked and looked again. Without warning, the yard had gone from broad daylight to a starless night. The yard's verdancy had turned to a monochrome with rough-cut video quality- I kid you not!. Our neighbor's white fence had been replaced by a gaggle of Marshall stacks, spotlights were illuminating the compost, and Slash, with trademark shades, ciggie and Les Paul, and partially obscured by the output of several smoke machines was furiously spewing out riff after riff, while Axl crooned, "You're in the jungle, baby" as he seemed to float over the grass. I covered my eyes, and shook my head. When I looked again, the moment was over. My yard was green, and back.
 |
| Seven Marshall stacks- fourteen cabinets and seven heads |
So, I cut the grass. Yes, it was long- long overdue for cutting. I'm not certain how long it took to complete the task, but I had to make several trips to the compost to empty the mower's bag. When that was done, I decided to tackle another yard task: the ivy. And, to be fair to the myriad of horticulturalists, botanists and assorted green thumbs in my audience, I'm not really certain what this plant is. It has dark green leaves, a woody stalk, and will sprout roots along the stalk. It's also quite capable of climbing. So, we've decide that it must go.
I got most of it off of the red mulberry. Now, technically the mulberry should be a bush, as it has multiple trunks. This one, though, is ~30' (over 9 meters) tall, so I call it a tree. Many websites also call it a tree.
 |
| Our Mulberry last Winter |
Finally- data! (Sorry, no data pictures!)
On Saturday morning (May 14th), I FINALLY completed the 1st phase of the Lego Peeron data normalization! Speeds and feeds are appropriate here, so here we go!
The original Peeron database I'm using is from March 19, 2012. It contains 18,510 parts (rows). After the first passes through the data- in which the data started out as a text file, and then was converted to an .xlxs file, and then the data had its initial cleansing where a "base" part number was created- the data ended up totaling 16,218 parts. It should be noted that this not a fixed number, as there are more parts that need to go to the "Stickers etc" worksheet. I also want to emphasize that I am cleaning data and not normalizing; after all, all of this data still resides in an Excel file, so it is still a flat file. My next task is to impose order onto this file, so that will require the creation of a unique part number. This part number will be the primary key once it is imported into Access.
Before any of this happens, I need to come up with a standardized format for my unique part number- currently called the "DB_Tracking_Number". Ugly, but good enough for now. I can't really automate this, once again because of the lack of standardization in the Peeron data, and my attempt to impose my own personal spin on Lego organization.
So, for now, I have ~ 16,000+ part numbers to create.
As always, I am hochspeyer, blogging data analysis and management so you don't have to.
Before any of this happens, I need to come up with a standardized format for my unique part number- currently called the "DB_Tracking_Number". Ugly, but good enough for now. I can't really automate this, once again because of the lack of standardization in the Peeron data, and my attempt to impose my own personal spin on Lego organization.
So, for now, I have ~ 16,000+ part numbers to create.
As always, I am hochspeyer, blogging data analysis and management so you don't have to.
Wednesday, March 30, 2016
Data cleansing, or Purge #4
I had about 50% of what passes for a normal length post written when I decided to scrap it and start afresh. I think I've mentioned that I truly hate rewriting anything, but the post just wasn't going anywhere. More importantly, I had no logical transition to a data breakthrough.
For the past few weeks I've been working on my Peeron-based database (Working with someone else's data and Meanwhile, back in the Secret Underground Lair...). It had been slow going, and I asked a coworker about it. My shy coworker suggested that my solution might be found in Excel.
I thought about that for a bit. I'm not bad with Excel (and that statement alone could be the subject of SEVERAL blog posts!), so I did a bit of exploring. In Excel 2007, on the Data tab, resides "text to columns". As I recall, this feature has been available for some time, but this has been the first time that I've used it. Recapping my challenge: I've got a text file (the Peeron data) which has the part numbers I need, plus much other ancillary data which I may or may not need. Important part: my source data is a .txt file.
So, I copied the master file to create a new working file. In the old master file, I executed text to columns; in the new (working), and then copied column A to the working file. This accomplished a few things: it preserved Peeron's numbering, as well as their descriptions.
Skipping a few steps, what I have now are four columns: Column D is unmodified Peeron data, column C is a size column, column B is called "Base w/mods" and column A is the "Base" column. I plan to add at least one more column at some point which will be a custom "internal" part number.
But... for now, I'm happy with my data, which is more than I've honestly been able to say about it for a few years! Seriously, this is one of those rubber meets the road blogs,where the plan actually comes together and progress is made! As promised, I've added another sheet to the workbook, and have begun extracting the extraneous data entries from the main spreadsheet, such as stickers, obsolete and moved part numbers.
Most all of the remainder of the process is going to be manual, but the text to columns wizard probably saved me upwards of sixty+ hours of work, assuming 300 rows per hour with the old cut and paste method I had been using. And I say upwards, because I think I only achieved 300 rows per hour once; generally, I've been at 200 or fewer rows per hour.
I'm desperately trying to get this published. I've said on more than one occasion that sometimes blogs just seem to write themselves. Well, this is not one of those occasions.
I'm going to close with a few speeds and feeds. As best I can tell, there were 18,512 rows of data in the original Peeron file. With the new and improved format, I've culled out close to 900 rows of data which are of no use to me. I still have quite a way to go, but for the first time in some while I'm seeing that there is a pot of gold at the end of this particular rainbow.
As always, I am hochspeyer, blogging data analysis and management so you don't have to.
For the past few weeks I've been working on my Peeron-based database (Working with someone else's data and Meanwhile, back in the Secret Underground Lair...). It had been slow going, and I asked a coworker about it. My shy coworker suggested that my solution might be found in Excel.
I thought about that for a bit. I'm not bad with Excel (and that statement alone could be the subject of SEVERAL blog posts!), so I did a bit of exploring. In Excel 2007, on the Data tab, resides "text to columns". As I recall, this feature has been available for some time, but this has been the first time that I've used it. Recapping my challenge: I've got a text file (the Peeron data) which has the part numbers I need, plus much other ancillary data which I may or may not need. Important part: my source data is a .txt file.
So, I copied the master file to create a new working file. In the old master file, I executed text to columns; in the new (working), and then copied column A to the working file. This accomplished a few things: it preserved Peeron's numbering, as well as their descriptions.
Skipping a few steps, what I have now are four columns: Column D is unmodified Peeron data, column C is a size column, column B is called "Base w/mods" and column A is the "Base" column. I plan to add at least one more column at some point which will be a custom "internal" part number.
But... for now, I'm happy with my data, which is more than I've honestly been able to say about it for a few years! Seriously, this is one of those rubber meets the road blogs,where the plan actually comes together and progress is made! As promised, I've added another sheet to the workbook, and have begun extracting the extraneous data entries from the main spreadsheet, such as stickers, obsolete and moved part numbers.
Most all of the remainder of the process is going to be manual, but the text to columns wizard probably saved me upwards of sixty+ hours of work, assuming 300 rows per hour with the old cut and paste method I had been using. And I say upwards, because I think I only achieved 300 rows per hour once; generally, I've been at 200 or fewer rows per hour.
I'm desperately trying to get this published. I've said on more than one occasion that sometimes blogs just seem to write themselves. Well, this is not one of those occasions.
I'm going to close with a few speeds and feeds. As best I can tell, there were 18,512 rows of data in the original Peeron file. With the new and improved format, I've culled out close to 900 rows of data which are of no use to me. I still have quite a way to go, but for the first time in some while I'm seeing that there is a pot of gold at the end of this particular rainbow.
As always, I am hochspeyer, blogging data analysis and management so you don't have to.
Saturday, March 5, 2016
Working with someone else's data
At a certain point in one's data career (plain English: pretty near every day, and with nearly every piece of data one normally touches!), you will have the "opportunity" to work with data someone else has prepared, or at least accumulated or aggregated or in some way modified. As I create Forty-Two, "most" of the data I will be using is data which I have entered (created) into a table. There is one notable exception to this, though, and it is for me a moderately to severely painful one: Lego parts.
As mentioned in a previous post, my experience tells me that the best place for my Lego data is in a spreadsheet- at least initially. And although calculations can be done in Access, Excel is much easier for me. And, they can be linked at a later date.
However, there is a HUGE caveat to the Lego data. The title refers to someone else's data. In my case, I'm using peeron.com's parts list. It's the best one that I've found, and many other AFOL (Adult Fan of Lego) sites use it.as a parts reference. The problem with the list and site is that they're horribly out of date. The list has a very standard naming convention, and the last update was nearly four years ago at the time of this writing. However, as it is the best, easiest to use and most complete list currently available, I've decided to use it. I can update newer parts as I find them on other websites.
The other issue with the Peeron data is that it is a .txt file. Not bad when importing to Excel- just copy and paste. However, to make it usable, I have to manually edit the 18K+ rows of data. As I'm in no great hurry to finish this phase of the project, it's not too much of an issue. Still, .... I know a guy (as they say) who might be able to help. More on that later.
The final issue with Peeron is that its creator strove to make it a very complete parts list. As such, there's a great deal of data which I actually don't need- stickers and superseded part numbers are two types which come to mind immediately. So, if my guy can fix my primary issue, the other issues will be much easier to deal with. If not, then I still have a great deal of work ahead of me. UPDATE: I'm not really surprised- but also not horribly disappointed- that we were not able to fix the data.
Before I forget, I wanted to post a brief update on the blog itself. I'm not quite sure when I did this last, but I think it was around the time the blog hit 10K viewers. As of today, the blog has over 15K viewers in fifty-five countries on six continents (c'mon, Antarctica!). Africa is represented by three countries, Asia by seventeeen, Australia by one, Europe by twenty-seven (I'm counting Russia in the Europe column rather than Asia), North America by six and South America by one. What's most amazing to me is that of these fifty-five countries, I could only count seven where English was either the official language, a dominant language, or one of a group of commonly accepted languages.
To each and every reader- THANK YOU!
Last: a small compilation of my blogs dealing with data (for those who are interested in how a small-time operator handles data)
http://hochspeyer.blogspot.com/2016/02/you-said-this-was-about-data-analysis.html
http://hochspeyer.blogspot.com/2015/06/data-science-pt-1.html
http://hochspeyer.blogspot.com/2016/02/a-database-against-rules.html
http://hochspeyer.blogspot.com/2015/05/data-defined.html
http://hochspeyer.blogspot.com/2015/02/forty-two-v7-or-so.html
As always, I am hochspeyer, blogging data analysis and management so you don't have to.
As mentioned in a previous post, my experience tells me that the best place for my Lego data is in a spreadsheet- at least initially. And although calculations can be done in Access, Excel is much easier for me. And, they can be linked at a later date.
However, there is a HUGE caveat to the Lego data. The title refers to someone else's data. In my case, I'm using peeron.com's parts list. It's the best one that I've found, and many other AFOL (Adult Fan of Lego) sites use it.as a parts reference. The problem with the list and site is that they're horribly out of date. The list has a very standard naming convention, and the last update was nearly four years ago at the time of this writing. However, as it is the best, easiest to use and most complete list currently available, I've decided to use it. I can update newer parts as I find them on other websites.
The other issue with the Peeron data is that it is a .txt file. Not bad when importing to Excel- just copy and paste. However, to make it usable, I have to manually edit the 18K+ rows of data. As I'm in no great hurry to finish this phase of the project, it's not too much of an issue. Still, .... I know a guy (as they say) who might be able to help. More on that later.
The final issue with Peeron is that its creator strove to make it a very complete parts list. As such, there's a great deal of data which I actually don't need- stickers and superseded part numbers are two types which come to mind immediately. So, if my guy can fix my primary issue, the other issues will be much easier to deal with. If not, then I still have a great deal of work ahead of me. UPDATE: I'm not really surprised- but also not horribly disappointed- that we were not able to fix the data.
Before I forget, I wanted to post a brief update on the blog itself. I'm not quite sure when I did this last, but I think it was around the time the blog hit 10K viewers. As of today, the blog has over 15K viewers in fifty-five countries on six continents (c'mon, Antarctica!). Africa is represented by three countries, Asia by seventeeen, Australia by one, Europe by twenty-seven (I'm counting Russia in the Europe column rather than Asia), North America by six and South America by one. What's most amazing to me is that of these fifty-five countries, I could only count seven where English was either the official language, a dominant language, or one of a group of commonly accepted languages.
To each and every reader- THANK YOU!
Last: a small compilation of my blogs dealing with data (for those who are interested in how a small-time operator handles data)
http://hochspeyer.blogspot.com/2016/02/you-said-this-was-about-data-analysis.html
http://hochspeyer.blogspot.com/2015/06/data-science-pt-1.html
http://hochspeyer.blogspot.com/2016/02/a-database-against-rules.html
http://hochspeyer.blogspot.com/2015/05/data-defined.html
http://hochspeyer.blogspot.com/2015/02/forty-two-v7-or-so.html
As always, I am hochspeyer, blogging data analysis and management so you don't have to.
Sunday, February 28, 2016
Managing data better
I just discovered a fairly large data loss. As mentioned in a previous post, I rebuilt my laptop due to a failed HDD. Unfortunately (no one is looking, you can raise your hand if this has ever happened to you), I had a fair amount of data on that drive which was not backed up.
To paraphrase Hall and Oates, its gone. So, to paraphrase the unofficial motto of Chicago ("Vote early, vote often"), Save early, save often.
As promised in my previous blog, I'm going to spend a bit of time on Lego, because as far as my data is concerned, it is handled a bit differently than the other items which I am cataloging.
Lego and I go way back, but I didn't attempt to catalog my Lego collection until fairly recently. I've given consideration to including Lego in the "big database" (Forty-Two), but have found that- at least for me in this particular application, Excel is the better tool for me to use. Let me try to unpack that a bit.
Some time ago, I had the opportunity to observe how several different businesses utilized software to work with data. Some preferred Microsoft Access, and most preferred Microsoft Excel, or, to be a bit more generic, spreadsheets were preferred over databases. In only one business were both used- but independently, rather than in a complimentary fashion. The preference of software had little to do with function, generally speaking. Rather, it was more about culture and familiarity. In every case I observed, the results could have been improved simply by not just "thinking outside the box," but merely by thinking.
In my case, a spreadsheet seems to be the best solution- based upon my experience. My primary reasoning behind this is because Lego is a single thing which does not need to be linked (or, related) to anything else. And, even though I may be interested in a bit of analysis of the Lego "population", in the larger scheme of things Lego exists in its own unique bubble: shapes, colors, themes, sizes. I could build tables based upon these and other categories, but once again, they would relate only to Lego.
Forty-Two, on the other hand, illustrates quite well the differences between a flat database (the Lego spreadsheet) and a relational database (Forty-Two). With the Lego spreadsheet, I can see a snapshot of all the facts of my Lego collection. But with Forty-Two I can write an ad hoc query to tell me where all Lord of the Rings media is located. It would show where each book, soundtrack, game and video is located. It would also show the number of copies in a given format. It would also tell me the last time the media was viewed.
There's also one additional aspect of Lego databasing which is unrelated to software, but makes cataloging it so much easier: storage.
I'm an AFOL (Adult Fan of Lego). AFOL is a title; more of a descriptor, actually, as it really doesn't carry any of the clout that, say, CCNA carries. Still, it differentiates me from most adults who play with their kids while playing with Lego. And, its somewhat hard to say that without sounding like some sort of pompous jerk, because it sounds like I'm slamming parents who "play" Lego with their kids. Quite the contrary! If you're a parent who engages with your kids over a pile of Lego, kudos to you!
An AFOL, though, uses Lego as their primary creative medium. There are professional Lego artists out there who make a good living by building amazing models for corporate clients. There are also educators who use Lego in either a standard classroom setting, or in a program for ASD (autism spectrum disorder) kids. Even architects use Lego for models. And although each of these examples is an example of adults working with Lego for a living, the typical AFOL is something else.
They're a bit of a subject expert on Lego. They probably have some advanced building skills. But mostly, I think, they are something of a type of a Leonardo da Vinci. They are probably the precursors of the maker movement, which is another subject entirely.
Back on track: AFOLs need organization, and the English word for this is storage. Over the course of many years, many bricks (elements) will be collected. I've found that the (current) best way to keep track of these is to put them in plastic bags (connected in groups of 10) with a card inside indicating the count and the date of the count. These, in turn, reside in drawer organizers.
As always, I am hochspeyer, blogging data analysis ad management so you don't have to.
To paraphrase Hall and Oates, its gone. So, to paraphrase the unofficial motto of Chicago ("Vote early, vote often"), Save early, save often.
As promised in my previous blog, I'm going to spend a bit of time on Lego, because as far as my data is concerned, it is handled a bit differently than the other items which I am cataloging.
Lego and I go way back, but I didn't attempt to catalog my Lego collection until fairly recently. I've given consideration to including Lego in the "big database" (Forty-Two), but have found that- at least for me in this particular application, Excel is the better tool for me to use. Let me try to unpack that a bit.
Some time ago, I had the opportunity to observe how several different businesses utilized software to work with data. Some preferred Microsoft Access, and most preferred Microsoft Excel, or, to be a bit more generic, spreadsheets were preferred over databases. In only one business were both used- but independently, rather than in a complimentary fashion. The preference of software had little to do with function, generally speaking. Rather, it was more about culture and familiarity. In every case I observed, the results could have been improved simply by not just "thinking outside the box," but merely by thinking.
In my case, a spreadsheet seems to be the best solution- based upon my experience. My primary reasoning behind this is because Lego is a single thing which does not need to be linked (or, related) to anything else. And, even though I may be interested in a bit of analysis of the Lego "population", in the larger scheme of things Lego exists in its own unique bubble: shapes, colors, themes, sizes. I could build tables based upon these and other categories, but once again, they would relate only to Lego.
Forty-Two, on the other hand, illustrates quite well the differences between a flat database (the Lego spreadsheet) and a relational database (Forty-Two). With the Lego spreadsheet, I can see a snapshot of all the facts of my Lego collection. But with Forty-Two I can write an ad hoc query to tell me where all Lord of the Rings media is located. It would show where each book, soundtrack, game and video is located. It would also show the number of copies in a given format. It would also tell me the last time the media was viewed.
There's also one additional aspect of Lego databasing which is unrelated to software, but makes cataloging it so much easier: storage.
I'm an AFOL (Adult Fan of Lego). AFOL is a title; more of a descriptor, actually, as it really doesn't carry any of the clout that, say, CCNA carries. Still, it differentiates me from most adults who play with their kids while playing with Lego. And, its somewhat hard to say that without sounding like some sort of pompous jerk, because it sounds like I'm slamming parents who "play" Lego with their kids. Quite the contrary! If you're a parent who engages with your kids over a pile of Lego, kudos to you!
An AFOL, though, uses Lego as their primary creative medium. There are professional Lego artists out there who make a good living by building amazing models for corporate clients. There are also educators who use Lego in either a standard classroom setting, or in a program for ASD (autism spectrum disorder) kids. Even architects use Lego for models. And although each of these examples is an example of adults working with Lego for a living, the typical AFOL is something else.
They're a bit of a subject expert on Lego. They probably have some advanced building skills. But mostly, I think, they are something of a type of a Leonardo da Vinci. They are probably the precursors of the maker movement, which is another subject entirely.
Back on track: AFOLs need organization, and the English word for this is storage. Over the course of many years, many bricks (elements) will be collected. I've found that the (current) best way to keep track of these is to put them in plastic bags (connected in groups of 10) with a card inside indicating the count and the date of the count. These, in turn, reside in drawer organizers.
As always, I am hochspeyer, blogging data analysis ad management so you don't have to.
Saturday, February 27, 2016
Feeds, Needs and Speeds
Well, it's starting to get real. The database, that is. As I posted earlier on Twitter, 200 rows of data in a single column do not a RDBMS make, but it's a start (the count is currently 224). As Jackie Gleason quipped in one of his signature lines, "And away we go!"
The casual reader may be wondering at this point: there are actually folks out there still building relational databases? Isn't the non-relational NoSQL model more popular in terms of new deployments, versatility and just plain coolness?
I'd like to explain a bit of my personal journey that brought me to Forty-Two, the database.
Long ago, like teenagers everywhere, I faced highschool graduation without a plan. Not just a clear plan, mind you- NO PLAN. Computer Science was in its infancy at the time, and nearly nonexistent in most high schools. I was not one of the cool kids, nor was I a jock or a brain, but I was also not a nerd. However, I knew one or two nerds. And the nerds were highly focused in their scholarly discipline: they were not into math and science; they were into math. They were geometry slingers, wearing leather slide rule holsters on their hips. They had glasses, thick glasses. Below average complexion. Few social skills. Pocket protectors in their left shirt pockets. And behind the pocket protectors... punch cards for their next "program".They were the late 70's analogues of Drs. Sheldon Cooper and Leonard Hofstadler (The Big Bang Theory).
I was not them. Well, sort of not like them. I liked history. I was (probably) the worst kind of history nerd: I was a military history buff. I started out with Avalon Hill games like France 1940 and Panzer Blitz, and progressed to Tobruk and Squad Leader, eventually culminating in the non-Avalon Hill classic Fletcher Pratt's Naval Wargame. The point is this: as the complexity increased, the playability decreased, as did the number of folks willing to take on the rules. But, I digress.
I was accepted to Rosary College (later Dominican University). I declared history as my major, and spent two unremarkable years there. I eventually had five colleges or universities under my belt, with no undergraduate degree to show for all of the buckazoids invested.
Long before this became a mainstream theory in education, I discovered that we do not all learn in the same way, and that higher education was not really the best choice for me.
Fast forward a few decades. I've mentioned this fairly recently- I was working as a data analyst at an "action sports" company. The company designed paintball equipment, and had it manufactured overseas. My job as the data analyst was to take Wal Mart RetailLink data and dice and slice it into what my employer could use.
The problem was my employer was using either Office 2000 or 2003, which limited Excel to a maximum of ~64K (I believe it was 63,536) rows. As time went on, my data often exceeded this artificial limitation, and I was forced to use Access just to grab the Monday morning data. Once again, skipping several steps, I became adept at moving data between Excel and Access.
Fast forward one more time to today. I use all sorts of tools to do my job; Access and Excel aren't really part of my professional portfolio of commonly used programs on the job, but I use them at home,.. pause for effect.
Yes, although I may have mentioned a bit about Forty-Two before, I don't think I've said too much beyond it was my own little database dev world. Here's where the title comes in: way back when I was an I.T. reseller, we used to often qualify sales by talking about speeds and feeds- equipment specifications. Needs are also important (besides completing the alliterative trilogy).
So, Forty-Two is an obvious reference to Douglas Adams works, and is so named because its' goal is to answer that elusive question: what is the meaning of Life, the Universe, and Everything. It is starting out life as an Access database, currently with only one table. Previous iterations have taught me to take it easy with adding tables, so my aim is to get this Titles table to be mostly complete before adding additional single- or very few-column tables and then finally starting to create the relationships.The first table is called Titles simply because it holds titles: books, videos, software... if its media, then its name goes here. Why? Forced normalization: why go through the normalization process when I can start out with a relatively clean dataset?
This is starting to turn into a wall of words (by my standards, anyway!), so stay tuned... Lego is next!
As always, I am hochspeyer, blogging data analysis and management so you don't have to.
The casual reader may be wondering at this point: there are actually folks out there still building relational databases? Isn't the non-relational NoSQL model more popular in terms of new deployments, versatility and just plain coolness?
I'd like to explain a bit of my personal journey that brought me to Forty-Two, the database.
Long ago, like teenagers everywhere, I faced highschool graduation without a plan. Not just a clear plan, mind you- NO PLAN. Computer Science was in its infancy at the time, and nearly nonexistent in most high schools. I was not one of the cool kids, nor was I a jock or a brain, but I was also not a nerd. However, I knew one or two nerds. And the nerds were highly focused in their scholarly discipline: they were not into math and science; they were into math. They were geometry slingers, wearing leather slide rule holsters on their hips. They had glasses, thick glasses. Below average complexion. Few social skills. Pocket protectors in their left shirt pockets. And behind the pocket protectors... punch cards for their next "program".They were the late 70's analogues of Drs. Sheldon Cooper and Leonard Hofstadler (The Big Bang Theory).
I was not them. Well, sort of not like them. I liked history. I was (probably) the worst kind of history nerd: I was a military history buff. I started out with Avalon Hill games like France 1940 and Panzer Blitz, and progressed to Tobruk and Squad Leader, eventually culminating in the non-Avalon Hill classic Fletcher Pratt's Naval Wargame. The point is this: as the complexity increased, the playability decreased, as did the number of folks willing to take on the rules. But, I digress.
I was accepted to Rosary College (later Dominican University). I declared history as my major, and spent two unremarkable years there. I eventually had five colleges or universities under my belt, with no undergraduate degree to show for all of the buckazoids invested.
Long before this became a mainstream theory in education, I discovered that we do not all learn in the same way, and that higher education was not really the best choice for me.
Fast forward a few decades. I've mentioned this fairly recently- I was working as a data analyst at an "action sports" company. The company designed paintball equipment, and had it manufactured overseas. My job as the data analyst was to take Wal Mart RetailLink data and dice and slice it into what my employer could use.
The problem was my employer was using either Office 2000 or 2003, which limited Excel to a maximum of ~64K (I believe it was 63,536) rows. As time went on, my data often exceeded this artificial limitation, and I was forced to use Access just to grab the Monday morning data. Once again, skipping several steps, I became adept at moving data between Excel and Access.
Fast forward one more time to today. I use all sorts of tools to do my job; Access and Excel aren't really part of my professional portfolio of commonly used programs on the job, but I use them at home,.. pause for effect.
Yes, although I may have mentioned a bit about Forty-Two before, I don't think I've said too much beyond it was my own little database dev world. Here's where the title comes in: way back when I was an I.T. reseller, we used to often qualify sales by talking about speeds and feeds- equipment specifications. Needs are also important (besides completing the alliterative trilogy).
So, Forty-Two is an obvious reference to Douglas Adams works, and is so named because its' goal is to answer that elusive question: what is the meaning of Life, the Universe, and Everything. It is starting out life as an Access database, currently with only one table. Previous iterations have taught me to take it easy with adding tables, so my aim is to get this Titles table to be mostly complete before adding additional single- or very few-column tables and then finally starting to create the relationships.The first table is called Titles simply because it holds titles: books, videos, software... if its media, then its name goes here. Why? Forced normalization: why go through the normalization process when I can start out with a relatively clean dataset?
This is starting to turn into a wall of words (by my standards, anyway!), so stay tuned... Lego is next!
As always, I am hochspeyer, blogging data analysis and management so you don't have to.
Wednesday, February 3, 2016
You said this was about data analysis!
Regular readers are familiar with the tagline that ends every one of my posts, "As always, I am hochspeyer, blogging data analysis and management so you don't have to." There's a bit of truth there, as well as a bit of attempted humor.
For starters, I've been using "hochspeyer" as my online persona for years. "Hochspeyer" (upper cased H) is a village in Germany where Jennifer and I lived for a few years. My online name (hochspeyer, lower cased h) is something I've been using for years. And although the topic of data doesn't come up as frequently as I'd like, I do try to feature it when possible. One problem I have is that I don't do as much analysis as, say, when I was actually employed as an analyst... for 15 USD/hr.
Pregnant pause for effect.
Yes, I once made only $15/hr working as a data analyst (this might be lots of money in some parts of the world, but in the greater Chicago area, its not- especially when you're feeding, clothing and providing housing for five persons). According to payscale.com, it should have been (at a minimum) 39K per year. According to an inflation calculator, the USD inflation rate between then and now is ~17.6%, so I was roughly 2K under the base salary for an analyst, or roughly 5%. However, at that point in my life, it was the only gig I could score, and the plus side was I learned quite a bit about basic data analysis, Excel, Access, customers, internal and external deadlines, and adding value to both your output and your position.
As a temp, I did analysis for $12/hr. My boss there was a lady who was the Quality Manager. The plant was a contract manufacturer of aluminum die castings for the automotive industry. Although they produced products for all three of the "Big 3" (General Motors, Ford, and Chrysler), their main customer was General Motors (GM). This was when Saturn was an exciting GM product line. The company had made a significant investment in equipment that was specifically designed for small cars, such as the Saturn line. Unfortunately, the Saturn line had disappointing sales for GM, and was ultimately scrapped. I apparently was doing a good job there, however: there were two rounds of layoffs in the company before I was let go. My value add there wasn't special- I integrated Excel data into an existing Access database. I also had the temerity to give reasons when they asked why I couldn't extrapolate results (when one has neither data or experience, one is up the creek without a paddle in terms of validity!)
With overtime, computer crashes and life in general, I haven't had much time to think about data as of late, much less do any analysis. However, the further I get from 10K blog viewers, the stronger the pull is to do a bit of analysis on blog hits to date.
So, here we go: blog speeds and feeds.
I started writing this blog in December of 2012. As of January 2016, I've published 196 posts to this blog- roughly five posts per month. As I'm no professional writer, I've got to say that this paltry volume is a challenge to maintain.
I've been tracking numbers from Day One, and they break down like this:
As far as continents go, only Antarctica has zero readers- Antarctica is 7th.
South America comes in 6th. With the limited amount of data I get back from Google (it's free, so I'm not complaining), I can only guess that language may be a factor in the readership here.
In terms of continents, Australia has the fewest number of countries... 1, and it is predictably 5th in the ranking. However, I think a few more countries could probably be considered a part of Australia for statistical purposes- New Zealand comes to mind immediately, but that's about it. If anyone has any ideas, please comment. The rest of the continents are as follows:
Africa (4th) is actually tied with Australia in terms of total readers, but these readers are distributed among a few countries, so I have to award the spot to Africa.
Europe comes in 3rd, which is slightly surprising because of the widespread use of English there. Twenty-seven countries represent Europe.
Asia is represented by seventeen countries and comes in 2nd, mainly because of Chinese readers.
My top market, again not a surprise, is North America, with a bit over 75% of my global total in readership.
That's it from here for now. Before signing off, I'd like to extend special e Day (2/7) greetings to Prof. Diego Kuonen!
As always, I am hochspeyer, blogging data analysis and management so you don't have to.
For starters, I've been using "hochspeyer" as my online persona for years. "Hochspeyer" (upper cased H) is a village in Germany where Jennifer and I lived for a few years. My online name (hochspeyer, lower cased h) is something I've been using for years. And although the topic of data doesn't come up as frequently as I'd like, I do try to feature it when possible. One problem I have is that I don't do as much analysis as, say, when I was actually employed as an analyst... for 15 USD/hr.
Pregnant pause for effect.
Yes, I once made only $15/hr working as a data analyst (this might be lots of money in some parts of the world, but in the greater Chicago area, its not- especially when you're feeding, clothing and providing housing for five persons). According to payscale.com, it should have been (at a minimum) 39K per year. According to an inflation calculator, the USD inflation rate between then and now is ~17.6%, so I was roughly 2K under the base salary for an analyst, or roughly 5%. However, at that point in my life, it was the only gig I could score, and the plus side was I learned quite a bit about basic data analysis, Excel, Access, customers, internal and external deadlines, and adding value to both your output and your position.
As a temp, I did analysis for $12/hr. My boss there was a lady who was the Quality Manager. The plant was a contract manufacturer of aluminum die castings for the automotive industry. Although they produced products for all three of the "Big 3" (General Motors, Ford, and Chrysler), their main customer was General Motors (GM). This was when Saturn was an exciting GM product line. The company had made a significant investment in equipment that was specifically designed for small cars, such as the Saturn line. Unfortunately, the Saturn line had disappointing sales for GM, and was ultimately scrapped. I apparently was doing a good job there, however: there were two rounds of layoffs in the company before I was let go. My value add there wasn't special- I integrated Excel data into an existing Access database. I also had the temerity to give reasons when they asked why I couldn't extrapolate results (when one has neither data or experience, one is up the creek without a paddle in terms of validity!)
With overtime, computer crashes and life in general, I haven't had much time to think about data as of late, much less do any analysis. However, the further I get from 10K blog viewers, the stronger the pull is to do a bit of analysis on blog hits to date.
So, here we go: blog speeds and feeds.
I started writing this blog in December of 2012. As of January 2016, I've published 196 posts to this blog- roughly five posts per month. As I'm no professional writer, I've got to say that this paltry volume is a challenge to maintain.
I've been tracking numbers from Day One, and they break down like this:
As far as continents go, only Antarctica has zero readers- Antarctica is 7th.
South America comes in 6th. With the limited amount of data I get back from Google (it's free, so I'm not complaining), I can only guess that language may be a factor in the readership here.
In terms of continents, Australia has the fewest number of countries... 1, and it is predictably 5th in the ranking. However, I think a few more countries could probably be considered a part of Australia for statistical purposes- New Zealand comes to mind immediately, but that's about it. If anyone has any ideas, please comment. The rest of the continents are as follows:
Africa (4th) is actually tied with Australia in terms of total readers, but these readers are distributed among a few countries, so I have to award the spot to Africa.
Europe comes in 3rd, which is slightly surprising because of the widespread use of English there. Twenty-seven countries represent Europe.
Asia is represented by seventeen countries and comes in 2nd, mainly because of Chinese readers.
My top market, again not a surprise, is North America, with a bit over 75% of my global total in readership.
That's it from here for now. Before signing off, I'd like to extend special e Day (2/7) greetings to Prof. Diego Kuonen!
As always, I am hochspeyer, blogging data analysis and management so you don't have to.
Thursday, August 27, 2015
1st down! (or, week 5, new goals)
As mentioned in a previous post, weight loss can be very easily related to American football.
For those unfamiliar with American football- there is a clock, like in soccer (football everywhere else!) In soccer (if I understand the game correctly, there are two 45-minute halves, with no real time outs, and time can be added by the officials at the end of the half (or maybe just the final half). American football is also divided into halves, but each half is 30 minutes long, and further divided into quarters of 15 minutes each. It is fairly unusual for much time, if any, to be added back on to the clock.
After my first successful round of weight loss, I've decided that this is a pretty manageable way of doing things, and have set a goal for the next four weeks. The weight loss goal of the first four weeks was a total of ten pounds; through diet, exercise, a pedometer and an Excel spreadsheet I managed to drop seventeen pounds.
I need to explain "downs" to those unacquainted with American football. In stark contrast to soccer, which is similar to ballet in that there is near constant motion, American football is more like a chess match, with one huge difference. In chess, opponents take turns attempting to place their opponent into check and ultimately mate. In football, a team has four opportunities to run plays and attempt to score- or, at least get another set of downs. An American football field is 100 yards long (meters/metres and yards are fairly close in length; one inch== 2.54cm, a yard ==36 inches, and a meter==39.36 inches [yes, I know these things by heart]). To get a "first down", a team needs to move the ball 10 yards from where they originally possessed it. When they move the ball 10 yards or more, they get a new set of downs.
So, I successfully completed my first four week challenge- I lost 17 pounds. In football parlance, I gained 17 yards, maybe. I now have a new set of downs.
I truly, truly hope all of the soccer fans out there are able to follow this analogy, because I think it works pretty well. Pressing on...
I've moved up the field, farther than I'd expected when I first started. I have a new set of four weeks to work with. Initially, I thought about increasing my goal just a bit, say to 12 pounds for the period. However, I added some simple analytics to my spreadsheet, and decided that instead of shooting for a weight goal for the next four weeks, I'd shoot for something a bit more significant in terms of health. 12 pounds is good, but 14 pounds will drop my BMI into a lower category, so it's a goal worth shooting for.
I've said this before on twitter: I'm not a data scientist nor do I pretend to be one. However, most of us can do some common sense analytics and improve or fine tune our goals. The answers (or very clear directions to the answers) are often right in front of us. We just need to know how to ask the right questions (or to be brave or honest enough to ask them).
My wake up call started with a reflection in a mirror. What's yours?
As always, I am hochspeyer, blogging data analysis and management so you don't have to.
For those unfamiliar with American football- there is a clock, like in soccer (football everywhere else!) In soccer (if I understand the game correctly, there are two 45-minute halves, with no real time outs, and time can be added by the officials at the end of the half (or maybe just the final half). American football is also divided into halves, but each half is 30 minutes long, and further divided into quarters of 15 minutes each. It is fairly unusual for much time, if any, to be added back on to the clock.
After my first successful round of weight loss, I've decided that this is a pretty manageable way of doing things, and have set a goal for the next four weeks. The weight loss goal of the first four weeks was a total of ten pounds; through diet, exercise, a pedometer and an Excel spreadsheet I managed to drop seventeen pounds.
I need to explain "downs" to those unacquainted with American football. In stark contrast to soccer, which is similar to ballet in that there is near constant motion, American football is more like a chess match, with one huge difference. In chess, opponents take turns attempting to place their opponent into check and ultimately mate. In football, a team has four opportunities to run plays and attempt to score- or, at least get another set of downs. An American football field is 100 yards long (meters/metres and yards are fairly close in length; one inch== 2.54cm, a yard ==36 inches, and a meter==39.36 inches [yes, I know these things by heart]). To get a "first down", a team needs to move the ball 10 yards from where they originally possessed it. When they move the ball 10 yards or more, they get a new set of downs.
So, I successfully completed my first four week challenge- I lost 17 pounds. In football parlance, I gained 17 yards, maybe. I now have a new set of downs.
I truly, truly hope all of the soccer fans out there are able to follow this analogy, because I think it works pretty well. Pressing on...
I've moved up the field, farther than I'd expected when I first started. I have a new set of four weeks to work with. Initially, I thought about increasing my goal just a bit, say to 12 pounds for the period. However, I added some simple analytics to my spreadsheet, and decided that instead of shooting for a weight goal for the next four weeks, I'd shoot for something a bit more significant in terms of health. 12 pounds is good, but 14 pounds will drop my BMI into a lower category, so it's a goal worth shooting for.
I've said this before on twitter: I'm not a data scientist nor do I pretend to be one. However, most of us can do some common sense analytics and improve or fine tune our goals. The answers (or very clear directions to the answers) are often right in front of us. We just need to know how to ask the right questions (or to be brave or honest enough to ask them).
My wake up call started with a reflection in a mirror. What's yours?
As always, I am hochspeyer, blogging data analysis and management so you don't have to.
Saturday, April 25, 2015
Data, evolving
Quick- fill in the blank: I do some of my best work in ______________________.
If you filled that blank with "the bathroom" congratulations! You, Freddie Mercury and I think alike. I believe Freddie Mercury claimed to have come up with the idea and melody for "Crazy Little Thing Called Love" in ten minutes while soaking in the bathtub in Műnchen (Munich), Germany, and the same thing happened to me Thursday evening!
Well, sort of... I didn't actually come up with a #1 U.S. Billboard chart single for a supergroup in a hotel in the Black Forest in Germany, but I think I've come up with the way I want to get my Lego data into Access.
A few years back, I had designed a flat database for my Lego collection in Microsoft Excel 2003. It was pretty nice and fit my needs at the time quite nicely. It was composed of several pages where the data was entered, and then a couple of pages which gave some bird's eye level analysis via links to the data pages. The project was scrapped mainly due to my impatience with data entry and my inability to actually get an accurate count of all of those elements- besides, they are much more fun to build with than count.
So, my plan is to go back to that format, but instead of merely having links within the workbook, have links from the Excel 2007 workbook to the Access 2007 database. And why 2007 rather than 2010 or 2013? Expediency: the Legos are close to a machine in the Secret Underground Lair (SUL) which runs 2007. The SUL redo is going very slowly, but if I am successful in liberating space for my laptop in my corner of the SUL, then it will be done on the 2013 versions of Excel and Access.
I'd like to get started on this first- creating the spreadsheet with the data, and then importing the data into an Access table and then (hopefully!) linking each data cell in Excel to its corresponding Access field so that the database can be updated via Excel. Its been some time since I did this, and it was on the "pre-ribbon" interface, so it looks like I'm going to be relearning some Excel tricks.
In other data news, Jennifer and I have been walking more as the weather has become a bit more pleasant. My replacement pedometer (identical to my expired Omron model) is chugging along, but its going to be at least another month before I have daily month-to-month data available.
As to my new glasses: the lady who fitted me for frames was slightly surprised as to how quickly I adjusted to my new spectacles, although I think I'm still breaking them in. She also said something that caught me off-guard: I wear my lenses lower than most folks.
I've never worn gradient lenses before- I've worn reading glasses since the age of eighteen and I'm now 50+; my new specs have three prescriptions per lens: top for distance, middle for computer work and bottom for reading. Although the glasses really do a good job (especially while driving), they are singularly ineffective in the place where I need them the most: computer. Here's the deal: before I went in for the exam, I had made some careful observations and mental notes about my work environment and how I typically used glasses, as well as my concerns about night driving. The gradient lenses the optometrist came up with (pretty much six prescriptions- three per lens!) are fantastic for general purpose applications (life!). My hope in getting this type of lens would be avoiding a second pair of glasses. To quote "Mad Dog" Tannen, "You thought wrong, dude." (*BLAM*) I was very quickly able to pick up the reading area of the lenses, and the distance area (which I don't really need but seems to be beneficial in low light/nighttime conditions) also "snapped" into place pretty quickly. I really need the computer part, though, and this is where I'm experiencing issues. Simply put: the lens cannot focus on the whole screen- even when I push the frame up tight to my face for the best focus. In the "good ole" 15" CRT days, these glasses would have been miraculous; with the 23" CRT's I have at work and at home, the lenses are just not up to it. And honestly, at home it really doesn't matter, because I'm generally not looking at the whole screen. At work, though- I do a lot of page layout and composition, and I generally NEED to take in the whole 23" diagonal screen at a glance- and more often than not, at a 90 degree offset. So, after only two days, I was back getting fitted for single vision computer glasses! (Just a hint here: when your livelihood depends upon a durable medical appliance- even if your employer or insurance doesn't pay for it: BUY IT!) The good news is that the second par of specs was massively discounted, and I should have them in time for my return from vacation.
One final note before I call it a night: my twitter account @CjoelHarrison grew 10% in twenty-four hours!
As always, I an hochspeyer, blogging data analysis and management so you don't have to.
If you filled that blank with "the bathroom" congratulations! You, Freddie Mercury and I think alike. I believe Freddie Mercury claimed to have come up with the idea and melody for "Crazy Little Thing Called Love" in ten minutes while soaking in the bathtub in Műnchen (Munich), Germany, and the same thing happened to me Thursday evening!
Well, sort of... I didn't actually come up with a #1 U.S. Billboard chart single for a supergroup in a hotel in the Black Forest in Germany, but I think I've come up with the way I want to get my Lego data into Access.
A few years back, I had designed a flat database for my Lego collection in Microsoft Excel 2003. It was pretty nice and fit my needs at the time quite nicely. It was composed of several pages where the data was entered, and then a couple of pages which gave some bird's eye level analysis via links to the data pages. The project was scrapped mainly due to my impatience with data entry and my inability to actually get an accurate count of all of those elements- besides, they are much more fun to build with than count.
So, my plan is to go back to that format, but instead of merely having links within the workbook, have links from the Excel 2007 workbook to the Access 2007 database. And why 2007 rather than 2010 or 2013? Expediency: the Legos are close to a machine in the Secret Underground Lair (SUL) which runs 2007. The SUL redo is going very slowly, but if I am successful in liberating space for my laptop in my corner of the SUL, then it will be done on the 2013 versions of Excel and Access.
I'd like to get started on this first- creating the spreadsheet with the data, and then importing the data into an Access table and then (hopefully!) linking each data cell in Excel to its corresponding Access field so that the database can be updated via Excel. Its been some time since I did this, and it was on the "pre-ribbon" interface, so it looks like I'm going to be relearning some Excel tricks.
In other data news, Jennifer and I have been walking more as the weather has become a bit more pleasant. My replacement pedometer (identical to my expired Omron model) is chugging along, but its going to be at least another month before I have daily month-to-month data available.
As to my new glasses: the lady who fitted me for frames was slightly surprised as to how quickly I adjusted to my new spectacles, although I think I'm still breaking them in. She also said something that caught me off-guard: I wear my lenses lower than most folks.
I've never worn gradient lenses before- I've worn reading glasses since the age of eighteen and I'm now 50+; my new specs have three prescriptions per lens: top for distance, middle for computer work and bottom for reading. Although the glasses really do a good job (especially while driving), they are singularly ineffective in the place where I need them the most: computer. Here's the deal: before I went in for the exam, I had made some careful observations and mental notes about my work environment and how I typically used glasses, as well as my concerns about night driving. The gradient lenses the optometrist came up with (pretty much six prescriptions- three per lens!) are fantastic for general purpose applications (life!). My hope in getting this type of lens would be avoiding a second pair of glasses. To quote "Mad Dog" Tannen, "You thought wrong, dude." (*BLAM*) I was very quickly able to pick up the reading area of the lenses, and the distance area (which I don't really need but seems to be beneficial in low light/nighttime conditions) also "snapped" into place pretty quickly. I really need the computer part, though, and this is where I'm experiencing issues. Simply put: the lens cannot focus on the whole screen- even when I push the frame up tight to my face for the best focus. In the "good ole" 15" CRT days, these glasses would have been miraculous; with the 23" CRT's I have at work and at home, the lenses are just not up to it. And honestly, at home it really doesn't matter, because I'm generally not looking at the whole screen. At work, though- I do a lot of page layout and composition, and I generally NEED to take in the whole 23" diagonal screen at a glance- and more often than not, at a 90 degree offset. So, after only two days, I was back getting fitted for single vision computer glasses! (Just a hint here: when your livelihood depends upon a durable medical appliance- even if your employer or insurance doesn't pay for it: BUY IT!) The good news is that the second par of specs was massively discounted, and I should have them in time for my return from vacation.
One final note before I call it a night: my twitter account @CjoelHarrison grew 10% in twenty-four hours!
As always, I an hochspeyer, blogging data analysis and management so you don't have to.
Saturday, October 25, 2014
Tables and chairs
In my previous post, The Elusive Balance, I had mentioned that my goal was to enter twenty-three CDs into the Media_Title table. I am happy to report that this goal was reached, and the rack they were destined to fill is now completely occupied. After doing the updates to this table, however, I discovered some flaws in my "main" media table, which has caused me to reconsider the design of my database.
I wish I could remember where I had read this, but someone once wrote that the best way to start a database is to design it with pencil and paper. Although I've always agreed in principle that this was a great idea, in all of the databases which I've designed, I've rarely heeded this sage advice. This is partly because planning things often results in frustration and headaches for me, but also partly because I can "see" with my mind's eye what the database will look like and what it will do prior to a single keystroke being executed (note to the faint of heart and the true database newbs: semi-professional DBA on a closed course. Do not attempt at home or work. Especially at work).
One other caveat is necessary when discussing database design: don't be too afraid of change.
When the idea of Forty-Two first came into my mind is hard to say- it predates this blog by a few years at the very least. The concept was fairly modest, at first, and didn't even actually have music or media as its primary focus. It was actually my Lego collection. This was another project that grew by fits, false starts, and occasional bursts of inspiration and diligence. And it was done entirely in Excel. Over time, though, I saw the possibility of building something more powerful (and consequently more useful) in Access.
The earliest Access versions- starting in Access 2003, then moving to 2007 and finally 2010- were not grand by any stretch of the imagination, and were at first only intended to catalog music CDs. Eventually, movies seemed to be a natural addition and were incorporated, followed by books, software, console games and finally books in the latest iteration. And all of it goes back to Lego.
One of the problems I have with Lego elements is that although I do not have a large collection by many AFOL (Adult Fan of Lego) collectors' standards, I have enough to necessitate them being stored in different containers and in different locations in our home. As I was working in Excel, I found that I didn't have a really good way of taking this into account. When I realized that Access could handle this particular problem, another thought occurred to me: I could do this with media as well, and eventually incorporate things that were totally unrelated, and this could be useful in insurance planning.
That's the short version of the history of Forty-Two. The immediate future, as alluded to in the first paragraph, will see the end of what I often refer to as the "primary" table. It will be replaced by several (relatively) smaller tables, each being tasked with holding a specific type of media: music, videos, books, etc. Other tables will host data on Legos and electronics, for starters. And lastly there will be the helper table which exist primarily to normalize the database.
Whew! A whole blog post devoted to data and nothing but data. At this point some may be wondering what is up with the chairs in the title? Well, "Tables" alone sounded boring; "chairs" is pretty much an attempt at a hook to draw a potential reader in.
As always, I am hochspeyer, blogging data analysis and management so you don't have to.
I wish I could remember where I had read this, but someone once wrote that the best way to start a database is to design it with pencil and paper. Although I've always agreed in principle that this was a great idea, in all of the databases which I've designed, I've rarely heeded this sage advice. This is partly because planning things often results in frustration and headaches for me, but also partly because I can "see" with my mind's eye what the database will look like and what it will do prior to a single keystroke being executed (note to the faint of heart and the true database newbs: semi-professional DBA on a closed course. Do not attempt at home or work. Especially at work).
One other caveat is necessary when discussing database design: don't be too afraid of change.
When the idea of Forty-Two first came into my mind is hard to say- it predates this blog by a few years at the very least. The concept was fairly modest, at first, and didn't even actually have music or media as its primary focus. It was actually my Lego collection. This was another project that grew by fits, false starts, and occasional bursts of inspiration and diligence. And it was done entirely in Excel. Over time, though, I saw the possibility of building something more powerful (and consequently more useful) in Access.
The earliest Access versions- starting in Access 2003, then moving to 2007 and finally 2010- were not grand by any stretch of the imagination, and were at first only intended to catalog music CDs. Eventually, movies seemed to be a natural addition and were incorporated, followed by books, software, console games and finally books in the latest iteration. And all of it goes back to Lego.
One of the problems I have with Lego elements is that although I do not have a large collection by many AFOL (Adult Fan of Lego) collectors' standards, I have enough to necessitate them being stored in different containers and in different locations in our home. As I was working in Excel, I found that I didn't have a really good way of taking this into account. When I realized that Access could handle this particular problem, another thought occurred to me: I could do this with media as well, and eventually incorporate things that were totally unrelated, and this could be useful in insurance planning.
That's the short version of the history of Forty-Two. The immediate future, as alluded to in the first paragraph, will see the end of what I often refer to as the "primary" table. It will be replaced by several (relatively) smaller tables, each being tasked with holding a specific type of media: music, videos, books, etc. Other tables will host data on Legos and electronics, for starters. And lastly there will be the helper table which exist primarily to normalize the database.
Whew! A whole blog post devoted to data and nothing but data. At this point some may be wondering what is up with the chairs in the title? Well, "Tables" alone sounded boring; "chairs" is pretty much an attempt at a hook to draw a potential reader in.
As always, I am hochspeyer, blogging data analysis and management so you don't have to.
Monday, September 29, 2014
A cautionary tale of fixed values used in calculating extrapolated fields
Since writing A Tale of Two Databases, I've dusted off my Excel worksheet that I use to keep tabs on blog readership. Yes, deep, deep, way deep down inside of me- somewhere- there's a tiny bit of statistician. And this statistician, who lives somewhere on the Continuum-, er, that is, the Spectrum- likes to keep track of statistics about the blog.
A bit of background is in order at this juncture. I've long been a fan of collecting facts and figures- at least since high school. The year I was a freshman in high school, a new store opened that was on my way to school. The shop was named "The Sutler's Wagon", and Jeff Urquhart (the store has long since closed) was the proprietor. It was a hobby shop. Even back then, I LOVED hobby shops.
Prior to The Sutler's wagon opening, there were three shops I frequented whenever I could get my Dad to take me to them. One was Pilot. I don't remember the whole name of the business, but Pilot was more of a hardware store with a pretty bustling O scale and S scale tinplate selection and HO scale model railroading. Pilot was actually pretty close to Jeff's shop, and was derisively referred to as "the Pirate". Stanton in Chicago was a fairly large store, stocking everything from N to G scale railroading, as well as rockets, RC, plastic models, flying balsa planes... pretty much anything one could ask for in hobbies. The last store was Hill's in Park Ridge, which seemed to have a product range similar to Stanton but smaller selection.
The Sutler's Wagon was different. No trains. A couple of rockets. Some model cars. Everything else was military. Books. Models. Boardgames. Paint. Brushes. Miniatures. Miniatures in 15mm, 25mm, 54mm, 1/35th, 1/72nd, 1/144th, 1/700th, 1/1200th and 1/2400th scale. The 15's, 25's, 1/144th, and 1/2400th were generally diecast metal; the others were generally plastic. He had rules for the games- these miniatures were not just for looking pretty- they were meant for action!
And this has what to do with anything?
I've mentioned this game before. In a very real way, Fletcher Pratt's Naval Wargame taught me long division. While playing the game is fun. I think I had almost as much fun making the ship (data) cards. In the game, each ship has a point value. The point value is based on a number of data which need to be factored into an equation. That's the easy part. From the total point value, one must then create a damage table which shows how a ship's performance is degraded as she takes damage. So, say a ship has a point value of 10,000 and a max speed of 20 knots. The ship takes 1000 points of damage. 10,000/20= 500, so for every 500 points of damage, the ship would lose 1 knot of maximum speed. It's not perfect, and is designed to work best with WWI and WWII vessels, but it does work rather well. There are other factors that can make the game quite involved, but overall it is great fun.
From this point, I started looking at music. On the same block as The Sutler's Wagon was a music store called RPM records. Along with Rolling Stone Records in Chicago, I would scour their bargain "cutout" bins. I discovered a lot of great music in these bargain bins, and by the time I got to college, I had a few hundred albums. My earliest music databases were handwritten on loose leaf paper- I counted an album as being played when I had listened to at least one side.- this worked for both LPs and cassettes. I did have some 8-tracks, but I did not track these.
Flash forward many years. On a normal day I'm on a PC or two at work, and three PCs (simultaneously) at home. In all honesty, a lot of what I do currently is online gaming, but I also write this blog, do a bit of research, maintain a few spreadsheets, and work on my database, Sometimes, I even take a bit of time off to play a game or two on Steam (user= drehenthalerhof).
Here's a lesson for all of you budding data analysts, data scientists, statisticians and anyone else who uses spreadsheets and databases: if the datum doesn't look right, don't assume it is. Check it- even if you are the one that entered it!
So there I was Saturday night, updating the Excel blog readership spreadsheet. This is not a particularly complicated spreadsheet- fifty rows and thirteen columns. As I've repeatedly mentioned, Google's free analytics aren't too deep- they're pretty much stats, and they only show the Top 10. And- because they're real time, if I miss a day or two, I can potentially miss some data. So I've had extrapolate to get a more accurate picture. At some point, though, I accidentally overwrote a formula- and my beautiful table of extrapolated data suddenly was being calculated by a constant instead of a variable! I figured out the error quickly enough, but one can't count on an easy fix every time. When I suspect a problem, or when I need to do a QC on an Excel worksheet, CTRL+~ is my goto.
As always, I am hochspeyer, blogging data analysis and management so you don't have to.
A bit of background is in order at this juncture. I've long been a fan of collecting facts and figures- at least since high school. The year I was a freshman in high school, a new store opened that was on my way to school. The shop was named "The Sutler's Wagon", and Jeff Urquhart (the store has long since closed) was the proprietor. It was a hobby shop. Even back then, I LOVED hobby shops.
Prior to The Sutler's wagon opening, there were three shops I frequented whenever I could get my Dad to take me to them. One was Pilot. I don't remember the whole name of the business, but Pilot was more of a hardware store with a pretty bustling O scale and S scale tinplate selection and HO scale model railroading. Pilot was actually pretty close to Jeff's shop, and was derisively referred to as "the Pirate". Stanton in Chicago was a fairly large store, stocking everything from N to G scale railroading, as well as rockets, RC, plastic models, flying balsa planes... pretty much anything one could ask for in hobbies. The last store was Hill's in Park Ridge, which seemed to have a product range similar to Stanton but smaller selection.
The Sutler's Wagon was different. No trains. A couple of rockets. Some model cars. Everything else was military. Books. Models. Boardgames. Paint. Brushes. Miniatures. Miniatures in 15mm, 25mm, 54mm, 1/35th, 1/72nd, 1/144th, 1/700th, 1/1200th and 1/2400th scale. The 15's, 25's, 1/144th, and 1/2400th were generally diecast metal; the others were generally plastic. He had rules for the games- these miniatures were not just for looking pretty- they were meant for action!
And this has what to do with anything?
I've mentioned this game before. In a very real way, Fletcher Pratt's Naval Wargame taught me long division. While playing the game is fun. I think I had almost as much fun making the ship (data) cards. In the game, each ship has a point value. The point value is based on a number of data which need to be factored into an equation. That's the easy part. From the total point value, one must then create a damage table which shows how a ship's performance is degraded as she takes damage. So, say a ship has a point value of 10,000 and a max speed of 20 knots. The ship takes 1000 points of damage. 10,000/20= 500, so for every 500 points of damage, the ship would lose 1 knot of maximum speed. It's not perfect, and is designed to work best with WWI and WWII vessels, but it does work rather well. There are other factors that can make the game quite involved, but overall it is great fun.
From this point, I started looking at music. On the same block as The Sutler's Wagon was a music store called RPM records. Along with Rolling Stone Records in Chicago, I would scour their bargain "cutout" bins. I discovered a lot of great music in these bargain bins, and by the time I got to college, I had a few hundred albums. My earliest music databases were handwritten on loose leaf paper- I counted an album as being played when I had listened to at least one side.- this worked for both LPs and cassettes. I did have some 8-tracks, but I did not track these.
Flash forward many years. On a normal day I'm on a PC or two at work, and three PCs (simultaneously) at home. In all honesty, a lot of what I do currently is online gaming, but I also write this blog, do a bit of research, maintain a few spreadsheets, and work on my database, Sometimes, I even take a bit of time off to play a game or two on Steam (user= drehenthalerhof).
Here's a lesson for all of you budding data analysts, data scientists, statisticians and anyone else who uses spreadsheets and databases: if the datum doesn't look right, don't assume it is. Check it- even if you are the one that entered it!
So there I was Saturday night, updating the Excel blog readership spreadsheet. This is not a particularly complicated spreadsheet- fifty rows and thirteen columns. As I've repeatedly mentioned, Google's free analytics aren't too deep- they're pretty much stats, and they only show the Top 10. And- because they're real time, if I miss a day or two, I can potentially miss some data. So I've had extrapolate to get a more accurate picture. At some point, though, I accidentally overwrote a formula- and my beautiful table of extrapolated data suddenly was being calculated by a constant instead of a variable! I figured out the error quickly enough, but one can't count on an easy fix every time. When I suspect a problem, or when I need to do a QC on an Excel worksheet, CTRL+~ is my goto.
As always, I am hochspeyer, blogging data analysis and management so you don't have to.
Sunday, September 8, 2013
#notsobigdata-a birthday, an epiphany and a conundrum
My birthday was this past week, and as birthdays go it was not spectacular. That's fine with me, as I grew up without most of the pomp and circumstance that is normally associated with kids' birthdays here in the United States. Still, as birthdays go, it wasn't bad. Jennifer and I had planned on going to the fitness center (*it's officially the fitness center to distinguish it from the gym, which is the big hardwood-floored room where basketball and volleyball are played, but since "gym" is colloquially used to mean 'a place where you go to lift weights, do cardio and sweat', all references to "gym" will refer to the weight and cardio place). I got up early, and after a few phone calls and a chat with tree trimmers who showed up unexpectedly to do some utility easement cleaning for the electric company, we were off to the gym. In retrospect, I could have slept in. However, we did have a very good workout.
The epiphany was the night before. Part of Mr. T's education this year is going to be about investing. He reads the business section of the Chicago Tribune every day, so this is really a logical progression. I figured the best way to do this would be to make a fantasy stock portfolio and track its performance in terms of profits and losses. He has Excel 2007 on his PC, so I whipped up a spreadsheet that he could use as a sort of master ledger for the exercise. I've done these before, so it came together pretty quickly- I even color-coded the cells with formulas so he wouldn't break anything.
The whole "whipping up" process took about ten minutes, and it was nearly perfect and complete. Of course, nearly == almost, and in this case there was a little issue which I had never had to deal with before.
The rules of the exercise include a provision for the paying of commissions on the virtual purchase and sale of equities, so I have a column for commissions- for the purpose of the exercise, this is set to == 10 USD. I did not build the commission directly into a purchase formula because this spreadsheet can be repurposed to do real world investing, and the ability to change the commission price should be available to the user. So, the grand total for a transaction is the purchase price ((price * number of shares) + commission). The problem is that this column ("total") appears as part of another formula which shows total available funds. As there are initially twenty-five rows for transactions, the user automatically starts off with a 250 USD deficit in their available funds, as 0*0+10==10. So, I embarked upon a quest to find the function that would take care of this.
I knew what I wanted, and a simple IF/THEN script would have done the trick, but VBA in newer versions of Microsoft Office (2007 and on) have apparently also had the developer tools revamped, as the Expression Builder I had hope to see was nowhere to be found.
Bummer.
On to Plan B: the library. My personal collection, that is. I spent a few hours pouring through my books, and came close to a solution on several occasions, but as they say, "close only counts in horse shoes and hand grenades". I found the definitive solution (and syntax) on an Excel site. (For those with inquiring minds, the function is IFSUMS.)
This made me happy. See, I have a smiley :)
As always, I am hochspeyer, blogging data analysis and management so you don't have to.
The epiphany was the night before. Part of Mr. T's education this year is going to be about investing. He reads the business section of the Chicago Tribune every day, so this is really a logical progression. I figured the best way to do this would be to make a fantasy stock portfolio and track its performance in terms of profits and losses. He has Excel 2007 on his PC, so I whipped up a spreadsheet that he could use as a sort of master ledger for the exercise. I've done these before, so it came together pretty quickly- I even color-coded the cells with formulas so he wouldn't break anything.
The whole "whipping up" process took about ten minutes, and it was nearly perfect and complete. Of course, nearly == almost, and in this case there was a little issue which I had never had to deal with before.
The rules of the exercise include a provision for the paying of commissions on the virtual purchase and sale of equities, so I have a column for commissions- for the purpose of the exercise, this is set to == 10 USD. I did not build the commission directly into a purchase formula because this spreadsheet can be repurposed to do real world investing, and the ability to change the commission price should be available to the user. So, the grand total for a transaction is the purchase price ((price * number of shares) + commission). The problem is that this column ("total") appears as part of another formula which shows total available funds. As there are initially twenty-five rows for transactions, the user automatically starts off with a 250 USD deficit in their available funds, as 0*0+10==10. So, I embarked upon a quest to find the function that would take care of this.
I knew what I wanted, and a simple IF/THEN script would have done the trick, but VBA in newer versions of Microsoft Office (2007 and on) have apparently also had the developer tools revamped, as the Expression Builder I had hope to see was nowhere to be found.
Bummer.
On to Plan B: the library. My personal collection, that is. I spent a few hours pouring through my books, and came close to a solution on several occasions, but as they say, "close only counts in horse shoes and hand grenades". I found the definitive solution (and syntax) on an Excel site. (For those with inquiring minds, the function is IFSUMS.)
This made me happy. See, I have a smiley :)
As always, I am hochspeyer, blogging data analysis and management so you don't have to.
Subscribe to:
Posts (Atom)