Andrew Que Sites list Photos
Projects Contact
   With the temperature at 81°F (27°C) this afternoon and almost no wind I decided to bike out to the wind turbines.  Along the way I saw a little town in this distance.  I determined that on this trip it there wasn't an easy way to reach it, but it will be my goal in the future.


   This is a graph of the highest temperature for every day from January 1st, 1948 through May 26, 2012.  It took me awhile to find this data and I ended up having to download a 3 GB compressed file with historical weather data from every city in the United States.  There was a mostly unusable front-end that was suppose to allow someone to get smaller data sets, but it had clearly been written by comity and sub-contracted to the most inept bidder.  So raw data it was.
   Why did I want all this data?  I want to be able to ask "how normal is the temperature today?"  It is easy to get the average temperature for any given day, to ask how normal it is you need to know how much the temperature varies from that average--you need the standard deviation.  Since it was unlikely I could find that number somewhere, I needed the raw data so I could calculate it.  Standard deviation tells you how much of the data varies from the average.  For example, April 10th this year seemed pretty chilly for April.  The average temperature for this day is 55°F (13°C), and this year the high was 48°F (8.9°C).  So we know that this year was 7°F cooler than average.  But that doesn't tell us how often the temperature is this much cooler.  The standard deviation for April 10th is 11.58°F.  What this means is that 68% of time the temperature will be within a single standard deviation of the average, or between 43° and 67°.  95% of the time it will be between two standard deviations, or 32°F to 78°F. 
   An other thing I learned about at the Great War reenactment was the existence of the group known as the International Red Star Alliance, or just the Red Star.  This group had a function similar to that of the Red Cross, but for helping animals.  Horses played a huge role in WWI, and large numbers were injured on the battlefield.  I learned a bit about the people who helped treat these animals.

April 10, 2016

Extract METAR data in spreadsheet

Behind Enemy Lines

Behind Enemy Lines

   I keep a spreadsheet of all my bike rides, skating sessions, and other forms of physical exercise.  I decided to add a column for the METAR to report weather information for when I was doing this event.  I found a website I could pick up historical METAR data to back-fill my spreadsheet.  Although the METAR data is meant for pilots, it contains several pieces of useful weather data.  For a cyclist the meaningful data includes temperature, and wind speed and direction.  One aspect I don't like about the METAR data is that wind speeds are in nautical mile (knots).  I wanted to be able to extract the useful data from the METAR data line, and wondered if I couldn't manage to do it with the spreadsheet.  Well, turns out you can.  And I set out on creating the longest spreadsheet formula I've ever written.
IF( R228<>"", CONCATENATE( ROUND(VALUE(SUBSTITUTE(LEFT(MID(R228,SEARCH(" [M0-9]+/[M0-9]+ ",R228)+1,LEN(R228)),SEARCH("/",MID(R228,SEARCH(" [M0-9]+/[M0-9]+ ",R228)+1,LEN(R228)))-1),"M","-"))*1.8+32), "°F ", VLOOKUP(VALUE(MID(LEFT(MID(R228,SEARCH(" [0-9G]+KT ",R228)+1,LEN(R228)),SEARCH("KT",MID(R228,SEARCH(" [0-9G]+KT ",R228)+1,LEN(R228)))-1),1,3)),WindDirections.$A$1:$B$17,2), " at ", ROUND(VALUE(MID(LEFT(MID(R228,SEARCH(" [0-9G]+KT ",R228)+1,LEN(R228)),SEARCH("KT",MID(R228,SEARCH(" [0-9G]+KT ",R228)+1,LEN(R228)))-1),4,2))*1.15078), "MPH", IF(COUNTIF(R228,".*[0-9]+G[0-9]+KT.*"),CONCATENATE( " G", ROUND(VALUE(MID(LEFT(MID(R228,SEARCH(" [0-9G]+KT ",R228)+1,LEN(R228)),SEARCH("KT",MID(R228,SEARCH(" [0-9G]+KT ",R228)+1,LEN(R228)))-1),7,2))*1.15078)),"") ), "" )
Assuming the METAR data is in cell R228, and there is a sheet called "WindDirections" that has a look-up table for wind directions, this formula will extract the create a line that has the temperature (in F), wind direction, and wind speed (in MPH).  For example:
KC29 031955Z AUTO 23017G28KT 10SM SCT110 22/02 A2950 RMK AO1 T02150018
Will produce the output:
72°F SW at 20MPH G32
   This makes use of LibreOffice's regular expression engine in the SEARCH function.  It can get the location of where a matched pattern starts.  From there I use the MID function to extract some amount of text.  This usually requires two SEARCH functions.  The first will find the beginning of the text I'm looking for, and the second will find the end.  Then it is a matter of converting it into units and concatenating all the strings together.