Saturday, March 29, 2014

Getting Your Google Drive Spreadsheet To Send Daily Emails

Another quick post today.  I've been playing around with Google Drive spreadsheets and I like what I see.  The scripts are the thing that interests me the most.  Sometimes it's easier to handle data in the javascript like environment than it is to use spreadsheet functions.

One of the cool little tricks I came across while playing, is getting your spreadsheet to email you each day with data from it.  This could come in handy if you have a group of people collaboratively working on a sheet and you want to get a daily report of some metric from it.  The way to do this is to add a small little script that is run everyday.  This is done by setting a timed trigger.  In my case the notifier function is run everyday and only sends an email if it's a weekday.

function notifier() {
  var currentDate = new Date();
  var dayOfWeek = currentDate.getDay();

  // is it a weekday?
  if ( (dayOfWeek >=1)&&(dayOfWeek <=5) ){
    sendEmails(currentDate);
  }
}

function sendEmails(currentDate) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getRange(5, 8, 1, 1);
  var daily = Number(dataRange.getValues()).toFixed(2);

  dataRange = sheet.getRange(2, 10, 1, 1);
  var total = Number(dataRange.getValues()).toFixed(0);
 
  var emailAddress = "email@email.com";
  var subject = ("Report for " + currentDate.toLocaleDateString());
  var message = "Daily " + daily +" \nTotal " + total;
  MailApp.sendEmail(emailAddress, subject, message);
}

Setting a timed trigger is simple as well.  Just have a look at the triggers under the resources menu.

Drop Down Boxes
Configuring a timed trigger

I think this is pretty nifty.  I'm starting to like all this cloud stuff.  You could also use a form to get data from people and then send emails to addresses they enter.  For example, if people were to RSVP to a party via a form leaving their email address, you could then respond to the people coming with any further updates.

Tuesday, March 18, 2014

Probability of Collecting Multiple Full Sets

Well, it's that time again.  One of the large supermarket chains has released yet another set of collector cards.  This time there are 42 cards to collect compared to the 108 cards of the last campaign, and I thought it'd be interesting to explore some the statistics of cards collecting.  In my last post on this topic, Probability of Collecting a Full Set, I looked at the expected number of cards you need to collect on average before you have a full set of cards.  I suggest reading that article for some background on the problem before preceding any further.

All caught up?  Good.  The question I'm posing this time is how many cards you need to collect on average before you have multiple complete sets?  The inspiration for this question came from hearing about families with multiple children trying to collect a set of cards for each child.  Of course this once again assumes that the trading of cards to other people is disallowed.

Like my first article about this, I initially thought that I had a handle on the problem and the answer would be easy, and once again the maths kicked my butt.  I scraped through by the skin of my teeth last time, but not this time.  I originally thought that the probability of collecting two sets of cards could be found by finding the probability of collecting one set of cards, multiplied by the probability of collecting further sets from the left over cards.  It seems logical, but after running a few quick simulations it was obvious I was wrong.  The problem lies in the fact the the left over cards are not an even distribution.

So, where to now?  Google.  It took some time to find but I eventually found that the problem was called the "double dixie cup problem" and was only solved  by Newman and Shepp in 1960.  I found a good run-down of the problem at http://www.brynmawr.edu/math/people/anmyers/PAPERS/SIGEST_Coupons.pdf that gets me close to solving it analytically, but I just didn't have the time to fully absorb it.  So I listened to that little engineer buried deep inside and decided to cheat by using a Monte Carlo simulation.  Near enough is good enough.  I suppose it's not technically cheating but it does feel a little dirty.

The files associated with this post are located here.  They're a bit rough, but they were only used for working.

Octave was good enough for the job.  It was simple to write a script that repeatedly drew cards and counted how many full sets were drawn.  My simulation was for randomly drawing 1 to 1000 cards and seeing how many full sets of 42 cards were collected.  Each of these simulations was run 10000 times.  The results are shown below.
cumulative distribution function
The Probability of collecting at least n full sets of 42 cards

The familiar shape from the previous article can be seen here.  The noise from the Monte Carlo simulation can also be seen on the graphs.  The results match what I expect.  For example if you were to collect 200 cards, it would be highly unlikely you would have 3 sets (0%), there's a slim chance you'd have 2 sets (10%), but you'd almost certainly have one complete set (90%).  But what is the expected value for a certain number of sets?   This is where things get a little dodgy.  Because of the noise in the simulation it's possible to have negative probabilities in the probability mass function.  (yes I know that it should be a stem plot, but I find it easier to see what's going on this way)  The main point is that a negative probability doesn't make much sense.  The plots below should be a series of smooth humps.  If the Monte Carlo simulation was run for 10 times longer we would get close to approximating a smooth plot.  Even thought the data doesn't make sense it should still be able to be used to approximate the expected value.
The expected number of cards needed to collect 1,2,3,4,5 sets was calculated.

1 set  - 182 cards - 182 cards per set
2 sets - 264 cards - 132 cards per set
3 sets - 337 cards - 112 cards per set
4 sets - 404 cards - 101 cards per set
5 sets - 468 cards -  93 cards per set

As the number of sets to collect increases, I suspect that the number of cards per set would approach a limit of 42 cards per set.

As a sanity check we can analytically calculate the expected number of cards to collect for one set using the equation from the previous post. nH(n), where H represents the harmonic number of n.  42 H(42) = 181.7 this agrees with the result from the Monte Carlo simulation.

So what's the point of all this?  Although it may seem a daunting task to collect a set of cards (you need to collect 182 on average) when you pool resources with other people the number of cards you need to collect decreases.  This can be accomplished within a family or by gathering a group of people to trade with.

Friday, March 7, 2014

DIY Relay Brackets

I finally got back to a long term project I've been working on.  I'm building a small control box containing relays that needs to be waterproof.  The layout and how to mount everything is sorted except for the relays.  I'm using automotive relays that have a metal tab coming out the back with a single hole.  This makes them easy to mount but if any force is put on the cabling the relays could rotate and work loose.  I'm using nylock nuts so they wont come loose and the spade terminals on the relays will have insulated boots so things can't short out.  Having said that though I want this to be reliable, work for as long as possible, and require little maintenance.  So adding some support for the realys seems like a good idea.

Relay Bracket
Relay Brackets
My solution was to pop rivet two small aluminium pieces to either side of the relays to hold them in place so the can't rotate.  I put my OCD tendencies to the side on this one and used some of the holes that were already in the plate, so unfortunately everything isn't symmetrical, but it'll still work.

Relay Bracket
Relay Brackets
I'll keep you posted with the progress of the project but at the rate I'm going I might get it done by Christmas.  I've been having trouble finding time to do anything lately.