Andrew's Blog

To Infinity and Beyond

Author: Drooten

Bee Hive – 2016-08-26

Moving Beehives across the back yard.

Part 2 of moving.

After being stung twice last night, I didn’t want a repeat of that again. So tonight I put on my socks with the holes cut in them to act like glove extender protectors, and I wore another set of a thin bee hive suit we purchased for basic bee checks.

It was after dusk when I started out, I had planned it this way as the temperate drops dramatically around that time of night. I think it was about 10 degrees (Celcius) at the time that I performed this task.

At last, I think I have figured out how to light and keep the smoker going for the full length of time required.  For those of you having trouble, read my post on How to Light a Bee Hive Smoker.

This seemed to have done the thing, because even though there seemed to be a lot of bees around, I did not get stung!

No wonder I couldn’t get the top box off of the hive last night. the box below it was totally stuck so when I did pull off the top, almost all of the bottom frames came with it!  At least I could deal with it this time.

I taped up the entrance to the hive and wrapped the hive up so it would not come apart. Then I decided to just move it straight to its new location. Why not? This will mean that I only have to open the hive up sometime next week, probably Sunday.

 

Bee

Bee

Bee Hive – 2016-08-20

Bee Hive Update August 2016

Every week I head out to check on our beehive. It is hard not to notice them in our small back yard as they are quite prominent.

However, I have been watching a lot of You Tube videos on how people deal with their hives, and I think that due to the location and accessibility of our hives, that I am probably freaking them out when I am in the garden.

In addition, there is an entire section of our garden my wife and I seem to be less likely to go to now we have the bees. This is because the bees are aimed straight at that plot of land, and it seems to be their ‘runway’.   As the hives are only accessible from the front, I have learnt from experience that they really do not like this, it really seems to agitate them.

And so it came to pass that I felt it was time to move the hives to better enable us to access the rear and sides of the hive. While also moving it slightly further away from the neighbors by about 10 meters. Mind you there are two fences between the bee hives and the neighbors.

So after preparing the land to allow for the bee hives to be moved, I suited up.

I am yet to master the smoker. I know that sounds strange coming from someone who is capable of lighting a fire, but I think that perhaps I am not well prepared at any point in time for creating a new smoker.  Some days it works well, today.  If you ask me, bees don’t like the smoke. if anything it agitates them like a crazy man running around with his arms waving above his head.  Yet all I do is smoke them.

With both hives smoked, I decided to move the smallest hive first. Makes sense to me. Do the smaller one, check my method and then move the bigger one. Given how dark it was and how cold it is, they sure don’t like being moved!  However, the small hive had been moved, bees were everywhere and .. OMG, one of the buggers was in my suit buzzing around my head! Not a good feeling, because they really shouldn’t bet getting  in.  I found out later I had not totally zipped up one of the sides to the helmet.  So like any good bee defending the colony, I got stung.

So, having been stung, I decided that dealing with the sting was more important than moving the bigger hive. So, after removing the sting, I went back to the hive which I just moved and placed a piece of wood over the entrance.  It is recommended that when moving a hive such a short distance as this, that I should prevent the bees from leaving for a few days. It is to do with stopping the bees from returning to the original location of the hive.   I plan on taking this off in a few days, by then I am sure they will be just itching to get out.

I shall leave that for tomorrow, and take some pics of the new location then too.

 

DateTime of DayCircumstance Surrounding Sting(s)Other Comments
2015-11-186:00 PMI had just finished 'pounding' in a post using the heavy crow bar. I think a bee caught a ride on my left leg. I was not stung until I walked past the front door to the Chicken Coop. The sting was more like a surprising annoyance. In fact ants leave a worse lasting impression. The sting was gone within 15 or 20 minutes.
2015-12-195:15 PMI was inspecting the right hand side Hive, and felt it sting me through my right leather glove!
2015-12-259:00 AMI had noticed that there were small ants crawling on the right hand side hive. So I put some boiling water and dish-washing liquid into a container, and gently cleaned off the ants. At least one of the bees decided I was in participation with the ants, so I got stung on the right forearm.
2016-01-107:30 PMWhile watering, one bee decided to become kamikaze on me. Making a bee line straight for my head, it was a one on one battle between me, the water hose and a bee. The bee won, and died a fast, painful death. My head is still sore.
2016-01-166:30 PMWatering, but this time i was wearing a simple bee suit. So of course it stung me on the left thigh.
2016-02-207:00 PMHaving just finished inspecting both hives, and adding a new box to one of the hives, I was walking away and one stung me on the instep of my left ankle. Approx 15 seconds later another one stung me on the out step of my ankle. I am convinced that this was a synchronized kamikaze attempt to ward me from the hive.
2016-03-037:00 PMI was inspecting the right hive. Fully suited up and wearing as much protection as possible. One bee stung me through my suit on the right lower forearm - somehow getting through the top part of my glove. Later on I found out that this bee managed to sting me twice (or perhaps it was two bees?).

Then, at my right ankle. What is it about ankles? I had three or four stings take me out. One in particular really hurt! I just remember thinking, Ill grin and bear it.

Over all, that is about six stings. More stings than I have ever had at once before.
So, I noticed my face start to swell up afterwards. I had never noticed this action before, so I thought it might be best to head off down to the ER and see if something should be done with it. Needless to say I got a shot of adrenaline and some antihistamine tablets. Four hours later, I was released and felt very tired for the next few days.

I have decided to leave the bees alone for a few weeks!
2016-03-087:00 PMI was watering the pot plants. It has never been an issue before, but a bee decided to attack me. I am no where near the bee hive, but still it seems I am on the agenda! This time it is the back of my left hand. Here is for hoping nothing further comes out of this sting.
2016-08-206:25 PM (after Sunset)I had decided to move both my hives so they are not as close to the neighbors. After discussing the new location and setting up the new location, I decided to wait until sunset, when the temperature would drop to about 13 degrees Fahrenheit. I had almost finished moving one of the hives when I noticed a buzzing sound slightly louder then usual only to find that the bugger had gotten into my suit and on my face! Bastard stung me about 1cm left of my left eye. While I have not finished moving both hives, I decided it was more important to get this sting out of my eye. So, one hive moved thus far.
2016-08-256:30 PM (after sunset)Not sure what I was thinking. I didn't wear the full suit. I could not find the protective sock coverings which I usually wear (long socks with five holes cut out of them that protect my wrists and arms).

One bee stung me through my leather gloves on the left wrist, and then moments later another bee stung me on the right wrist.

I was able to get them out quick enough. Though while the sting on my right dissipated quickly, the one on the left swelled for quite a few hours.
2016-08-289:15 AMI should have been wearing a hat!
I was pruning the Tamarillo tree (it is a cousin to the tomato plant) and a bee landed on my hair. Instead of flying off, it kept buzzing and must have gotten itself caught. The inevitable happened and I got stung on my head. Lucky my wife was around to get it out.

DateTime and DateTimeOffset

DateTime and DateTimeOffset

for more information, reference: https://blogs.msdn.microsoft.com/bartd/2009/03/31/the-death-of-datetime/

As of SQL 2008 a new and improved DateTime datatype is available, called DateTimeOffset.

I suspect it will be difficult for many coders to move away from the DateTime datatype, however there are several benefits to using the DateTimeOffset datatype.

The DateTimeOffset stores the current time as well as the offset of that time from UTC time. This becomes useful when comparing DateTimes that occurred across time zones, or between Daylight Savings Times.

The DateTimeOffset requires upto 2 extra Bytes over the original DateTime DataType.

SELECT SYSDATETIMEOFFSET()

Identify what your SQL SErvers UTF offset currently is:

SELECT DATENAME (TZoffset, SYSDATETIMEOFFSET())

–Variable provided calculated.

DECLARE @N DATETIMEOFFSET;
SET @N = GETDATE();
SELECT TODATETIMEOFFSET (@N, DATENAME (TZoffset, SYSDATETIMEOFFSET()))

VB Dot Net Threading implementing System.ComponentModel BackgroundWorker

This article explains the basics of Dot Net Threading, provides a simplistic diagram of how to implement a Background Worker into code, AND I provide VB .NET code that demonstrates one application with three Threads showing different ways to implement Background Worker Thread so as to update controls, objects and variable from a form. Hopefully I have done this in a constructive way so that you, the novice yet to learn about threading, or the ‘I am about to pull my hair out’ coder can take it and see how easy it is to implement a thread.  BE WARNED. There is absolutely no error coding in this application as the point was to show you Threading, not Error Control.  Perhaps I could make that another topic?

Threading. You are either a programmer who understand what it is, or you probably are not a programmer.

When I say the word ‘Multitasking’ most people have an understanding that computers can multitask, and females love to say they can multitask, and employees try to say they are great at their job because they multitask,  yet they would have no idea about CPU Time Slicing or what effort it takes to make YOUR program Thread Safe.

I am somewhat bemused when I tell people that I’ve been doing some programming and how I needed to do some threading, and their response implies that I have a Microsoft Excel Spreadsheet open and am having difficulty with a formula.  Um. No.

How easy is threading?  On a scale of 1 : 10, I’d rate it about a 7. or an 8.  If you have a framework, it still takes care and consideration.  Compared with anything I do at work, Threading is FAR more complicated.

Enough crapping.. bollocking on!  Let’s do this.

  1. Pictures tell a thousand words, and here is my Play School Style Picture (oops, I meant to say the Parallel System Support Program).  
  2. And below I illustrate the classes in my application.  Note that I have three threads, depicted as Thread_A, Thread_B, Thread_C. Each Thread requires three classes.
    I personally like to name classes so that I can quickly and easily identify what they do.  If you prefer to use ambiguous names feel free to call them what you like.
    I use one class to store a ‘database table’ of types (clsTableStorage, which creates a Data Table consisting of four columns), and then there is the main form, which has the controls on it for testing.  Don’t worry, you don’t have to DO anything – this is currently working in Visual Studio 2010 with Dot Net 4.0Classes for Threading
  3. On first Load, I have Three lines of Controls (two labels, a Progress Bar, one label) Then two Command buttons followed by a Data Grid View. Your mission, should you accept it, is to press the Run button. OnLoad - Form Background Worker Thread
  4. After you pressed the run button, you probably saw all three lines of controls, display different things. Each Line represents a different thread.  They should run fairly fast as the thread is nothing more than looping a For Loop from 0 to 500, and updating the controls as it goes. In the case of the Data Grid View, I only update the grid every 50th Record.Completed - Form Background Worker Thread
  5. Ah, so what?! I hear you exclaim. Well., there are three different models being used to update these controls:
    1. The first Thread updates the Controls directly.  I pass the name of each and every control to the Thread, and it updates those controls.  If you have not had threading work before, this seems like a miracle cure… but if you have more than five controls to update, it is going to be a pain in the neck!   Still . it works.
    2. This thread passes in a Data Table in the form of a variable.  The thread only updates the Data Table.  The way that the controls get updated is that before calling the thread, I bind the controls to the Data Table as if it were a Data Source (which it is in fact). There are downsides to this method.  Downside 1. As  you will see when you run the code, the Progress Bar will not update during the thread. Downside 2: the Data Table can only store ONE RECORD for this to work.  If you can make it work with a Data Table that stores two or more, please let me know as I did try to include that as the next example.
    3. This thread uses a Data Table (obviously) but I pass in the Progress bar AND I prove to you that you can modify the form side variable (the Data Table) while the thread is in play.  After you have clicked ‘Run’ (and don’t wait for the code to end) Simply click ‘Force Record into Data Table’, and a dummy record will be ‘injected’ into the Data Table.  If you look at the code, you will see there are no tricks here. The Data Grid View is bound to the Data Table.  I think this is pretty cool!
  6. Go ahead and check out the code!  I suggest commenting out one section of thread while you identify how the other ones work… but you will find that they work the way I describe it in my Play School Style hand out.
  7. If your computer is not very powerful, you may need to extend the
    Form1 Constant;
    Const THREADSLEEPTIME As Integer =1,
    to
    Const THREADSLEEPTIME As Integer = 3

 

In case you missed it, the VB.NET code, and I really don’t know  how you did so, it is here.  and way over there (Look right).                                            ->

 

Since creating this post, I modified the Thread Example to include a fourth thread.  This fourth thread runs from within the for loop, presented below.  This is imitating a section (or sections) of code where the guts of the code can take a while to calculate. In my case I send it off to calculate a Fibonacci Number, and I am happy to report that doing so slowed down the application… slightly.

The

I also took the time to improve some small issues… but nothing major.

I also noticed, that at home on my 6 year old laptop, that the threads required a longer THREADSLEEPTIME than on my good  computer, which was actually a  surprise.  What was interesting was that as the night went on, the laptop became more responsive towards the program.

 

<! back to top>

Bee Hive – 2015-12-19

I have no pictures today as normally I get someone else to take pictures while I perform the autopsy 🙂

Temperature during the inspection today was a balmy 39 Degrees, which is cooler than the 44 Degrees (Celsius) it reached earlier on today.

My left hand hive is finally taking off, so it is a good job I have some spare brood boxes in the shed. Next month I will be adding another brood box on top.

The Right had hive, which I added a second Brood box to late last month, is already looking really good.  Most of the frames have bee wax on them and there are some signs of frames with honey on them already. I am looking forward to being able to take a frame or two out at some time to collect my first bit of honey, but I am no where near ready for that yet.

DateTime of DayCircumstance Surrounding Sting(s)Other Comments
2015-11-186:00 PMI had just finished 'pounding' in a post using the heavy crow bar. I think a bee caught a ride on my left leg. I was not stung until I walked past the front door to the Chicken Coop. The sting was more like a surprising annoyance. In fact ants leave a worse lasting impression. The sting was gone within 15 or 20 minutes.
2015-12-195:15 PMI was inspecting the right hand side Hive, and felt it sting me through my right leather glove!
2015-12-259:00 AMI had noticed that there were small ants crawling on the right hand side hive. So I put some boiling water and dish-washing liquid into a container, and gently cleaned off the ants. At least one of the bees decided I was in participation with the ants, so I got stung on the right forearm.
2016-01-107:30 PMWhile watering, one bee decided to become kamikaze on me. Making a bee line straight for my head, it was a one on one battle between me, the water hose and a bee. The bee won, and died a fast, painful death. My head is still sore.
2016-01-166:30 PMWatering, but this time i was wearing a simple bee suit. So of course it stung me on the left thigh.
2016-02-207:00 PMHaving just finished inspecting both hives, and adding a new box to one of the hives, I was walking away and one stung me on the instep of my left ankle. Approx 15 seconds later another one stung me on the out step of my ankle. I am convinced that this was a synchronized kamikaze attempt to ward me from the hive.
2016-03-037:00 PMI was inspecting the right hive. Fully suited up and wearing as much protection as possible. One bee stung me through my suit on the right lower forearm - somehow getting through the top part of my glove. Later on I found out that this bee managed to sting me twice (or perhaps it was two bees?).

Then, at my right ankle. What is it about ankles? I had three or four stings take me out. One in particular really hurt! I just remember thinking, Ill grin and bear it.

Over all, that is about six stings. More stings than I have ever had at once before.
So, I noticed my face start to swell up afterwards. I had never noticed this action before, so I thought it might be best to head off down to the ER and see if something should be done with it. Needless to say I got a shot of adrenaline and some antihistamine tablets. Four hours later, I was released and felt very tired for the next few days.

I have decided to leave the bees alone for a few weeks!
2016-03-087:00 PMI was watering the pot plants. It has never been an issue before, but a bee decided to attack me. I am no where near the bee hive, but still it seems I am on the agenda! This time it is the back of my left hand. Here is for hoping nothing further comes out of this sting.
2016-08-206:25 PM (after Sunset)I had decided to move both my hives so they are not as close to the neighbors. After discussing the new location and setting up the new location, I decided to wait until sunset, when the temperature would drop to about 13 degrees Fahrenheit. I had almost finished moving one of the hives when I noticed a buzzing sound slightly louder then usual only to find that the bugger had gotten into my suit and on my face! Bastard stung me about 1cm left of my left eye. While I have not finished moving both hives, I decided it was more important to get this sting out of my eye. So, one hive moved thus far.
2016-08-256:30 PM (after sunset)Not sure what I was thinking. I didn't wear the full suit. I could not find the protective sock coverings which I usually wear (long socks with five holes cut out of them that protect my wrists and arms).

One bee stung me through my leather gloves on the left wrist, and then moments later another bee stung me on the right wrist.

I was able to get them out quick enough. Though while the sting on my right dissipated quickly, the one on the left swelled for quite a few hours.
2016-08-289:15 AMI should have been wearing a hat!
I was pruning the Tamarillo tree (it is a cousin to the tomato plant) and a bee landed on my hair. Instead of flying off, it kept buzzing and must have gotten itself caught. The inevitable happened and I got stung on my head. Lucky my wife was around to get it out.
DateTime of DayCircumstance Surrounding Sting(s)Other Comments
2015-11-306:350 AMApparently 2 bees went a little too close to her while she was picking lettuce in the garden. They got tangled up in her hair (she tried so hard not to react to them). One flew away but the other got disorientated and stung her on the back of the neck.What surprised me is how long she took to come to me and ask.. 'Do I have a bee sting in my neck?' A whole 5 minutes. Anyway, when I removed the sting, she had no swelling or any signs of a reaction to the sting whatsoever. PHEW!
2015-12-237:40 PMDuring general gardening, it seems a bee wanted to explore my wifes head. Having long hair, they seem to get tangled and possibly even confused, so in this case it decided to sting her. Now we are not sure if she was stung twice or once, but I could only find one sting lodged in her head.In future, I think we should just wear a hat when around the hives.

 

Bee Hive – Record of Bee Stings

I have decided to try to keep a record of each time I get stung by a bee. It just seems like a logical thing to do, for someone like me who deals with ‘data’ on a daily basis.

DateTime of DayCircumstance Surrounding Sting(s)Other Comments
2015-11-186:00 PMI had just finished 'pounding' in a post using the heavy crow bar. I think a bee caught a ride on my left leg. I was not stung until I walked past the front door to the Chicken Coop. The sting was more like a surprising annoyance. In fact ants leave a worse lasting impression. The sting was gone within 15 or 20 minutes.
2015-12-195:15 PMI was inspecting the right hand side Hive, and felt it sting me through my right leather glove!
2015-12-259:00 AMI had noticed that there were small ants crawling on the right hand side hive. So I put some boiling water and dish-washing liquid into a container, and gently cleaned off the ants. At least one of the bees decided I was in participation with the ants, so I got stung on the right forearm.
2016-01-107:30 PMWhile watering, one bee decided to become kamikaze on me. Making a bee line straight for my head, it was a one on one battle between me, the water hose and a bee. The bee won, and died a fast, painful death. My head is still sore.
2016-01-166:30 PMWatering, but this time i was wearing a simple bee suit. So of course it stung me on the left thigh.
2016-02-207:00 PMHaving just finished inspecting both hives, and adding a new box to one of the hives, I was walking away and one stung me on the instep of my left ankle. Approx 15 seconds later another one stung me on the out step of my ankle. I am convinced that this was a synchronized kamikaze attempt to ward me from the hive.
2016-03-037:00 PMI was inspecting the right hive. Fully suited up and wearing as much protection as possible. One bee stung me through my suit on the right lower forearm - somehow getting through the top part of my glove. Later on I found out that this bee managed to sting me twice (or perhaps it was two bees?).

Then, at my right ankle. What is it about ankles? I had three or four stings take me out. One in particular really hurt! I just remember thinking, Ill grin and bear it.

Over all, that is about six stings. More stings than I have ever had at once before.
So, I noticed my face start to swell up afterwards. I had never noticed this action before, so I thought it might be best to head off down to the ER and see if something should be done with it. Needless to say I got a shot of adrenaline and some antihistamine tablets. Four hours later, I was released and felt very tired for the next few days.

I have decided to leave the bees alone for a few weeks!
2016-03-087:00 PMI was watering the pot plants. It has never been an issue before, but a bee decided to attack me. I am no where near the bee hive, but still it seems I am on the agenda! This time it is the back of my left hand. Here is for hoping nothing further comes out of this sting.
2016-08-206:25 PM (after Sunset)I had decided to move both my hives so they are not as close to the neighbors. After discussing the new location and setting up the new location, I decided to wait until sunset, when the temperature would drop to about 13 degrees Fahrenheit. I had almost finished moving one of the hives when I noticed a buzzing sound slightly louder then usual only to find that the bugger had gotten into my suit and on my face! Bastard stung me about 1cm left of my left eye. While I have not finished moving both hives, I decided it was more important to get this sting out of my eye. So, one hive moved thus far.
2016-08-256:30 PM (after sunset)Not sure what I was thinking. I didn't wear the full suit. I could not find the protective sock coverings which I usually wear (long socks with five holes cut out of them that protect my wrists and arms).

One bee stung me through my leather gloves on the left wrist, and then moments later another bee stung me on the right wrist.

I was able to get them out quick enough. Though while the sting on my right dissipated quickly, the one on the left swelled for quite a few hours.
2016-08-289:15 AMI should have been wearing a hat!
I was pruning the Tamarillo tree (it is a cousin to the tomato plant) and a bee landed on my hair. Instead of flying off, it kept buzzing and must have gotten itself caught. The inevitable happened and I got stung on my head. Lucky my wife was around to get it out.

 

Alas, it finally happened. My beautiful wife got her very first ever bee sting.  Allow me to reiterate.  My wife has gone her entire life without ever being stung by a bee.

DateTime of DayCircumstance Surrounding Sting(s)Other Comments
2015-11-306:350 AMApparently 2 bees went a little too close to her while she was picking lettuce in the garden. They got tangled up in her hair (she tried so hard not to react to them). One flew away but the other got disorientated and stung her on the back of the neck.What surprised me is how long she took to come to me and ask.. 'Do I have a bee sting in my neck?' A whole 5 minutes. Anyway, when I removed the sting, she had no swelling or any signs of a reaction to the sting whatsoever. PHEW!
2015-12-237:40 PMDuring general gardening, it seems a bee wanted to explore my wifes head. Having long hair, they seem to get tangled and possibly even confused, so in this case it decided to sting her. Now we are not sure if she was stung twice or once, but I could only find one sting lodged in her head.In future, I think we should just wear a hat when around the hives.

Bee Hive – 2015-11-15

 

After speaking to a few people, I was informed that the reason Bees might be creating wax on the ceiling, was that they were cold. The advice was to put lino into the top of the hive. I could not get Lino: I did try. I went to a Carpet place and asked if they had any off-cuts.  Nope. Not unless I wanted to purchase a $70 roll.

So I found some plastic mats which seemed to look like they may do the job. I cut them to size and below are the pics of those being implanted into the hives.

2015-11-15 Observation:

The hives are a little bit more detailed, as I tried to take pics of everything as I went.

2015-11-14 Right More Active

 

 

 

 

 

 

 

Left Hand Hive (closest to current Green House) :

This hive seems to have covered over 2 and a bit frames.   I removed one of the frames from this hive to put into the right hand hive.

2015-11-14 Left 2 Frames Close up2015-11-14 Left Lino

 

 

 

 

 

 

 

 

Right Hand Hive (further from Green House):

Almost every frame here, but one (because it doesn’t have wax attached to it) is totally covered in bees.  I took the spare frame from the left, and placed it into the fight frame.

2015-11-14 Right 1 Frame Empty

 

 

 

 

 

 

 

 

Lessons Learnt:

  1. Nothing new here. Putting all the tools and implements I needed onto a table first made working a whole lot easier!

2015-11-14 Preparation is Key

 

 

 

 

 

 

DateTime of DayCircumstance Surrounding Sting(s)Other Comments
2015-11-186:00 PMI had just finished 'pounding' in a post using the heavy crow bar. I think a bee caught a ride on my left leg. I was not stung until I walked past the front door to the Chicken Coop. The sting was more like a surprising annoyance. In fact ants leave a worse lasting impression. The sting was gone within 15 or 20 minutes.
2015-12-195:15 PMI was inspecting the right hand side Hive, and felt it sting me through my right leather glove!
2015-12-259:00 AMI had noticed that there were small ants crawling on the right hand side hive. So I put some boiling water and dish-washing liquid into a container, and gently cleaned off the ants. At least one of the bees decided I was in participation with the ants, so I got stung on the right forearm.
2016-01-107:30 PMWhile watering, one bee decided to become kamikaze on me. Making a bee line straight for my head, it was a one on one battle between me, the water hose and a bee. The bee won, and died a fast, painful death. My head is still sore.
2016-01-166:30 PMWatering, but this time i was wearing a simple bee suit. So of course it stung me on the left thigh.
2016-02-207:00 PMHaving just finished inspecting both hives, and adding a new box to one of the hives, I was walking away and one stung me on the instep of my left ankle. Approx 15 seconds later another one stung me on the out step of my ankle. I am convinced that this was a synchronized kamikaze attempt to ward me from the hive.
2016-03-037:00 PMI was inspecting the right hive. Fully suited up and wearing as much protection as possible. One bee stung me through my suit on the right lower forearm - somehow getting through the top part of my glove. Later on I found out that this bee managed to sting me twice (or perhaps it was two bees?).

Then, at my right ankle. What is it about ankles? I had three or four stings take me out. One in particular really hurt! I just remember thinking, Ill grin and bear it.

Over all, that is about six stings. More stings than I have ever had at once before.
So, I noticed my face start to swell up afterwards. I had never noticed this action before, so I thought it might be best to head off down to the ER and see if something should be done with it. Needless to say I got a shot of adrenaline and some antihistamine tablets. Four hours later, I was released and felt very tired for the next few days.

I have decided to leave the bees alone for a few weeks!
2016-03-087:00 PMI was watering the pot plants. It has never been an issue before, but a bee decided to attack me. I am no where near the bee hive, but still it seems I am on the agenda! This time it is the back of my left hand. Here is for hoping nothing further comes out of this sting.
2016-08-206:25 PM (after Sunset)I had decided to move both my hives so they are not as close to the neighbors. After discussing the new location and setting up the new location, I decided to wait until sunset, when the temperature would drop to about 13 degrees Fahrenheit. I had almost finished moving one of the hives when I noticed a buzzing sound slightly louder then usual only to find that the bugger had gotten into my suit and on my face! Bastard stung me about 1cm left of my left eye. While I have not finished moving both hives, I decided it was more important to get this sting out of my eye. So, one hive moved thus far.
2016-08-256:30 PM (after sunset)Not sure what I was thinking. I didn't wear the full suit. I could not find the protective sock coverings which I usually wear (long socks with five holes cut out of them that protect my wrists and arms).

One bee stung me through my leather gloves on the left wrist, and then moments later another bee stung me on the right wrist.

I was able to get them out quick enough. Though while the sting on my right dissipated quickly, the one on the left swelled for quite a few hours.
2016-08-289:15 AMI should have been wearing a hat!
I was pruning the Tamarillo tree (it is a cousin to the tomato plant) and a bee landed on my hair. Instead of flying off, it kept buzzing and must have gotten itself caught. The inevitable happened and I got stung on my head. Lucky my wife was around to get it out.
DateTime of DayCircumstance Surrounding Sting(s)Other Comments
2015-11-306:350 AMApparently 2 bees went a little too close to her while she was picking lettuce in the garden. They got tangled up in her hair (she tried so hard not to react to them). One flew away but the other got disorientated and stung her on the back of the neck.What surprised me is how long she took to come to me and ask.. 'Do I have a bee sting in my neck?' A whole 5 minutes. Anyway, when I removed the sting, she had no swelling or any signs of a reaction to the sting whatsoever. PHEW!
2015-12-237:40 PMDuring general gardening, it seems a bee wanted to explore my wifes head. Having long hair, they seem to get tangled and possibly even confused, so in this case it decided to sting her. Now we are not sure if she was stung twice or once, but I could only find one sting lodged in her head.In future, I think we should just wear a hat when around the hives.

Bee Hive – Things I have learnt over the years

Each time I go to deal with my Hives, I am sure Ill go there, find something new, or make a new observation.  Sure, others have been there all before and can give me ideas.

I am sure someone could create a joke around ‘How many Apiarist it would take to change a light bulb’… But seeing I am joining the

Things for me to remember when Dealing with my Bee Hives

  1. Don’t put tools which you use IN the hive on top of another hive. While there are only trickles of honey on the tools it does tend to leave a very small residue which the ants pick up on very quickly. I was gone for about 10 mins after doing this, and the ants were ‘all over’ (that is, about 50 of em) the lid of the hive.  I have placed ant pellets around the hive, but I am not sure how I feel about this approach.
  2. If any of the Frames inside the hive are damaged (or if the Wax has melted off of the frame), remove the entire frame straight away for mending. In the future, i shall have a few spare frames available in case I need to swap them in.
  3. When it comes to the Smoker, there IS a way to use it. I noticed that when I smoked the hive, they became quite pissed off. I was sure the idea of the Smoker was to calm em all down.  What is going on?  Apparently, I am supposed to wait a few minuets after smoking them, and when I do smoke them, I only need to puff them a few times and wait.

Bee Hive – 2015-11-11

As an Amateur Bee Keeper, AKA, an Apiarist, I have become responsible for several thousand bees in my backyard.  I have two hives, one is doing remarkably well and is a ‘Hive of Activity’.    The saying is true! When you stand around a Bee Hive that has a lot of bees, it is like standing besides a very busy Airplane Runway.

I have not been stung yet, but the Bees seem to be very placid. They leave me alone and I – ah hem – need to check on them at least once every 6 weeks.

As I need to keep a record, somehow, somewhere, of the observations I make of the Bee Hives, this is the way I think I’ll do it.

 

2015-11-11 Observation:

Left Hand Hive (closest to current Green House) :

At present consists of Brood Chamber.  This hive seems to have covered over 4 frames, but there are not many bees in this Bee Hive. I admit to being concerned, however as I have not ever done this before, I just need to leave it alone.

Right Hand Hive (further from Green House):

This hive seemed to be ‘full’ of bees.  In reality it probably is not.  The out most frames have no bees on them. One of the frames’ wax had become ‘unstuck’ so I removed the wax, but not the frame.  In hindsight (and in the future I shall do this) I should have removed the frame so I could re-melt the wax back onto it.

A further issue were that the bees were building their wax on the ceiling of the box. I could not remember what that meant, so at the Tuesday night Bee Keepers meeting I asked of this and the suggestion was that perhaps the bees were searching for warmth, so I need to place a piece of Lino at the top of the hive.  I have purchased the lino to put into the hive, but have yet to insert it into the hives.

Lessons Learnt:

  1. Don’t put tools which you use IN the hive on top of another hive. While there are only trickles of honey on the tools it does tend to leave a very small residue which the ants pick up on very quickly. I was gone for about 10 mins after doing this, and the ants were ‘all over’ (that is, about 50 of em) the lid of the hive.  I have placed ant pellets around the hive, but I am not sure how I feel about this approach.
  2. If any of the Frames inside the hive are damaged (or if the Wax has melted off of the frame), remove the entire frame straight away for mending. In the future, i shall have a few spare frames available in case I need to swap them in.
Checking the Bee Hive Lid

Checking the Bee Hive Lid

Close up view of the Lid

Close up view of the Lid

 

From this, wax on the lid, I gently brushed the bees back into the hive and pried the wax / honey into a clean container. From there I separated out the pieces into three small round containers to give to friends who I knew would like to try the honey.  Although, as you can see from the lower picture, there was more wax than honey in the containers.

Bedside Table

I have been creating this Bedside Table for quite about 9 months now (there just are not enough hours in a day after work).  I wanted to replace an ‘old wooden box’ which is in the guest spare room, and I think I have done a fairly good job. If I want to be picky, I could take the handles and modify the Handle color from soft light color to a harder redwood color.

So, here is the Before and After pics…
2015-10-19 Bedside Table Before2015-10-19 Bedside Table After

 

 


 

 

 

 

T-SQL Intersect Command

If you are like me, you like to find new ways of doing the same thing in different ways. Perhaps a new way is easier to read. Perhaps the query runs faster. Perhaps it will run slower? Unless you implement the ideas, try them out and perform an Execution Plan along with Timing the results, how would you ever know.

Personally another Indicator I use for coding is ‘Ease of Reading’. I don’t mean for kindergarten children.  If T-SQL were that simple we would not have jobs that pay as well as they are. T-SQL falls in the line of Adult Literacy.  For those of you who just chuckled at those two concatenated nouns, no, I am not referring to the other type of Adult Entertainment’.

 

Below is a highly simplistic form of a query I was creating.  But the point is, I wanted everyone who existed in the first table, but only if they also existed in the second table. So easy, even my Mum could write this.  7 lines of code (9 including Brackets).


SELECT DISTINCT [E].[clientno] &nbsp;--140,000 Records
FROM [dbo].[episode] AS [E]

WHERE [E].[ClientNo] IN
(
   SELECT DISTINCT [A].[clientno]  --3,100 Records
   FROM [dbo].[CHSP_RelevantEpisodes] AS [A]
)

However, as we all know, the WHERE IN is not a very efficient search. Sure it does the job, but is there a better way? Or even just another way?

Introducing: INTERSECT


SELECT DISTINCT [E].[clientno]--140,000 Records
FROM [dbo].[Episode] AS [E]

INTERSECT

SELECT DISTINCT [E].[ClientNo] --3,100 Records
FROM [dbo].[CHSP_RelevantEpisodes] AS [E]

5 lines of code, No Brackets Required.  In THIS format it is sure a lot easier to read, easier to Error Check and easier to perform Data Integrity Checks.

As far as the Execution Plan goes, the ratio WHERE IN : INTERSECT is 51%:49%, so in this case it is not a deal breaker.

HOWEVER, I am still to figure out what the go is if you need more columns from the first Table. I think I might start testing the Intersect method  over the next few weeks or so.

Segmented Wood Formulas and Calculation Spreadsheet

Feel free to download and look the associated SegmentedWood V7 – still in Draft mode.  Feedback welcome!

I was recently watching a You Tube video on Vase Turning.  In the first 3 seconds of this video, right before the gentleman turns on his lathe, you can just make out an utterly bizarre wood concatenation of glued up wood squares (albeit very fancy wood squares) stacked one on top of each other.  Comparing the first 3 seconds to the last minuet or so, it is somewhat impossible to imagine that the two pieces of wood are related.

Me being me, I just had to figure out what the heck this was.  So with a little research found some references to ‘Segmented Wood’.  Now, don’t get me wrong, there is a lot of information out there pertaining to this, but I wanted to know and understand the mathematics behind the segments.  So, I started a little Spreadsheet (promise, it is very small) which given a Diameter of two circles (ie the inside and outside diameter) this will let you see what you need to do in order to cut up and glue together just ONE of the wood segments.

In the Vase below, the Gentleman has 19 different segments (that is a whole lot of work) all glued together to create a phenomenal result!  I am not at the stage to even create a bowl yet from Segmented Wood… But maybe I can try it out in the near (far) future?

There is a whole lot of stuff missing from this spreadsheet, but perhaps it might help others with lengths and angles, etc for their wood working projects?

 

 

How long does my code take to run?

How long does my code take to run – SQL?

If you are like me, and you probably are not, then you might come across occasions where you would like to know how long your code is taking to run.  For me, it was the desire to compare code I had written against someone else code to see who’s was faster / more efficient, etc. (Bragging rights?)

In .NET I have a class file to handle this sort of thing.  Simply call the class, pass in a start and end time, and the return value is what you desire.  This method takes one preparation line, say to instantiate the class and provide a ‘start time’, then a completion line, placed after the code I want to measure, along with the ‘End Time’.  I have found it simpler to request both start and end times as this just allows you to measure between any two points in time, not just a point in time that passed moments ago and right this very moment. Or this new very moment.  etc.  My class file also handles storage of historical data for comparisons at a later date (and half a dozen other cool things that a statistician like myself might like to know over time).

I decided on writing my own SQL Function (provided below) which would allow two time inputs, manipulate the time inputs and return a table of values, allowing the caller to decide on which format or set of values they wish to display / manipulate etc.

My chosen outputs (as a table) include (but not limited to) a formatted String of HH:MM:SS:ms, and a Decimal Value representing the total Milliseconds between the two dates. As you can see from the below function I have not been interested in Year, Month, Day kind of thing, only the Hours, Minutes, Seconds and Milliseconds.  I did try to include Nanoseconds, however I still utilize a few SQL 2005 Servers and wanted to make a portable function (with few minor tweaking requirements, not included in example).  All of the below code has been tested on SQL 2012 Server.


CREATE FUNCTION [dbo].[utf_ConvertSecondsToLengthOfTime] (
 @StartTime TIME,
 @EndTime TIME
)

RETURNS @T TABLE([Hours] CHAR(2), [MINUTES] CHAR(2), [SECONDS] CHAR(2), [MILLISECONDS] CHAR(3), [HH:MM:SS:ms] CHAR(12) , [TotalMilliSeconds] DECIMAL(12,3)) 

AS

BEGIN

 DECLARE @ms as INT
 SET @ms = DATEDIFF(ms, @StartTime, @EndTime) / 1000 

 --Convert miliseconds into useable numbers
 DECLARE @Hours AS INT
 SET @Hours= @ms/3600;

 DECLARE @Minuets AS INT
 SET @Minuets = CAST(( @ms % 3600)/60 AS INT) ;

 DECLARE @Seconds AS INT
 SET @Seconds = CAST((@ms % 3600) % 60 AS INT) ;

 DECLARE @MilliSeconds AS INT
 SET @MilliSeconds =CAST(RIGHT(DATEDIFF(MILLISECOND, @StartTime, @EndTime) , 3) AS INT);

 DECLARE @Totals as DECIMAL(12,3)
 SET @Totals =(@Hours * 216000) + (@Minuets * 3600) + (@Seconds * 60) + CAST(@MilliSeconds AS DECIMAL(12,3)) / 1000;

 --Convert Numbers into formatted strings.
 -- Doing all of this in a two stage process makes error checking much simpler.

 DECLARE @sHours AS CHAR(2)
 SET @sHours = RIGHT ('00' + LTRIM(STR(@ms/3600)), 2 );

 DECLARE @sMinuets AS CHAR(2)
 SET @sMinuets = RIGHT ('00' + LTRIM(STR(@Minuets)), 2 );

 DECLARE @sSeconds AS CHAR(2)
 SET @sSeconds = RIGHT ('00' + LTRIM(STR(@Seconds)), 2 );

 DECLARE @sMilliSeconds AS CHAR(3)
 SET @sMilliSeconds = RIGHT ('00' + LTRIM(STR(@MilliSeconds)), 3) ;

 INSERT INTO @T ([Hours], [MINUTES], [SECONDS], [MILLISECONDS], [HH:MM:SS:ms] , [TotalMilliSeconds])
    VALUES (@sHours,@sMinuets, @sSeconds , @SMilliSeconds, @sHours + ':' + @sMinuets + ':' + @sSeconds + ':' + @SMilliSeconds, @Totals) ;

 RETURN

END

/*
SELECT * FROM [dbo].[utf_ConvertSecondsToLengthOfTime] (CAST('10:03:54.9398338' AS TIME) , CAST('10:03:54.9710340'AS TIME) )

SELECT * FROM [dbo].[utf_ConvertSecondsToLengthOfTime] ('10:03:54.0000000' , '10:03:54.0000000' ) --Identical
SELECT * FROM [dbo].[utf_ConvertSecondsToLengthOfTime] ('10:03:54.0000000' , '10:03:54.5555555' ) --Half a Second

SELECT * FROM [dbo].[utf_ConvertSecondsToLengthOfTime] ('10:03:54.0000000' , '10:03:55.0000000' ) --One Second
SELECT * FROM [dbo].[utf_ConvertSecondsToLengthOfTime] ('10:03:55.0000000' , '10:04:55.0000000' ) --One Minute

SELECT * FROM [dbo].[utf_ConvertSecondsToLengthOfTime] ('10:03:54.0000000' , '10:03:55.5000000' ) --One Second
SELECT * FROM [dbo].[utf_ConvertSecondsToLengthOfTime] ('10:03:55.0000000' , '10:04:55.5000000' ) --One Minute

SELECT [TotalMilliSeconds] FROM [dbo].[utf_ConvertSecondsToLengthOfTime] ('10:03:54.9398338' , '12:03:55.9710340' )
*/

And now, it is possible to calculate how long it took for a piece of SQL Code to run. Similar to the following: NB: the function name is now obsolete, but i am sure you can modify it more appropriately.


 DECLARE @StartTime Time
 SET @StartTime = CAST(SYSDATETIME() AS TIME) 

 SELECT * FROM wherever

 SELECT [TotalMilliSeconds] FROM [dbo].[utf_ConvertSecondsToLengthOfTime] (@StartTime, SYSDATETIME() )

So, I decided to put my code to the test and see how fast (slow?) it was.  Regardless of efficiency, I believe it would be simpler to call this than to have to re-write it every time I have a question on how long code is taking to run.

DECLARE @TestingTime INT =3
DECLARE @LoopStartTime DATETIME2 = GETDATE()

DECLARE @LoopEndTime DATETIME2
SET @LoopEndTime = DATEADD(s,@TestingTime,@LoopStartTime)

WHILE GETDATE() <= @LoopEndTime -- DateDiff(s, 1, @LoopStartTime)
BEGIN
 PRINT SYSDATETIME() --50,804
END

SET @LoopStartTime = GETDATE()
SET @LoopEndTime = DATEADD(s,@TestingTime,@LoopStartTime)

WHILE GETDATE() <= @LoopEndTime -- DateDiff(s, 1, @LoopStartTime)
BEGIN

 SELECT * FROM [dbo].[utf_ConvertSecondsToLengthOfTime] (@LoopStartTime, @LoopEndTime ) -- 76
END

And the results.

Seconds SYSDATETIME() Function
1 50,804 76
2 121,173 121
3 165,302 136

 

 

Fizz Buzz Woof: and the ability to code

People who can’t program still get hired as programmers.

I first heard about the Fizz Buzz code from Jeff Atwood on Coding Horror web page.  His point has nothing to do with being able to actually code a solution for ‘Fizz Buzz ‘, but the ability to code for a solution.  Fizz Buzz is just the example provided.

To be honest, I am not surprised that people get employed to program who can’t even program something as simple as ‘Fizz Buzz’! Frequently people don’t get hired because they can prove (and I mean beyond a benefit of a doubt) that they can perform well in the roll.  For instance. Take the Spelling mistake I just provided to you. Roll. The word is ‘Role’. So, if I can’t spell the word Role, then how on earth can I be a good coder?  (if you don’t know the answer, then you are not what I consider a good coder). Be honest, all too often an individual gets employed because they are good at embellishing the answers to the interview questions, and one or more of the Interviewers gets a good ‘gut feeling’ about the individual. (for more information watch Simon Sinek’s You Tube Video). Plus, lets face it, what proof do I have that YOU wrote your own Job Application?

Over the years in which I have seen these sorts of Fizz Buzz requests, I have tried several different ways to write them – just for fun.  Personally, I find that Fizz Buzz holds absolutely no challenge other than the ‘Fun Factor’.  I have seen several ways to create them.  Ranging from almost One liners (I am sure this could be done with Power Shell), to intricate class files (all hail the wonders of abstraction and inheritance).  Although, I am yet to see it in Binary 😉

I thought I would post just ONE (non nobis solum) solution, which I have not seen anyone else post using VBA (Visual Basic for Applications).

OK. Let’s define the Fizz Buzz problem:
Loop through a set of numbers (N), say up to 100 starting at 1 (n).
If n is divisible by 3, print Fizz.
If n is divisible by 5, print Buzz.
If n is divisible by 15, print FizzBuzz Woof. Could someone please use a bit of creativity?
If n is not divisible by 3, 5 or 15, print the number (n).

Here is my VBA solution.
(NB: I most often use VBA because next to 99% of the population can, if they so desire, load it up and try it out)


Public Sub FizzBuzzWoof()

  'I am using a single Data Type (4 bytes of Nominal Storage Allocation)
  ' I chose to do this as  I want to store at least one decimal place.
  Dim iThree As Single
  Dim iFive As Single 

  'I decided to dim as a byte, as the question is from 1 to 100 - not infinity.
     'However, if you want to go higher, use a different type.
  Dim i As Byte
  For i = 1 To 100 'or you could create a constant. 

    iThree = i Mod 3 'a number divisible by 3 will return 0.
    iFive = i Mod 5 'a number divisible by 5 will return 0

    '53% of outputs will be a numerical number.  How do I know this?
    'Because I sat down with a Spreadsheet and did the maths.
    'Here is a thought: if our output is a number deal with it first?!
    'While THIS code is only '4' lines long it's the principle of the matter.
    If iFive <> 0 And iThree <> 0 Then
      Debug.Print i

    'If you check for both Three AND Five at the same time, you are actually checking for 15.
    'While fifteen only occurs 6% of the time, it just simplifies the overall testing if we do this now.
    ElseIf iFive = 0 And iThree = 0 Then
      Debug.Print "Woof"

    'Three will occur 27% of the time. So, Ill place it above the Five test.
    ElseIf iThree = 0 Then
      Debug.Print "Fizz"

    'Five will occur 14% of the time.
    ElseIf iFive = 0 Then
      Debug.Print "Buzz"

    End If

  Next i

End Sub

The code I have provided isn’t rocket science,  it isn’t complicated. but it is somewhat thoughtful, it does work and it shows that I can think about how to solve this problem, hopefully in a way that others didn’t initially conceive of.

 

 

Canasta Database Tracking

27 March 2015


Canasta

We meet up with some friends on a monthly basis for a game of Canasta. Personally I really enjoy this monthly gathering, and we have been doing it for quite a number of years.  We use a set of colored pebbles to randomly select who will partner with who, and since the first meeting have kept a book (we are now into our second book) to record the ongoing results.

A suggestion arose that I create a Canasta Database, and so I started out to create a Canasta Database which would not only track all our games, but show a running tally, graph and basic reports pertaining to the games played.

The attached Canasta Database is not filled with our scores, but I do remember looking around when I first went to design this to see if anyone had created anything similar, and no.

The Main Screen

Canasta Main Screen

Canasta Main Screen

That’s right, it has buttons…. Several in fact.  I have made no attempt (as usual) to hide my code or to prevent you from having full access to the database.

Modify Player Details

The first thing you will need with the Canasta Database to do is ‘Modify Player Details’.    Delete the pretend people from here, and put in the real names of people.  The tick box is simply used later on to hide or show peoples name on the main screen.  I define a location as we move around to different peoples places, so that just allows us to state who’s place we played the game at for that date.  I also take pictures of peoples places from time to time, and I just reference the path and name of the image I took.    There is a ‘secret’ double click action on the image, which allows you to ‘delete this game’, useful for when you make a big mistake and just want to start again, or to ‘true up’ the entire database.

Canasta - Player Details

Canasta – Player Details

 

View Canasta Games

The ‘View Canasta Games’ takes you to the main page, where you fill in the details.  At the start of each game, I select the Game date, type in information about the Game Number (memo field really) and the the time we start the game.  When we finish the game, I enter the end time and it manually calculates a rough time (hours.minutes).

I normally Enter Team 1: Player 1 being the person who deals first. Add their partner into P4.  Then P2, P5, P3 and finally P6.  The AI does the rest to figure out who the dealer is going to be what the round is and which team you are in.  It also automatically calculates the score and what your starting hand needs to be for that round (50, 90, 120).   The difficult part is probably that box to the right.  The Refresh button will refresh the entire page, including that box, and all you need to fill in is the associated score.  In the example we have completed two rounds.  (there is more to this page than just the screen dump below).

Canasta - Game Details

Canasta – Game Details

The Graph.  Everyone Loves Graphs
(so long as it isn’t a pie chart)

As the Canasta game is played, a graph is created to show everyone how well (or how bad) they are going. Frankly it’s the highlight for those sitting at the other end of the table!

Canasta - Graph

Canasta – Graph

Oh, so a Pie chart you say?  Here you go.  Pretty useless if you ask me. The only thing a pie chart is good for is, well, nothing. Unless you are into Pac-Man 🙂

Canasta - Pie Chart

Canasta – Pie Chart

Lies, Damn Lies and Stats

What is the point of a database if you can’t create some basic stats?   I also like to show how long it takes to present this page.. its a thing I do, recording how long it takes to perform action x – because then I know if action x takes too long, my code probably needs work!

Canasta - Show Stats

Canasta – Show Stats

Team Reports – More Stats

Finally, the part I like the most (and those of you out there with DB skills, feel free to improve this!).

For each Canasta Round, it shows you who is most likely to win that round based on their number of wins.  I also show the minimum number of rounds ever played (5 rounds) , the average rounds (8) and the maximum rounds we have played (14).  Then, remember how I said we ‘randomly’ team up by picking rocks?  Well, one would think it was indeed a random event, but the stats show it’s not as random as you would expect.  My friend Dorothy and I have teamed up 20 games, yet poor Penelope and I have only teamed up 5 times.  Go figure!

Finally, the number of Canasta wins, this also gives away number of times played and gives you an idea of whom you should team up with if you want to win.  Or perhaps it shows who cheats more than others ? 😉

Canasta - Team Reports

Canasta – Team Reports

It took HOW long to run?

Finally, for those of you interested in the Canasta stats, (the version you download at the moment is Version 1.32), below are some stats from earlier versions.  I just reset the counters on each version upgrade.  After all the version upgrades should have a faster AI and less errors.

Canasta - Run Times

Canasta – Run Times

Provisos, Quid Pro Quos, Disclaimers:

Yup, it’s that time of the day again. Sometimes I get a Code error when I try to close the Team Reports (on Me.gphCanastaRounds).  Perhaps in the future Ill bother with why and how to stop it.  It doesn’t annoy me as I just stop it and close out of the form using the X (top right hand corner).

Statistics, Bell Curve Graphs and Standard Deviations

26 March 2015

Statistics

I work for an organisation in a job where understanding statistics is definitely required. I wont bore  you in this post about the applications I use, as this is about a singular product that I have created using Microsoft Excel.  If you are interested in my Bell Curve Excel Template, feel free to download it. Don’t use a low powered CPU to run this, as it does require a little grunt.  Now, like most things I create, its a jigsaw of features from here and there.  Feel free to open it and take a look prior to actually pressing any buttons or following my example further down.

BTW I am a BIG fan of using XLSB file types for saving Large Excel Documents.

Add ’em all up, Divide by the Count = Average

When I first started in this job, I was asked to create an ‘average’ number from a population of 1300 records.  The expected average was to be somewhere around 80, but what I returned was approx 60% higher at 124.  Why?

Lets face it, most people don’t care about anything beyond one piece of data – that average number.  But when the numbers don’t add up, they can just assume you don’t know what you are doing. Thankfully I understood Standard Deviations and was able to create a Bell Curve Graph to explain why.  Although the graph took 3 hours to create and the explanation took 15 minutes.  Way too long for my liking.  And instead of you wasting time, take a copy of my Bell Curve Excel Template and use it for yourself.

Standard Deviation

There are LOTS of good YouTube – I recommend ExcellsFun on YouTube, this guy knows his stuff and has some great formula ideas for Spreadsheets.  In fact, if you look at some of my formulas, I think I used his ideas to ensure the chart would not show ‘null’ values.

I also acknowledge that a lot of my inspiration came from
Wittwer, J.W., “Graphing a Normal Distribution in Excel”.

While there are similarities tot he above references, hopefully you will conclude that I have made this a much more user friendly version.

Percent of the Population

Here it gets complicated.  I want to try to keep the blog simple – so the complexity is in the Bell Curve Excel Template.  The beauty of the spreadsheet, is I can simply highlight the area of interest to individuals (which to be honest, normally isn’t even the graph as that is way too complicated for them too).

Bell Curve Graph

Bell Curve Graph

The part of interest to most people is the 68% or the 95% section.  My humble understanding is that I can throw away the 0.3% result AND here is where it gets really cool (I’m just a geek at heart) I can also ignore the 99.7% if I so desire.   Suddenly, my average numbers are on target and explainable!

Bell Curve Template Spreadsheet

I wont go through the mechanics (VBA) of the Bell Curve Excel Template. I wont lock the code so feel free to take a look at it (Alt + F11).  I believe I limit the record set to 20,000 (a constant set in the module) records, but if you have the grunt power, or the requirement or nerves of steel, feel free to up the anti.  I had it at 100,000 at one point and… overkill!

Disclaimer #1: Two Spread Sheets.  IF you rename the sheets, the code wont work.   You can copy a sheet and rename the copied sheet if you so desire.  I do this to compare the graph outputs from time to time.

Disclaimer #2: If you are smart enough to understand the VBA and to make positive changes to it, feel free to show me what you did or how you improved it.  If your a programmer and only want to banter about how crap VBA is or that I should have used an OO language instead.. Then go away.  f

Sheet One: USER INPUT 

Option 1 –  Delete the old data and paste your data.

Option 2 –  In the Average Number cell, put a number.  Say 60?  In the Std Deviation cell, put the standard deviation you’d like to see.  Say 5?  Then how many records would you like to compare.  More is best.  Don’t say 5. don’t say 10.. make it a number above 400 preferably in the thousands. Then just click ‘Create Test Data Set’ and wait for a moment.  The data set will now have changed.

Option 3: Click the Create Totally Random Numbers button. Just so you know. Its not totally random. Like all buttons, I made it to help ME test an idea.

THEN: Click ‘Load Graph’.  And wait.  Just wait for it.  it will flicker. that’s good! Patience. It will show two pages at once..  No issues.. Pax Gentlemen, Pax. Then suddenly, you will have landed on the NEW sheet.

Sheet One: NEW

This is a large sheet that contains many wonderful things.  Column A (labeled x) is your list of numbers. Column B (labeled x (n Sorted)) is your list of numbers sorted lowest to highest.

To understand Columns C, D and E, seriously, do some research.  My code will automatically fill in these cells if they are needed.

Columns G and H took a bit of work… but it allows you to move a slider under the graph (not my idea and I forgot where I saw this at work, but I did modify it for the way I needed it in my spreadsheet).

Cell J2: Dont edit this.  its the Average Number. ‘yay!’
Cell J3: Dont edit this cell. It is The Standard Deviation.

Cell N2 and N3: you may alter this, but keep it like in the following formats:
(-1, 1) or (-2,2) or (-3,3) or (-4,4) .. or.. (-12, 12).    If you don’t see the pattern, then I highly suggest you do NOT use the spreadsheet 😉

Cells J8 Through to J17.  (thats J8, J9, J10….. J16, J17) : Don’t change these either.
Ah, yes… Cell J12 is indeed a Magic Number.  It took me way too long to figure out how to calculate this number (it ended up being the MAX number in my data set.

If you want to reload the Chart after making a change, just press ‘Reload Chart’. Easy Huh?

Then the true magic.  The results!  A graph that automatically adjusts to your numbers.  you needed one decimal place for the Standard Deviation?  It (should) give you one decimal.. You need two decimal places. It (should) give you two decimals.  I think its up to three decimal places max….

And finally, those words.  Displaying the four optional Standard Deviation % rule.  I forget what this rule is.

Provisos, Quid Pro Quos, Disclaimers:

If there are errors, let me know.

And in Conclusion

Should you create a random set of numbers from the ‘User Input’ Sheet, then you should see a near perfect Bell Curve Graph.

Should you use your own set of numbers from a different subset, it MIGHT be a perfect Chart, or like mine, it might be skewed.

But therein lies the fun.

And no longer do I need to spend 3 hours to create a Bell Curve Graph.

Dovetail Calculator

25 March 2015

Dovetail Calculator

All woodworkers have been here: the desire to create a nice, clean dovetail join but the mathematics to create a dovetail seem to be elusive… When I searched the internet for a clear, succinct description of how to calculate these, I just couldn’t find them. Sure there are plenty of sites that will magically create a diagram for you, but I wanted more. (And yes, you can use a protractor if you know what you are doing).

I created an excel Dovetail Calculator  – Feel free to take a copy and do what you like with it.

What is a Dovetail?

For the non-woodworkers out there, a search of Google University will show up plenty of pictures.  Here are a few pics I located:

Dovetail - Used to join two pieces of wood without nails.

Dovetail – Used to join two pieces of wood without nails.

Source: http://www.oakfurnitureland.co.uk/page/dovetail-joints.html

What does it do?

I am glad you asked.  I don’t know.  No wait. Yes I do!  (I wrote it)  Firstly, the form itself allows you to select the Width and Height of the wood you are going to be working with.  You then select the number of ‘tails’ (woodworkers understand these terminologies).  The number of ‘pins’ will always be one more than the number of tails.  You can then select if you want to define the tail width OR the pin Width.  I also allow you to select Hardwood, Softwood or your own Angle (as a ratio).   Go crazy.

Do you see those /// to the bottom right hand corner of the screen?  If you double click that it will show you the formulas I used to obtain the results.  Note that the formula process is slightly different depending on if you selected the ‘Tail Width’ or the ‘Pin Width’.

Dovetail Form

Dovetail Form

Finally, press ‘Calculate’ and the diagram on the Spreadsheet will redraw itself with the appropriate sizes / dimensions for you to see.  IF you don’t like what you see, try a different width / set of pins / angles, etc.

Dovetail Calculator Main Form

Dovetail Calculator Main Form

If you close the form and delete all the shapes on the page, my code will just redraw the entire thing.  This has got to be almost fool proof?

Provisos, Quid Pro Quos, Disclaimers:

Yup. If it doesn’t work, try something else.

Want to see something amazing?!

This is Rob Cosman and from what I have seen he is a wizz at making Dovetails (and I bet other things too).  He also has an amazing web page too which I suggest you head over to and take a look at.

© 2017 Andrew's Blog

Theme by Anders NorenUp ↑

css.php