Showing posts with label coding. Show all posts
Showing posts with label coding. Show all posts

Wednesday, May 20, 2020

Old Phone/Tablet as an Info Board: Update 3 - The elusive sunrises and sunsets

While I was messing up with the infoboard's bus widget, I also decided to touch up on the weather forecast (see the detailed description in the earlier post) and and add a few more visual cues to it.

I wanted to distinguish day and night, so that it becomes immediately clear how the weather behaves at sunset and during the morning commute, kind of like so:



My first attempt was very simple: I added a function eachTime() that did this:

function eachTime(){
 var str = $(this).text();
 var hhmm = str.split(":");
 var sunrise = 6; var sunset = 22;
 if (hhmm[0]>=sunset || hhmm[0] < sunrise) {
  $(this).css("background","rgb(0,0,0)");
  $(this).css("color","rgb(255,255,255)");
}}

along with a single line in forecast():
$("#rowTime").find("td").each(eachTime);

However, this seemed a bit artificial to declare night as "anything from 22:00 till 6:00", so I wanted actual sunrise/sunset times to be calculated. Although, with my degree of precision of 1 hour, this seemed like a relatively simple mathematical task, there was no need to reinvent the wheel since PHP already has date_sunrise() and date_sunset() functions. So I geared up a new PHP script as follows:
<?php
$remote_dtz = new DateTimeZone('US/Eastern');
$remote_dt = new DateTime("now", $remote_dtz);
$offset = ($remote_dtz->getOffset($remote_dt))/3600;
$lat=43.6;$lon=-79.6;
echo '<html><head></head><body>';
echo '<span id="sunrise">';
echo(ceil(date_sunrise(time(),SUNFUNCS_RET_DOUBLE,$lat,$lon,90,$offset)));
echo '</span>';
echo(':'); echo '<span id="sunset">';
echo(ceil(date_sunset(time(),SUNFUNCS_RET_DOUBLE,$lat,$lon,90,$offset)));
echo '</span>';echo '</body></html>';
?> 

The only trick here is to get the correct GMT offset; I am also rounding up the result to the nearest hour. It is then very easy to load this script in another hidden iframe like so:
<iframe id="astronomy" style="visibility: hidden; height: 0px !important;" src="astro.php"> </iframe>

and amend eachTime() as follows:

function eachTime(){
 var str = $(this).text();
 var hhmm = str.split(":");
 var dom=$("iframe#astronomy").contents();
  var sunrise = 6; var sunset = 22; //fallback
  sunrise = parseInt(dom.find("#sunrise").text());
  sunset = parseInt(dom.find("#sunset").text());
 var commute = 8;
 if (hhmm[0]>=sunset || hhmm[0] < sunrise) {
  $(this).css("background","rgb(0,0,0)");
  $(this).css("color","rgb(255,255,255)");
  if (hhmm[0]==commute){ $(this).css("color","rgb(225,255,0)");$(this).css("border","1px solid yellow");}
 }
 else{  if (hhmm[0]==commute) {$(this).css("border","1px solid black"); $(this).css("background","rgb(255, 213, 171)");}}  
}


There is no need to bother about refreshing astro.php because the entire system refreshes at 2 am every day anyway, and even if we happen to be 1 day off we are well within the desired accuracy margin. In addition I have added a cue mark for the morning commute, which is (unfortunately) independent of where the Sun happens to be. (And your mileage may vary here too, distinguishing weekdays from weekends and even accounting for statutory holidays if need be.)


BONUS: As an addition, notably after a strong wind storm in our area, I wanted to colour code wind as well, along with rain and temperature. Here's what I changed about eachWind():

function windgradient(base,gust){ 
var b=(base>=80)?1.0:(base/80.0);
var g=((gust-base)>=25)?1.0:((gust-base)/25.0);
if (g<0) g=0;
b=Math.pow(b,0.75);g=Math.pow(g,1.0);
    return "rgb(" 
  + Math.round(255.0) + "," 
  + Math.round(255.0*(1.0-b*g)) + "," 
  + Math.round(255.0*(1.0-b)) + ")";
}

function eachWind(){
 var rawstr = str = $(this).text().trim(); 
 var str = rawstr.split(String.fromCharCode(160));
 var result = "rgb(255,255,255)"; var speed = 0;
 try { speed = parseInt(str[1]);} catch(ignore){speed=0;}
 var gust=speed;
 if(rawstr.indexOf("gust")!=-1) {try { gust = parseInt(str[2]);} catch(ignore){gust=speed;}}
 result=windgradient(Math.round((speed+gust)/2.0),gust);
 $(this).css("background",result);
 $(this).text(rawstr.replace("gust",">"));
}


So the colour intensity and hue can independently give some information about how strong and how gusty the wind is, more or less like so (yes I did it on Wolfram Cloud for a quick illustration):



Friday, May 15, 2020

Old Phone/Tablet as an Info Board: Update 2 - The snappy client-side bus board updates

Last time I promised to touch upon what I did on the client side of the bus widget to make it look like this:

In simple terms, I needed to solve the following problem: given the timetable loaded X minutes ago, find the number of minutes remaining until the bus departures now, and based on that, highlight the buses that are convenient to catch (i.e. which depart roughly when I reach the bus stop if I leave soon).

We remember that the timetable, as loaded, returns its output nicely compartmentalized into distinct <span>'s:


so the idea is to do the following at regular intervals:

  • query all #bustime's for departure times;
  • determine the time difference between those times and the current time
  • update the number of minutes in #busreal's
  • re-colour all #bus* according to the number of minutes left, taking into account the time it takes to walk to the bus stop.
The ideal point of insertion from the point of view of the flow control would be the clock() function, which is scheduled to run every second to update the clock; every 10 seconds I ask it to do the following: 

function clock(){
var now = new Date(); var h=now.getHours(); var m=now.getMinutes(); var s = now.getSeconds();
$("#nowclock").text(((h>=10)?h:("0"+h)) + ":" + ((m>=10)?m:("0"+m)) + ":" + ((s>=10)?s:("0"+s)));
if (s % 10 ==0 ) {
var mintogo = 6; // walking distance to stop in minutes
var togo = -1; var nextgo = -1; var num=0; var fnum=-1;
var dom = $("iframe#miway").contents();
while(num<6) {
 var token = "#bustime"+num;
 var nextbus = dom.find(token).text().trim();
 nextbus.replace("now","0 min");
 var am = (nextbus.indexOf('am')>0); var pm = (nextbus.indexOf('pm')>0)
 var idx = nextbus.indexOf(' ');
 if (idx>0) nextbus = nextbus.substr(0,idx);
 var ddot = nextbus.indexOf(":"); if (ddot<0) return;
 var hh = parseInt(nextbus.substr(0,ddot)); var mm = parseInt(nextbus.substr(1+ddot,nextbus.length-ddot));
 if (am && hh==12) hh=0; if (pm && hh<12) hh+=12;
 var hcarry=(h<20 && hh>20); var hhcarry=(h>20 && hh<20);
 if (hcarry) h+=24; if (hhcarry) hh+=24; 
 var diffmin = (mm+hh*60) - (m+1+h*60);
 togo = diffmin - mintogo;
 var thiscol = buscolor(togo);
 var token = "#busnum"+num; if (num==0) token="#bustoken";
 dom.find(token).css("background", thiscol);
 var mintxt = ':'+diffmin+'m';
 if (diffmin<0) mintxt = ':-';
 if (diffmin==0) mintxt = ':now';
 if (num==0) {
   mintxt = '('+diffmin+' min)';
   if (diffmin<0) mintxt = '(--)';
   if (diffmin==0) mintxt = '(now)';
   }
 dom.find("#bustime"+num).css("color", thiscol);
 dom.find("#bustime"+num).text('\xa0'+hh+":"+((mm<10)?"0":"")+mm+'\xa0');
 dom.find("#busreal"+num).css("color", thiscol);
 dom.find("#busreal"+num).text(mintxt);
 if (togo>=0 && nextgo<0) { nextgo=diffmin; fnum=num+1; }
 num++;
 }
dom.find("#realtime").text("leave in " + (nextgo-mintogo) + " min");
}} 


where the auxiliary function buscolor() is simply

function buscolor(togo){
 if (togo<0) return 'rgb(200,180,180)';
 if (togo<=2) return 'rgb(255,200,0)';
 if (togo<=4) return 'rgb(255,0,0)';
 if (togo<=8) return 'rgb(255,64,64)';
 if (togo<=15) return 'rgb(255,100,100)';
 if (togo<60) return 'rgb(255,128,128)';
 if (togo<120) return 'rgb(255,128,255)';
 return 'rgb(255,0,255)' // error
}

That's it. Note the following:

  • The code handles both 12-hour and 24-hour times but the output is forced into 24-hour because it offers smaller footprint.
  • The item number 0 has a different, more verbose output format; the additional #realtime contains a hint when to leave home for the "next suitable" bus.
  • The container that should be called #busnum0 is called #bustoken instead. This is for historical reasons: the system looks for #bustoken as an indicator that the timetable has loaded.

It remains to be seen whether doing this every 10 seconds will prove feasible for the Playbook in terms of "mean time between refreshes / restarts". 

P.S. Turns out I had planned this feature all along, according to my own original post:


Our mileage may vary even further. For example:

  • we may imagine the code to throw out "missed" departures, moving the remaining ones up the queue, and even triggering an extra timetable refresh when there are too few (say <3) left;
  • we may intelligently trigger an extra refresh every time we are nearing the "leave home in 2 minutes situation in order to make sure that the upcoming bus is still on schedule;
  • we may keep track of how much the real-time departure information is changing between refreshes (provided they happen often enough) and detect when delays start to appear or frequently change (this is indicative of unstable traffic, warranting more frequent refreshes)...
...but we aren't building an after-market professional grade departure board here, and "perfect" is a very common enemy of the "good enough". 

Friday, December 13, 2019

Heartbeat/watchdog service in MS Excel

We have already covered automating Excel tasks at work -- and indeed it can be a life saver (or rather, work-life balance saver) not to have to be present at work every single day at time XX:XX no matter what only to have to push a few buttons.

However, there is an inconvenience that no automation is 100% fool-proof, especially in a corporate environment. The automation computer may be restarted (by your colleagues or your IT department who decided to push some security updates). It may lose power (because the cleaning personnel got a bit too vigorous with their brooms). It may crash, or freeze, or have a hardware failure. It is particularly nasty is the automation computer is a shared terminal that only gets used once in a while, so a user log-off due to restart may not be immediately detected. And of course once the user is logged off, no code nothing written by that user can be invoked any more, and needless to say that admin access to all computers in the corporate is strictly verboten.

One way of getting around the inconvenience would be to implement a "heartbeat" or "watchdog" service that would periodically query the availability of your automation machine and "phone home" if it goes offline.

The general idea is as follows:
  • The "server" is an Excel workbook that does nothing and resides in the user's private folder on the shared drive (most corporate environments have that), so that it's accessible from any computer in the office but only by the particular user. This worksheet contains no code, but is configured to open at startup on the automation machine.
  • The "client" is another Excel workbook that runs on the user's regular desktop (which is assumed to be operational). 
  • The client polls the server by trying to open the server workbook (assuming that if it's locked for editing, it's open on the automation machine so the latter is operational). 
  • Should the server workbook become unlocked, this means that the automation machine has logged the user off and automation won't run. The client then informs the user by sending an email. 
To determine whether a file is open on the automation machine, I use the following macro (adapted from this idea):


Function IsFileOpen(filename As String)
    Dim filenum As Integer, errnum As Integer
    On Error Resume Next   ' Turn error checking off.
    filenum = FreeFile()   ' Get a free file number.
    ' Attempt to open the file and lock it.
    Open filename For Input Lock Read As #filenum
    Close filenum          ' Close the file.
    errnum = Err           ' Save the error number that occurred.
    On Error GoTo 0        ' Turn error checking back on.
    ' Check to see which error occurred. Open for 
    IsFileOpen = (errnum = 70)
End Function

The periodic polling code may look something like this:



Sub Heartbeat()
 Dim monitor As String
 Dim span, tick, fail, restart As Integer
 With ThisWorkbook.Worksheets("Control")
  .Calculate
  monitor = .Range("monitor").Value
  span = .Range("span").Value: fail = .Range("numfail").Value: restart = .Range("autoreset").Value
  If IsFileOpen(monitor) Then
    .Range("tick").Value = 0
    .Range("retick").Value = 0
    Call LogMessage("INFO", "Heartbeat server detected")
  Else
    .Range("tick").Value = .Range("tick").Value + 1
    Call LogMessage("WARN", "Heartbeat server NOT detected")
  End If
  If .Range("tick").Value < fail Then
     Application.OnTime Now + span / 86400, "Heartbeat"
  Else
     Call LogMessage("FAIL", "Heartbeat server has failed, REPORTING FAILURE")
     Call SendEmail(.Range("ReportMail"))
     Application.OnTime Now + restart / 86400, "RestartHeartbeat"
  End If
 End With
End Sub

The idea is that the macro will restart itself using Application.OnTime if all goes well, until the server is not detected several times in a row (to guard against false positives due to intermittent network failures). In that case, a report is set via Outlook (adapted from here and possibly here) using this function:


Sub SendEmail(params As Range)
    Dim OutApp As Object, OutMail As Object, strbody As String, toaddr As String, ccaddr As String, subj As String
    strbody = "": toaddr = "": ccaddr = "": subj = "":
    Dim here As Range
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    Set here = params(1, 1)
    While Len(here.Value) > 0
       If UCase(Left(Trim(here.Value), 2)) = "TO" Then toaddr = here.Offset(0, 1).Value
       If UCase(Left(Trim(here.Value), 2)) = "CC" Then ccaddr = here.Offset(0, 1).Value
       If UCase(Left(Trim(here.Value), 2)) = "SU" Then subj = here.Offset(0, 1).Value
       If UCase(Left(Trim(here.Value), 2)) = "BO" Then strbody = here.Offset(0, 1).Value
       If UCase(Left(Trim(here.Value), 1)) = ":" Then strbody = strbody & vbNewLine & here.Offset(0, 1).Value
    Set here = here.Offset(1, 0)
    Wend
    On Error Resume Next
    With OutMail
        .to = toaddr
        .CC = ccaddr
        .BCC = ""
        .Subject = subj
        .Body = strbody
        .Send   'or use .Display
    End With
    On Error GoTo 0
    Set OutMail = Nothing: Set OutApp = Nothing
End Sub

Note that the parameters for the email are conveniently stored on the spreadsheet for easy editing, so that only the range pointing to the parameter block needs to be passed. The email can then be easily defined using something like this:


After the failure report is sent, a slightly different macro is invoked:


Sub ReStartHeartbeat()
 Dim monitor As String, span As Integer, autoreset As Integer
 With ThisWorkbook.Worksheets("Control")
  .Calculate
  monitor = .Range("monitor").Value
  span = .Range("span").Value
  autoreset = .Range("autoreset").Value
  If IsFileOpen(monitor) Then
    .Range("tick").Value = 0
    .Range("retick").Value = 0
    Call LogMessage("OK", "Heartbeat server restarted, monitoring resumed")
    Call SendEmail(.Range("ReSetupMail"))
    Application.OnTime Now + span / 86400, "Heartbeat"
  Else
    .Range("retick").Value = .Range("retick").Value + 1
    Call LogMessage("FAIL", "Heartbeat server NOT restarted, keeping on trying")
    Call SendEmail(.Range("ReSetupErrorMail"))
    Application.OnTime Now + autoreset / 86400, "ReStartHeartbeat"
  End If
 End With
End Sub

Here the idea is to keep reminding the user that the server is still down, and getting  back on track once the user has successfully corrected the problem.

The entire process is set into motion by two macros:


Sub StartHeartbeat()
 Dim monitor As String, span As Integer
 Call LogMessage("INFO", "Service started")
 With ThisWorkbook.Worksheets("Control")
  .Calculate
  monitor = .Range("monitor").Value
  span = .Range("span").Value
  If IsFileOpen(monitor) Then
    .Range("tick").Value = 0
    Call LogMessage("OK", "Heartbeat server detected, monitoring started")
    Call SendEmail(.Range("SetupMail"))
    Application.OnTime Now + span / 86400, "Heartbeat"
  Else
    Call LogMessage("WARN", "Service not started, heartbeat server not detected")
    Call SendEmail(.Range("SetupErrorMail"))
  End If
 End With
End Sub

Private Sub Workbook_Open() ' Put this in the Workbook code rather than Module1
 If ThisWorkbook.ReadOnly Then Exit Sub ' DO NOT execute if read only OR
 If Workbooks.Count > 1 Then
  Call LogMessage("WARN", "Started in non-clean session, entering setup mode. Service NOT starting. For production, run in a CLEAN session.")
  ThisWorkbook.Save
  Exit Sub ' ONLY execute in clean, dedicated session
 End If
 ThisWorkbook.Worksheets("Control").Calculate
 Application.OnTime Now + TimeValue("00:00:05"), "StartHeartbeat"
End Sub

The safeguards in place make sure that opening the client for debugging or viewing (or by accident) do not start spurious monitoring processes (.OnTime is nasty, once set it will persist untill that particular Excel session is ended, even after the workbook containing the .OnTime was closed). So the client only starts if the file is only opened in a clean, dedicated Excel session.

Finally, an auxiliary subroutine, purely aesthetic, is used to log the monitoring actions. Here goes:


Sub LogMessage(code As String, msg As String)
 Dim here As Range, c As Integer, level As Integer
 level = 255
 If UCase(Left(Trim(code), 3)) = "INF" Then level = 10
 If UCase(Left(Trim(code), 2)) = "OK" Then level = 5
 If UCase(Left(Trim(code), 3)) = "WAR" Then level = 2
 If UCase(Left(Trim(code), 3)) = "FAI" Then level = 1
 If UCase(Left(Trim(code), 3)) = "ERR" Then level = 0
 
 For c = 1 To 2
  Set here = ThisWorkbook.Worksheets(IIf(c = 1, "Log", "Errors")).Range("A1")
  If c = 2 And level >= 2 Then Exit For
  While Len(here.Value) > 0
   Set here = here.Offset(1, 0)
  Wend
  With ThisWorkbook.Worksheets("Control")
   .Calculate
   here.Value = IIf(Len(code) > 0, code, "___")
   here.Offset(0, 1).Value = Now
   here.Offset(0, 2).Value = .Range("monitor").Value
   here.Offset(0, 3).Value = .Range("span").Value
   here.Offset(0, 4).Value = .Range("numfail").Value
   here.Offset(0, 5).Value = .Range("tick").Value
   here.Offset(0, 6).Value = .Range("autoreset").Value
   here.Offset(0, 7).Value = .Range("retick").Value
   here.Offset(0, 8).Value = msg
   Set here = ThisWorkbook.Worksheets(IIf(c = 1, "Log", "Errors")).Range(here, here.Offset(0, 8))
   Select Case level
   Case 10
    here.Font.Color = RGB(200, 200, 200)
   Case 5
    here.Font.Color = RGB(0, 128, 0)
   Case 2
    here.Font.Color = RGB(255, 128, 0)
   Case 1
    here.Font.Color = RGB(128, 0, 0)
   Case 0
    here.Font.Color = RGB(255, 0, 0)
   Case Else
    here.Font.Color = RGB(255, 0, 255)
   End Select
  End With
  If c = 2 Then ThisWorkbook.Save ' only save on error
 Next c
End Sub

Friday, November 8, 2019

House hunting in the cloud

Let us continue the tradition of following a low-level language coding post with really high-level. This time, with a cloud to boot.

Anyone who was apartment and house hunting knows that it is time consuming and hard to go through individual listings. Extracting trends from a large number of them can be very time-consuming and involving a lot of manual input.

It would be really helpful to have a took that would extract the features you need from a listing feed, and visualize it in your preferred way, like so:




We can actually do this with a little bit of coding entirely in Wolfram Cloud. Let's get started with a sample Toronto MLS listing selection (the kind you will be receiving from a real estate agent), looking like so (sorry for a tall image).



So the task is (1) to web-scrape the listing web page for information, and (2) to visualize it in human digestible form.

Using this nice Wolfram Language scraping guide, we can get started by simply grabbing the table data in one line, like so:


url = "http://v3.torontomls.net/Live/Pages/Public/Link.aspx?Key=...&App=TREB";
structured = Import[url,"Data"];
mlstable = structured[[1,2]];
addresses = Transpose[mlstable][[2]];
rawprices = Transpose[mlstable][[4]];
prices = ToExpression[StringReplace[#,{"$"->"",","->""}]]&/@rawprices;


Now we have the list of street addresses and prices. To geo-locate the addresses, the easiest way is to complete each address with city and country info, ans then use Interpreter:


locations = Map[Interpreter["StreetAddress"][#<>", Mississauga ON Canada"]&, addresses];


We can then convert the list of prices into a list of colored pins where color is determined by the house price, using a slightly modified example from GeoMarker documentation:


pin[color_]:= Graphics[GraphicsGroup[{FaceForm[color],EdgeForm[Black],
   FilledCurve[{{Line[Join[{{0, 0}}, ({Cos[#1], 3 + Sin[#1]} &) /@
   Range[-((2 Pi)/20), Pi + (2 Pi)/20, Pi/20], {{0, 0}}]]}, {Line[(0.5 {Cos[#1], 6 + Sin[#1]} &) /@
   Range[0, 2 Pi, Pi/20]]}}]}]]
rcfun[sc_] :=Blend[{{0,Green},{0.5,RGBColor[0.75,0.75,0]},{1,Red}},sc]
pins = Map[pin[rcfun[(#-900000)/(1300000-900000)]]&,prices];


It only remains to convert the list of geographical coordinates and pins to GeoMarker and filter out failed address lookups (as well as missed lookups, defined as those more than say 10 miles away from the arbitrarily chosen city center), like so


markertable = MapThread[GeoMarker[#1,#2]&,{locations,pins}];
home = Interpreter["StreetAddress"]["Square One, Mississauga ON Canada"];
goodmarkers = Select[markertable,(!FailureQ[#[[1]]] && GeoDistance[#[[1]],home][[1]]<10)&];


In my example, 94 markers out of 99 remain as "good". Then, we simply plot the markers on a map using GeoGraphics:


gp = GeoGraphics[{"Mississauga",Append[goodmarkers,GeoMarker[home,pin[White]]]}];
gins=DensityPlot[(x*1000-900000)/(1300000-900000),{y,0,1},{x,900,1300},ColorFunction->rcfun,AspectRatio->5,FrameTicks->{None,Automatic},Background->RGBColor[1,1,1,0.5],FrameStyle->Directive[Thick],LabelStyle->Normal];
Show[gp,Epilog->Inset[gins,Scaled[{1,0}],Scaled[{1,0}],0.028]]






In the second example let us color code the markers using price per square foot. Note that the square footage of the houses is not in the table, so we need to parse individual listings. So we need to do a more complicated web scraping:


url = "real_estate.html"; xml = Import[url,"XMLObject"];
formitems=Cases[xml,XMLElement["span",{"class"->"formitem formfield"},x_]->x,Infinity];
sqfeet=ToExpression[Last[StringSplit[#,"-"]]]&/@((If[#[[3]]=={},"0-0",#[[3,1]]])&/@Extract[formitems,(#+{0,1})&/@Position[formitems,XMLElement["label",{},{"Apx Sqft:"}]]] )


Note several things about this code:
  • The specific criteria to supply to Cases have been determined by inspecting the page code in the browser; in this case all the information bearing fields conveniently are <span> tags with classes formitem formfield. Your particular case will be different.
  • In the last line, Extract basically retrieves "every element following a label saying Apx Sqft:". Again your case will be different, and I admit that this is not the only way to get to the right info.
  • Local HTML file is used instead of a live URL. This is a trick dome to work around asynchronous deferred loading of listings on Toronto MSL website; if live URL were used, the scraper would only retrieve some 25 listings. The HTML file is obtaied by loading the MLS link, scrolling all the way down (not too fat so that all listings have a chance to load), then saving the webpage as a complete package and loading its HTML file into Wolfram Cloud (or creating a text file in the cloud and copy-pasting, or hosting it locally and making it accessible to Wolfram Cloud)
  • The last portion, involving Last[StringSplit[...]] is needed to convert approximate designations like "1500-2000" into a number 2000.

After this, the code is familiar, except that marker filtering should now include filtering out the listings without square feet information:



pricesperfoot = Quiet[prices/cfeet];
score=Quiet[(pricesperfoot-250)/(800-250)];
scorepins=Map[pin[rcfun[#]]&,score];
smarkertable = MapThread[GeoMarker[#1,#2]&,{locations,scorepins}];
sgoodmarkers=Select[smarkertable,(!FailureQ[#[[1]]] && GeoDistance[#[[1]],home][[1]]<10 
                    && NumberQ[#[[2,1,1,1,-1,-1]]])&];
gs=GeoGraphics[{"Mississauga",Append[sgoodmarkers,GeoMarker[home,pin[White]]]}];
Show[gs,Epilog->Inset[ginss,Scaled[{1,0}],Scaled[{1,0}],0.028]]


Here's the final result:



This is only an example I spent about an hour coding, another 1-2 polishing and another 1-2 hours writing about. Your mileage may vary. By the same token you can easily visualize houses according to any score you compute (such as "price per score determined by adding the number of bedrooms and half the number of washrooms"). You can also add multidimensional visualization, where a pin's size, or border color, or shape, or all of these, would convey different information. You can use geo-location and scrape some other website to score neighborhoods and show the "best bang for the buck" according to that score. You can build a linear regression machine-learning house pricing service. If you are dragged into the boredom of house hunting, there are always ways to make some colorful fun out of it :) .

Thursday, September 26, 2019

Esoteric multiplication and an (almost) failed exercise

In the multiverse of programming languages it's hard not to mention Brainf*ck, purposely written to have a minimal set of commands needed to (theoretically) program just about any problem out there thanks to its Turing completeness. The set is actually so minimal that I can include all of it, below. For non-programmers it would be useful to imagine an infinite linear magnetic tape with a read/write head able to move along it, like so:

Command    C-equivalent     Action

   >       ++ptr;           Move the head one position to the right
   <       --ptr;           Move the head one position to the left
   +       ++*ptr;          Increment the byte of data at the head position
   -       --*ptr;          Decrement the byte of data at the head position
   .       putchar(*ptr);   Write a keyed-in byte of data at the head position
   ,       *ptr=getchar();  Read and display the byte of data at the head position

   [       while(*ptr){     If the byte at the head position is zero, 
                             rather than executing the next command, 
                             jump forward to the matching ]
   ]       }                If the byte at the head position is non-zero, 
                             rather than executing the next command, 
                             jump back to the matching [


Well I mentioned it in conversations so often that it would have been a shame not to have tried it hands on. I took on a randomly chosen problem, namely writing a program that would multiply two single-digit numbers, totally out of the zone, somewhere in between spending last half an hour of the working day, thinking during my commute and in between playing and walking with the kid(s), and some half an hour debugging after the said kids went to sleep.

I got inspired by the addition code snippet, namely, [->+<], and tried to generalize it to summing several single digits in a row. My first example, [[->+<]>], expectedly resulted in an infinite loop; however a slightly more complicated [<[->+<]>>] ended up working fine.

My next challenge was how to replicate one of the operands in m*n an arbitrary number of times, i.e. to translate the memory layout [m n] into something like [m m ... m] where m would be repeated n times. After a few unsuccessful tries a working algorithm was:

  1. Replicate one operand 1 time, then 2 times, then 3 times, ... all the way to m_max times. This is somewhat ugly but realizable using something like
    [- >>>>>>> >+>>>>>> >+>+>>>>> >+>+>+>>>> >+>+>+>+>>> >+>+>+>+>+>>
         <<<<<<<  <<<<<<<  <<<<<<<  <<<<<<< <<<<<<< <<<<<<<]

  2. Replicate the second operand, decreasing it on every iteration, so that for n, zero is reached in front of n copies of m, like so
    [[- >>>>>>> + <<<<<<<] >>>>>>>-]

  3. Apply our previously written "sum all numbers until zero" [<[->+<]>>]

Taken together and surrounded with some I/O, the final program ended up being

,------------------------------------------------> read and convert n
,------------------------------------------------> read and convert m
<[- >>>>>>> >+>>>>>> >+>+>>>>> >+>+>+>>>> >+>+>+>+>>> >+>+>+>+>+>>
     <<<<<<<  <<<<<<<  <<<<<<<  <<<<<<< <<<<<<< <<<<<<<] replicate m
<[[- >>>>>>> + <<<<<<<] >>>>>>>-]>> propagate n
[<[->+<]>>]<. sum n copies of m

Note that, for brevity, I have limited one of the operands to 5, however the expansion to an arbitrary number would be straightforward by increasing all stretches of >>>>>>> and increasing the number of replications from 5 to the desired maximum.

You can test it in a BrainF emulator for yourself that it works (screenshot).


For debugging you can use something like this visualizer, but note that it's heavily memory limited.

The real surprise was when I decided to compare my solution against the internet, dimly suspecting that there may be far superior alternatives out there. And indeed, as this StackOverflow discussion nicely points out, there is a MUCH more elegant code doing this. Here goes,
[
 >[>+>+<<-]
 >[<+>-]
 <<-]

Compared to this, my example above looks like such baaaad code :)
What has this taught me? Several things:
  • Brainf*ck can be used as a measure of one's "internal memory capacity" for solving puzzles and math problems. Mine turns out to be pretty limited. I simply cannot, at least not quickly and entirely in my mind, come up with a program that requires you to keep track of more than 3 conditions at once (like 3 loops). Well, I kinda knew this already from my school-time profound hate of learning poems by heart.
  • On the other hand, I was able to come up with a working solution, all by myself, in circumstances that were very very far from optimal.
  • Learning curve is very important. Even in Brainf*ck, there are typical nuts-and-bolts programming tricks that make life so much easier once you've mastered them. I guess that just as I found it very difficult to stop thinking in higher-level languages such as (at least) the assembly, many people who never coded in their life would find it very difficult to grasp what we almost take for granted, such as i=i+1, or for-loops, or pointer arithmetic, or polymorphism, or templates.

I hope this exercise will make it a bit easier for me to explain programming to non-programmers.


Wednesday, June 5, 2019

Excel VBA: Automatic macro execution for Windows Task Scheduler (and a few pranks to boot)

Despite all modern trends towards workflow automation, manual Excel-based workflows still abound in many areas of industry, notably finance. In our previous post we learned how to simplify and automate some of them, reducing manual copy-and-paste to a button push.

The next step of this would be further automation, so that the"button" gets pushed automatically, for example on a daily basis at a predetermined time.

One intuitive approach would be to invoke your code using an Auto Macro such as Workbook_Open, and then have Windows Task Scheduler open your workbook on schedule as required.

However, there is a catch here. Workbooks used for automated workflow are very often also used for ad-hoc manual tasks, or are simply opened to view some data, with no intention of performing any (re)calculations. Now your auto macro code would run every time the workbook is opened. If the automated workflow involves some data modification and/or email notification, you will have side effects very time you open your workbook just to see what is in there.

Worse still, if your workbook is on a shared drive, other used may stumble upon it by accident, triggering execution of the auto macros when they never expect it.

Of course, you could run two copies of your spreadsheet - one for automation and one for manual inquiries, naming the "automation" one something like "never_ever_open_this_manually.xlsm", but what happens in practice in such cases is that the two workbooks fall out of sync, and one of them is always out of date (or worse, they are both partially out of date).

A much more elegant solution would be for the auto macro to detect that the workbook was invoked by the Task Scheduler and not manually, and run the workflow only in this case.

After some probing and looking for inspiration I have come up with this solution:

Private Function IsScheduled() As Boolean
 ' Determine if the workbook was opened manually or as part of a Task Scheduler routine
 On Error GoTo Decided
 Dim Result As Boolean
 Result = True ' An invisible or programmatically started session is always assumed scheduled
  If Not Application.Visible Then GoTo Decided
  If Not Application.UserControl Then GoTo Decided
 Result = False ' A session with more that one workbooks is always assumed manual
  If Application.Workbooks.Count > 1 Then GoTo Decided
 ' otherwise assume scheduled task if the workbook name was supplied in the command line
 Dim wname As String, cmdline As String
 cmdline = UCase(Trim(GetCommandLine)): wname = UCase(Trim(ThisWorkbook.Name))
 Result = InStr(cmdline, wname) > 0
Decided: IsScheduled = Result
 On Error GoTo 0
End Function

Private Sub Workbook_Open()
 If Is Scheduled Then
  ' Run your automated workflow here
 End If
End Sub

It basically relies on the discovery that if a workbook is started by the Task Scheduler, its command line would be
C:\Program Files\OFFICE##\Excel.exe v:\path_to_workbook\workbook.xlsm
whereas for most other scenarios of workbook opening (directly from Excel or from Windows Explorer) the command line would look like
C:\Program Files\OFFICE##\Excel.exe
or
C:\Program Files\OFFICE##\Excel.exe /dde
i.e. would not contain the workbook name (instead, it passes the workbook via DDE)

To get to the command line, we need to implement the following API function (taken from here):

#If Win64 Then
    Private Declare PtrSafe Function GetCommandLineL Lib "kernel32" Alias "GetCommandLineA" () As LongPtr
    Private Declare PtrSafe Function lstrcpyL Lib "kernel32" Alias "lstrcpyA" (ByVal lpString1 As String, ByVal lpString2 As LongPtr) As Long
    Private Declare PtrSafe Function lstrlenL Lib "kernel32" Alias "lstrlenA" (ByVal lpString As LongPtr) As Long
#Else
    Private Declare Function GetCommandLineL Lib "kernel32" Alias "GetCommandLineA" () As Long
    Private Declare Function lstrcpyL Lib "kernel32" Alias "lstrcpyA" (ByVal lpString1 As String, ByVal lpString2 As Long) As Long
    Private Declare Function lstrlenL Lib "kernel32" Alias "lstrlenA" (ByVal lpString As Long) As Long
#End If

Private Function GetCommandLine() As String
GetCommandLine = "FAILED TO RETRIEVE" ' fallback value is case of error
On Error GoTo Finally ' suppress errors
   Dim strReturn As String
   #If Win64 Then
    Dim lngPtr As LongPtr
   #Else
    Dim lngPtr As Long
   #End If
   Dim StringLength As Long
   'Get the pointer to the commandline string
   lngPtr = GetCommandLineL
   'get the length of the string (not including the terminating null character):
   StringLength = lstrlenL(lngPtr)
   'initialize our string so it has enough characters including the null character:
   strReturn = String$(StringLength + 1, 0)
   'copy the string we have a pointer to into our new string:
   lstrcpyL strReturn, lngPtr
   'now strip off the null character at the end:
   GetCommandLine = Left$(strReturn, StringLength)
Finally: On Error GoTo 0
End Function

Note the compiler directives to make the code 32/64-bit aware -- this is absolutely essential if the workbook resides on a shared drive in a network with mixed 32/64-bit Excel installation. Otherwise opening the workbook might crash the entire Excel session for random unsuspecting users.


BONUS: When debugging the automation routine I have stumbled upon some wonderful tools you can use to play a practical joke on your peers. Namely you can use Application.OnKey to override the function of a commonly used key (like, a letter or an arrow) to something totally bizarre, and wrap it into Application.OnTime to activate at some (possibly random) point in the future. Put it in your auto macro, like so:

Public num As Integer

Function sov(sekunder As Double) As Double
starting_time = Timer
Do
 DoEvents
Loop Until (Timer - starting_time) >= sekunder
End Function

Sub GetDizzy()
num = num + 1
On Error Resume Next
Select Case (num Mod 5)
Case 2 'move to opposite drection
 ActiveCell.Offset(0, 1).Select 
Case 3 'shake the workbook
 dx = Round(Rnd * 10) - 5: dy = Round(Rnd * 10) - 5
 For i = 1 To 3
  ActiveWindow.SmallScroll toright:=dx, down:=dy: sov (50 / 1000)
  ActiveWindow.SmallScroll toleft:=dx, up:=dy: sov (50 / 1000)
 Next i
Case 4 'nudge the workbook
 ActiveCell.ColumnWidth = ActiveCell.ColumnWidth + Round(Rnd * 15) - 7
Case Else 'normal operation
 ActiveCell.Offset(0, -1).Select 
End Select
If num > 200 Then MsgBox "You are tired and dizzy... Why don't you call it a day and go home?"
On Error GoTo 0
End Sub

Sub Payload()
 Application.OnKey "{LEFT}", "GetDizzy"
End Sub

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("8:00:00"), "Payload"
End Sub


and watch your colleagues of choice get delighted at the emphatic computer offering them to go home at the end of the working day because they are tired and have worked long hours and it's late outside (I've borrowed the sleep function sov() from here). The code is very hard to detect because it executes totally silently and persists even after the workbook containing has long been closed; the empathy will last until Excel is closed or restarted -- but in most industry workplaces I have seen, this only happens when it crashes.

Your mileage may actually vary with the payload, but do remember not to use it for any real sabotage because this is a surefire way to get fired, if you excuse the tautology again.


Tuesday, March 5, 2019

Excel VBA: Automatic opening of external files and other neat tricks

Suppose you have a workflow in Excel that involves manual copy-and-paste of data from one spreadsheet (A) into another spreadsheet (B), and you want to automate it.

In some cases, you might make use of data sources and automatic linking between files, but there are many cases where this would be undesirable. Either the automatic update of links makes your process undesirably slow, or you need those intermediate values unchanged for reporting/auditing purposes, or the links update is just too buggy and unreliable (as are, alas, many automatic features in the MS Office suite), whatever the reason, sometimes you are better off with the "plain old-school" VBA macro approach, approximately,

  1. Open the external spreadsheet A
  2. Find the source data
  3. Copy the data into your destination spreadsheet B
To do this gracefully, one must think through several scenarios though. What if the source spreadsheet is already open - should we re-open it (possibly discarding unsaved changes), or should we reuse the already opened sheet? What do we do after the data copying is done - should we force-close the source sheet or should we keep it? 

After giving these questions some thought, I have created a short VBA code snippet, and after having re-used it about a dozen times over the course of less than one month, I am putting it here for reference. Here goes:

Set targetWB = Application.ActiveWorkbook
TargetPath = targetWB.Path & "\"
SourcePath = "..\otherfolder" 'Relative to target workbook 
SourceName = "somename.xlsx" 'Can be any calculation to determine name

alreadyOpen = False
For Each thisWB In Workbooks
  If thisWB.Name = SourceName Then
   alreadyOpen = True: Set sourceWB = thisWB
  End If
Next thisWB
On Error GoTo ERRORHANDLER ' to gracefully handle "File not found" scenarios
 If Not alreadyOpen Then Set sourceWB = Workbooks.Open(Filename:=TargetPath & SourcePath & SourceName, ReadOnly:=True)
On Error GoTo 0

' *** Data copying code goes here *** 

If Not alreadyOpen Then sourceWB.Close SaveChanges:=False
Set sourceWB = Nothing: Set targetWB = Nothing ' garbage collection

Note a few touches here: 
  • The code reuses the already opened spreadsheet if it is already open (pardon the tautology), and opens it otherwise. This makes the code both faster for debugging (no repeated open/close - the source workbook can be large!) and suitable for automated production workflows. The only downside here is that you need to separate the actual name of the spreadsheet, and a path to it; however if you only have the full path, you can easily work around it with a one-liner like so:
    SourceName = FullPathName: While InStr(SourceName,"\") > 0: SourceName = Right(SourceName,Len(SourceName)-InStr(SourceName,"\")): Wend
    using FullPathName instead of TargetPath & SourcePath & SourceName in Workbooks.Open(...).
  • The code automatically closes the source spreadsheet only if it had to open it. This way, no spurious workbooks are left open in a production workflow.
  • The files are opened read-only and closed without saving changes. This prevents unwanted queries that can otherwise pop up if the file happens to be opened by another user, or if the data copying process ends up messing up the source file (which, depending on how complicated the data processing routine is, is quite likely to happen). These queries are annoying whilst debugging and totally disruptive in a production workflow; last but not least we are preventing unwanted modification of the source file.




BONUS: Here is the routine I commonly use for the actual data copying. I find it preferable to using selection / clipboard / autofilter operations because it involves less GUI interaction and therefore is more robust (and, with Application.ScrenUpdating=True, can be faster!)
targetWS = "Analysis" : sourceWS = "Data" 'use any names 
targetStartAt = "A2": sourceStartAt = "A2" 
  'feel free to use named ranges here
  'or to have your code determine the locations based on search criteria
Set here = sourceWB.Worksheets(sourceWS).Range(sourceStartAt)
Set there = targetWB.Worksheets(targerWS).Range(targetStartAt)
While Len(here.value) > 0 
 If Not IsError(here.Value) Then
  If here.Value = "GOOD" Then ' put whatever condition to validate a source line to determine if it needs copying
   ' example of data copying, edit as your task requires
   there.Value = here.Value
   there.Offset(0,1).Value = Left(here.Offset(0,1).Value,8)
   For i = 4 to 12 
    there.Offset(0,i-2).Value = here.Offset(0,i).Value
   Next i
   Set there = there.Offset(1,0)
  End If
 End If
 Set here = here.Offset(1,0)
Wend


Wednesday, February 6, 2019

No more calculation babysitting!

Let's imagine you work with a computer on a daily basis (which, as it were, is a rather common scenario these days), and let's imagine your work includes "relatively lengthy" computational tasks -- lengthy enough that it is not productive to just sit there staring at the proverbial hourglass cursor (or meditating over the progress bar, or praying over the build output console window that it compiles without errors, or whatever it is). So...

So, after some time waiting (depending on your boredom threshold, about 30 seconds for me, I'd guess under 2 minutes for most people) you decide to multitask and switch to another task (work-related or otherwise). Before long, that other task immerses you and you realize that your lengthy computation was actually done minutes ago and you could have, and should have, resumed your workflow earlier. So in an attempt to avoid doing nothing and increase productivity, you just dumped it down the tubes.

Or consider another scenario. Your computation is now lengthy enough (say, 10-20 minutes) so that you decide to grab a coffee from the kitchen, or grab a quick bite/smoke/chat, or whatever it is. It would be cool if you could get an alert that your task has finished, so you can timely return and resume work without having to check on your workstation multiple times.

Or yet another scenario. You need to run an even lengthier calculation, perhaps a few hours on end, so you leave it running after hours. And you have several of them to run after each other, and you want to run as many as you can before the next working day. So again it would be cool if you can get an alert once your calculation finishes, rather than having to log in and check on the computation  progress repeatedly. More importantly, if your task is aborted early due to some mishap (which happened to me due to my own banana fingers more times than I'd confess), you really want to be alerted at once, rather than after what you thing the task should have taken, had it completed normally.

The first scenario can partially be mitigated by running Windows Task Manager, minimizing it, and noticing when the CPU usage drops, indicating that your task has finished. But you still need to remain vigilant and keep watching that tiny indicator, and with current multicore CPUs, the drop may be from 13% to some 2%, which is not very noticeable visually. Not to mention that the two remaining scenarios cannot be worked around in this way.

Wouldn't it be nice to have an automated monitor which can do this for you?

Yeah it sure would.

So let's see how we can do it in Windows Powershell. You can determine the CPU usage of a process using this function (loosely adapted from here):

function get-excel-CPU ($avgs=1)
{
 $result=0
 for ($i=1; $i -le $avgs; $i++)
 {
  $cpuinfo = Get-WmiObject Win32_PerfFormattedData_PerfProc_Process -filter "Name LIKE '%EXCEL%'"
  $result=$result + ($cpuinfo.PercentProcessorTime |Measure -max).Maximum
  start-sleep -m 150
 }
 $result = $result/$avgs
 $result
}

Note the following:

  • I use EXCEL as an example because I use it most often. It is trivial to modify it to work with any other program.
  • The function takes into account that there can be multiple instances of your program, and will report the CPU usage of the most CPU intensive process. Usually, this is what you want, because only one of your instances will be doing computations anyway, but you can easily fine-tune it to be more instance-specific.
  • The function measures CPU usage several ($avgs) times with a short waiting period in between, and then averages the measurement. This is done because some computations, mostly ones heavily on local or network I/O, will have wildly fluctuating CPU usage, so taking one measurement may trick the script into falsely deciding that the computation has finished. You may need to fine-tune the number of measurements and the wait time between them to reflect your specific computation pattern.

Now that we have a way to automatically determine the CPU usage, we can easily wrap it in a control loop:

$poll = 10
$homethresh = 600
$sensitivity = 5
$fullout = new-timespan -Seconds 86400
$mailout = new-timespan -Seconds $homethresh
$sw = [diagnostics.stopwatch]::StartNew()

$thiscpu = get-excel-cpu 5

if ($thiscpu -lt $sensitivity) 
{
 write-host $thiscpu, ": Excel not running, exiting."
}
else
{
 write-host "Initial CPU is ", $thiscpu
 $cnt=0
 while ($sw.elapsed -lt $fullout)
 {
  start-sleep -s $poll
  $thiscpu = get-excel-cpu 3
  $lock = is-locked
  write-host "Elapsed", $sw.elapsed, " -- CPU is ", $thiscpu, "   ",$lock
  if ($thiscpu -lt $sensitivity) {$cnt++} else {$cnt=0}
  if ($cnt -ge 2)
   {
    write-host "FINISHED!!!!"
     if ($lock -eq "UNLOCKED") {show-splash} else {phone-home}
    return
   }
 }  
 write-host "Timed out!"
}

Note the line if (...) {show-splash} else {phone-home} . There are two ways to alert you that the computation has finished. One is to show you a big splash screen, borrowed from here:

function show-splash 
{
 Add-Type -AssemblyName System.Windows.Forms
 $Form = New-Object system.Windows.Forms.Form
  $Form.Text = "Finished"
  $Form.AutoSize = $True
  $Form.AutoSizeMode = "GrowAndShrink"
  $Form.BackColor = "Lime"
  $Font = New-Object System.Drawing.Font("Arial",96,[System.Drawing.FontStyle]::Bold)
  $Form.Font = $Font
  $Label = New-Object System.Windows.Forms.Label
  $Label.Text = "Calculation finished!"
  $Label.AutoSize = $True
  $Form.Controls.Add($Label)
  $Form.Topmost=$True
  # -- this ensures your splash screen appears on top of other windows!
 $Form.ShowDialog()
}

The other is to simply send you an email that gets pushed to your smartphone or smart watch, borrowed from here (using Outlook rather than Send-MailMessage so that your IT department can safely inspect your outgoing email and won't mistake your script for a trojan):

function phone-home
{
 $Outlook = New-Object -ComObject Outlook.Application
 $Mail = $Outlook.CreateItem(0)
  $Mail.To = "youremailaddress@mailserver.com"
  $Mail.Subject = "Calculation finished"
  $Mail.Body ="Your calculation has finished. If you need to start another one, go for it."
 $Mail.Send()
}

Now, how to choose between the two? You will want the splash screen if you are sitting in front of your screen, and the email otherwise. So we need a way of discriminating between the two. Following this idea, we can use
function is-locked
{
try {
$currentuser = gwmi -Class win32_computersystem | select -ExpandProperty username
$process = get-process logonui -ea silentlycontinue
if($currentuser -and $process){"LOCKED"}else{"UNLOCKED"}
return}
#Always return LOCKED if logged in remotely
catch{"LOCKED";return} }

Finally, here is a BAT-file one-liner wrapper, called ps.bat to run your PowerShell script on systems where execution of random scripts has been disallowed by default (for good reason). We cannot override this default without admin privileges, but we can by pass it temporarily by calling

@powershell -ExecutionPolicy RemoteSigned .\%1.ps1

You can then call your PowerShell script, e.g., poll.ps1, and simply type ps poll in your command prompt to invoke it quickly.

Enjoy!

Tuesday, November 20, 2018

Memory leak tester in Excel

In many scenarios involving complicated Excel calculations, especially those relying on extensive VBA code and/or custom-made add-ins, there is a danger that memory leaks could be created by programming errors such as careless object operations.

Most often, such memory leaks are discovered post factum, when, left unchecked, they cause slowdowns or crashes when an Excel session runs out of memory. In such a case, correcting the error becomes a major pain. How to spot a memory leak offender in a calculation intensive spreadsheet containing thousands of custom functions? 

To this end, I have written a very simple program that basically evaluates a given formula multiple times and measures how the memory consumption increased. I have used this googled snippet to determine the memory consumption:

Declare Function GetCurrentProcessId Lib "kernel32" () As Long

Function GetMemUsage()
  ' Returns the current Excel.Application memory usage in KB
  Set objSWbemServices = GetObject("winmgmts:")
  GetMemUsage = objSWbemServices.Get( _
    "Win32_Process.Handle='" & _
    GetCurrentProcessId & "'").WorkingSetSize / 1024

  Set objSWbemServices = Nothing ' We don't want to cause memory leaks here :)
    ' We don't want to cause memory leaks here :)  
End Function

and wrote a very simple wrapper: 

Sub Measure_Leak()
 Set here = ActiveSheet.Range("A2")
 template = here.Value
 i1from = here.Offset(0, 1).Value: i1to = here.Offset(0, 2).Value
 i2from = here.Offset(0, 3).Value: i2to = here.Offset(0, 4).Value
 Set there = here.Offset(0, 5)
 
 there.Offset(0, 1).Value = GetMemUsage()
 
 For i1 = i1from To i1to
 For i2 = i2from To i2to
  working = "=" & template
  On Error Resume Next
   working = Replace(working, "$1", i1)
   working = Replace(working, "$2", i2)
  On Error GoTo 0
  there.Formula = working
 Next i2, i1
 
 there.Offset(0, 2).Value = GetMemUsage()
 Set here = Nothing
 Set there = Nothing
 ActiveSheet.Calculate
End Sub

Now I put this on an even simpler spreadsheet which looks like this:



Basically, pressing the Measure button evaluates the template expression substituting $1 and $2 with values spanning two ranges, a total of (i2t-i2f+1)*(i1t-i1f+1) times. An increased memory footprint at the end of the execution means there is a memory leak.

In the screenshot we see that a built-in Excel function does not cause any memory leaks (hurra!)

To test it, let us define a really leaky VBA function using this example:

' Put this into class module Class1
Option Explicit
Private A As New Class2
Private Str As String
Private Sub Class_Initialize()
   Set A.B = Me ' Fool garbage collector
   Str = Space(1024 * 10) ' Allocate lots of memory 
End Sub

'Put this into class module Class2
Option Explicit
Public B As Class1

with two functions that look similar but one is known to be leaky:

Function vbaNoLeak()
 Dim MyObject As Class2
 Set MyObject = New Class2
 Set MyObject = Nothing
End Function

Function vbaLeak()
 Dim MyObject As Class1
 Set MyObject = New Class1
 Set MyObject = Nothing
End Function

...and...




Note that using this method to troubleshoot parts of your VBA macro won't always work because many functions are prohibited inside VBA functions (they abort immediately, basically ensuring that VBA functions have no side effects). But it is very easy to modify the code above to be callable as a procedure from within a VBA macro.


Tuesday, August 7, 2018

Laurel / Yanny Hands-On

Ever since the Laurel/Yanny auditory illusion went viral, I had a suspicion that we are dealing with a bifurcation-type illusion similar to the "figure or ground" type:


That guess seemed correct with the publishing of a NY Times article where you can "move a slider" to augment the clip in either direction, causing either "Laurel" or "Yanny" to be more pronounced.

I admit that this article is quite revealing and educating (it taught me how I can tweak my brain into hearing one or the other). But moving a slider still seems rather artificial. For all I know they could have been cheating, actually having two separate recordings and the slider mixing them in different proportions.

Can we prove this wasn't cheating? Hands on?

Yes we can.

Some snooping around reveals that here is a working version of the original recording:
https://ia802800.us.archive.org/28/items/YannyVsLaurelVideoWhichNameDoYouHear-Audio/Yanny%20vs%20Laurel%20video%20which%20name%20do%20you%20hear%20%E2%80%93%20audio.mp3

Let's go to Wolfram Cloud Computing and run this simple program:

url="(the url above)"
audio=AudioTrim[Audio[url],{0,4}]
CloudExport[AudioPitchShift[audio,1.1],"wav"] (*Laurel*)
CloudExport[AudioPitchShift[audio,0.85],"wav"] (*Yanny*)

We see that at the heart of it is AudioPitchShift which simply shifts the pitch of the recording by the desired amount. Looks like lower frequencies emphasize "Yanny" while higher frequencies bring out "Laurel", in agreement with the above mentioned Wikipedia article.

My conjecture, based on the observation that "Laurel" is lower-frequency than "Yanny", is that we tend to hear whatever is closer to the maximum frequency sensitivity of our ears, so whoever initially hears "Yanny" likely has an ear for higher-pitched tunes than the "Laurel" guy.



It could have been a bit easier if Wolfram Cloud could actually play audio from within the interface without resorting to CloudExport. Still, it opens nearly endless possibilities for experiments. Enjoy!

Monday, August 6, 2018

Old Phone/Tablet as an Info Board: Table of Contents

Hi folks, now that this long overdue series of posts is complete, here's a table of contents for the ease of the reading.



Enjoy!

TL/DR: This is a series of (moderately boring) posts about how to turn your old and outdated tablet or smartphone into an information board you can use at home. Uses range from purely practical to purely aesthetic.

Old Phone/Tablet as an Info Board Final Part: Flow Control and Asynchronous Dynamic Data Refresh

This is the final post about how to make an information board out of your old tablet or smartphone.

Let's assume you have all your elements that deliver your information as you want it, beautifully designed and tested, again using my board as an example:




OK, the information is up to date when the page has loaded. Now, there comes a question: how to keep all this information reasonably up to date, ready for you at any moment in case you need it? On-demand refresh of any kind is not an option - an info board you'd have to stand in front of, waiting, is a lousy info board.

It would seem easy at first - "just do a META REFRESH", - but there are several pitfalls to think through:

  1. It is obvious that you need to refresh all the elements with some frequency. But how to choose this frequency? Too seldom, your info is out of date half the time. Too frequently, and your board will spend more time refreshing than actually showing the info, and you risk being kicked out by your data sources for DDOS'ing them. 
  2. Further elaborating on the idea, it is clear that some elements need a more frequent refresh than some others: you are quite OK with a weather forecast obtained half an hour ago, but train departures from half an hour ago are totally useless to you. 
  3. Furthermore, the refresh frequency should be time dependent. You don't need frequent traffic updates in the middle of the night, but you do need them during commute hours.
  4. What to do if an element fails to refresh, or gets stuck? Should we perhaps retry sooner than its normal refresh cycle? Then again, what would be the reasonable timeout? Set it too short and you'll mistake normal wait times for "getting stuck".
What it all means that you will need to refresh asynchronously (i.e. some elements but not others), and dynamically (using time intervals that are element and time dependent), and you'll have to have a mechanism to check if the refresh was actually successful or needs to be retried. Also note that some elements (notably those that use jQuery on a hidden iframe) will need a two-step refresh: some method to reload the iframe, and another to process its contents once it has loaded and rendered in DOM (but no sooner).



So, let us begin by introducing a "refresh handler" like so:

var dummydate = new Date();
var rExample = {freq: 30, // refresh every 30 minutes by default
    prime: [{freq: 5, start:{h:7, m:30}, end:{h:8, m:30}}, // between 7:30 and 8:30 refresh every 5 minutes
            {freq: 15, start:{h:15, m:30}, end:{h:20, m:30}}], // between 15:30 and 20:30 refresh every 15 minutes
    last: dummydate, next: dummydate, fresh: true,  overdue: 0, //internal variables
    lag: 5, retry: 45, //internal constants
    handleRefresh: function(){...}, // refresh function 
    handleReady: function(){...}, // "is ready" function
    handlePost: function(){...} // post-refresh function for 2-stage refresh
    };  


We see that this handler is a self-contained object that handles all refresh code for "something on the board". Then, given an array of these handlers, we define an event poller to be called periodically:

const scale = 1; //seconds per minute: 1 for debugging; 60 for actual use
var REFRESH = [rWeather, rTrain1, rTrain2, rBus, rHourly, rMap, rTravel];

function pollEvent()
{
var now= new Date();
// add force refresh @ 2AM, ONCE

for (var i=0, len=REFRESH.length; i<len; i++)
{
 var handle = REFRESH[i];
 if(now > handle.next) {
 try{
  if (handle.handleReady()) {
    handle.overdue=0;
 var behind = parseInt((now.getTime() - handle.last.getTime())/1000/scale);
 $("#ref"+(i+1)).text(behind);$("#ref"+(i+1)).css("color","green");
  }
  else { console.log(i+" not ready")
        if (handle.overdue > 5) {console.log("***FORCE REFRESH***"); /*location.reload();*/};
  handle.overdue++;
  $("#ref"+(i+1)).text("x");$("#ref"+(i+1)).css("color","darkred");
  };
  
   console.log("refresh "+i); 
   handle.handleRefresh(); handle.fresh = true;
   postprocess(handle);
   handle.last = now;
   handle.next = new Date(now.getTime() + 1000*((handle.overdue==0)?handle.retry:scale*timespan(handle,now)));
   } 
catch(ignore) {handle.overdue++; $("#ref"+(i+1)).text("X");$("#ref"+(i+1)).css("color","red"); handle.next = new Date(now.getTime() + 1000*handle.retry);} 
 }
}
setTimeout(pollEvent,100*scale); // call again, every 6 seconds in production 
};


Here timespan() defines the refresh frequency for a given handler at a given time:


 function timespan(handle,stamp)
{
 var ts = handle.freq;
 for (var j=0; j<handle.prime.length; j++)
 {
  var thisprime = handle.prime[j];
  if (stamp.getHours()>= thisprime.start.h && stamp.getHours()<= thisprime.end.h 
  && ( stamp.getHours()>thisprime.start.h || 
     (stamp.getHours() == thisprime.start.h && stamp.getMinutes()>= thisprime.start.m))
  && ( stamp.getHours()<thisprime.end.h || 
     (stamp.getHours() == thisprime.end.h && stamp.getMinutes()<= thisprime.end.m))
   ) {ts = thisprime.freq;}
 }
 return ts;
}


Now define another function to ensure two-step refresh happens as fast as possible:


function postprocess(handle){ 
 try{
  if(handle.handleReady) {console.log("++"); handle.handlePost();}
  else {console.log("--");setTimeout(function(){postprocess(handle);}, 1000*((handle.fresh)?1:handle.lag)); handle.fresh=false; };
}
 catch(ignore) {}
}


It only remains to define some auxiliary routines


function frameload(frameid){$(frameid).attr("src",$(frameid).attr("src"));}
function nop(){return true;}
function clock(){
var now = new Date(); var h=now.getHours(); var m=now.getMinutes(); var s = now.getSeconds();
$("#nowclock").text(((h>=10)?h:("0"+h)) + ":" + ((m>=10)?m:("0"+m)) + ":" + ((s>=10)?s:("0"+s)));} 


and add an initial invocation upon document's DOM ready:


$( document ).ready(function() 
{
var now = new Date(); var h=now.getHours(); var m=now.getMinutes();
$("#loadclock").text(((h>=10)?h:("0"+h)) + ":" + ((m>=10)?m:("0"+m)));
setInterval(clock, 1000);
setTimeout(pollEvent,3000);
});



That's all. For reference here are the refresh handlers for all the elements:


 var rWeather = {freq: 30, 
 prime: [{freq: 15, start:{h:7, m:30}, end:{h:8, m:30}}, 
   {freq: 15, start:{h:15, m:30}, end:{h:20, m:30}}],
 last: dummydate, next: dummydate, fresh: true,  overdue: 0, lag: 5, retry: 45,
 handleRefresh: function(){f(document, 'script', 'plmxbtn');},
 handleReady: function(){return $("#weather").find(".city").length>0;},
 handlePost:nop };
 
 var rTrain1 = {freq: 120, 
 prime: [{freq: 10, start:{h:5, m:30}, end:{h:9, m:0}}, 
   {freq: 2, start:{h:7, m:45}, end:{h:8, m:20}}],
 last: dummydate, next: dummydate, fresh: true,  overdue: 0, lag: 1, retry: 10,
 handleRefresh: function(){frameload("#go1");},
 handleReady: function(){return $("iframe#go1").contents().find(".currentDateMain").length>0;},
 handlePost:trainhide };
 
 var rTrain2 = {freq: 20, 
 prime: [{freq: 5, start:{h:7, m:30}, end:{h:8, m:30}}],
 last: dummydate, next: dummydate, fresh: true,  overdue: 0, lag: 1, retry: 10,
 handleRefresh: function(){frameload("#go2");},
 handleReady: function(){return $("iframe#go2").contents().find(".currentDateMain").length>0;},
 handlePost:trainhide };
 
 var rBus = {freq: 20, 
 prime: [{freq: 5, start:{h:6, m:15}, end:{h:9, m:0}}, 
   {freq: 1, start:{h:7, m:45}, end:{h:8, m:15}}],
 last: dummydate, next: dummydate, fresh: true,  overdue: 0, lag: 1, retry: 10,
 handleRefresh: function(){frameload("#miway");},
 handleReady: function(){return $("iframe#miway").contents().find("#bustoken").length>0;}, 
 handlePost:nop };
 
 var rHourly = {freq: 60, 
 prime: [{freq: 15, start:{h:7, m:30}, end:{h:8, m:30}}, 
   {freq: 15, start:{h:15, m:30}, end:{h:20, m:30}}],
 last: dummydate, next: dummydate, fresh: true,  overdue: 0, lag: 5, retry: 45,
 handleRefresh: function(){frameload("#forecast");},
 handleReady: function(){return $("iframe#forecast").contents().find("table.wxo-media")
   .find("[headers='header1']").length>0;},
 handlePost:forecast }; // *** add clear if exists
 
 var rMap = {freq: 30, 
 prime: [{freq: 5, start:{h:7, m:30}, end:{h:8, m:30}}, 
   {freq: 5, start:{h:15, m:30}, end:{h:18, m:00}}],
 last: dummydate, next: dummydate, fresh: true,  overdue: 0, lag: 10, retry: 45,
 handleRefresh: refreshMap,
 handleReady: function(){return true;}, 
 handlePost:nop }; // *** add clear if exists?
 
 var rTravel = {freq: 20, 
 prime: [{freq: 5, start:{h:7, m:30}, end:{h:8, m:30}}, 
   {freq: 5, start:{h:15, m:30}, end:{h:18, m:30}}, 
   {freq: 1, start:{h:7, m:50}, end:{h:8, m:10}}, 
   {freq: 1, start:{h:17, m:00}, end:{h:17, m:30}}],
 last: dummydate, next: dummydate, fresh: true,  overdue: 0, lag: 5, retry: 45,
 handleRefresh: navigate, 
 handleReady: function(){return ( parseInt($("#result1").text()) > 0 
   && parseInt($("#result2").text()) > 0);}, 
 handlePost:nop }; 




The code also includes forced refresh once daily at approximately 2:00 AM. This is done to prevent an occasional memory leak to screw our browser (we'll be running this 24/7 for months on end, remember?). I am leaving this part as an exercise for the reader - the flow chart is basically, "if the current time is between 2:00 and 3:00, and if the startup day is not the same as the current day, do location.reload()".

Still, I can see that the Playbook browser (or to be exact, an app called Backlight Override, which is just a browser wrapper that additionally prevents the backlight from ever going off) does crash - once every 3-4 weeks. Well, for me, this is a fairly acceptable "mean time between failures", even though anything more frequent than once a week would already border on annoying.


Thursday, August 2, 2018

Old Phone/Tablet as an Info Board Part 5: PHP capture-and-process

Finally, I describe one more method of putting information on the info board, suitable when there is a very limited amount of information you need from the target page, and when your output will have very little, if anything, to do with the target page layout. It is also the only possible method when the server outputs data in any format other than HTML.

The example is our local bus company, where I would like to get the bus departure info for a certain stop. In the browser, the result looks like this:



Now the biggest difficulty about using the previously described methods is the lack of a clearly defined URL associated with the results page - the results are dynamically generated by the parent page's javascript. After some sniffing around, it was possible to extract a working request link in the format
http://www4.mississauga.ca/PlanATrip/NextPassingTimes/RequestNextPassingTimes ?suggestionInputIdentifier=(stop_number) &suggestionInputType=Stop &stopInputIdentifier= &mustBeAccessible=false

which brings up the result in this JSON-like format:



Well, because the departure information I am after is somewhere in this JSON, I decided to parse it entirely in PHP using preg_match_all, extracting route number and arrival time for the next upcoming bus, as well as three more after it, like so:

<?php
date_default_timezone_set('EST');
$opts = array('http'=>array('header' => "User-Agent:MyAgent/1.0\r\n"));
$context = stream_context_create($opts);
$url = "http://www4.mississauga.ca/PlanATrip/NextPassingTimes/RequestNextPassingTimes?suggestionInputIdentifier=1127&suggestionInputType=Stop&stopInputIdentifier=&mustBeAccessible=false";
$timestamp = date("H:i");
$code = file_get_contents($url,false,$context); 
 
$code = strstr($code, 'NextPassingTimesList',false);
$code = strstr($code, 'NextPassingTimesLegend',true);
// now code contains only trip data
$rid = preg_match_all("/data-route-key=([^=]+)data-trip-key/",$code,$matches1);
$tid = preg_match_all("/NextPassingTimesTime ([^N]+)u003c\/div/",$code,$matches2);
for($i=0;$i<$rid;$i++)
{
$route = str_replace("\\","",$matches1[1][$i]); $route = str_replace("\"","",$route); $route = str_replace("r","",$route);$route = str_replace("n","",$route);
$routes[$i] = str_replace("~~East"," E",$route);
 
$time = $matches2[1][$i];
$id1=stripos($time,"u003e"); 
$time = substr($time,$id1+5,strlen($time)-($id1+5)-1);
$sid = stripos($time,"u003c");
if ($sid!==false) {$time = substr($time, 0, $sid-1);}
$times[$i] = $time;
}
$color = (strpos($times[0],"min")===false)?"rgb(255,128,128)":"red";
 
echo '<html><head></head><body>';
echo '<div style="color:rgb(255,128,128); font-family: Arial, Helvetica, sans-serif; font-size: 20px; position: relative; top: -5px;">';
echo '<span style="color:gray;">', $timestamp, '&nbsp;</span>';
echo '<span id="bustoken" style="background: ',$color,'; color:white;">', " ".$routes[0], '</span>',  '<span style="font-weight: bold; color:',$color,'">', "&nbsp;", $times[0], '</span>';
if ($rid > 1 && $tid > 1)
{
    echo "<br/>";
    echo '<span style="color:rgb(255,235,235);">', $timestamp, '&nbsp;</span>';
    for ($j=1; $j<(($rid>4)?4:$rid); $j++)
    {
        echo '<span style="background: rgb(255,128,128); color:white; font-size: 14px;">', " ".$routes[$j], '</span>',  '<span style="font-weight: bold; color: rgb(255,128,128);font-size: 14px;">', "&nbsp;", $times[$j], '</span> &nbsp;';
 
    };
}
echo '</div></body></html>';
?> 


Putting it into our standard PHP iframe like this:
<div id="bus" class="basic info" style="position: absolute; left: 150px; bottom: 270px; width: 415px; height: 50px; background:rgb(255,235,235);">
<iframe id="miway" scrolling="no" src="bus.php" style="height: 100px; width: 415px; border: none;"> </iframe>
</div>


The result looks like:


Note the following tricks:
  • Whenever the real-time information is available ("the bus is leaving in XX minutes"), I am accenting the color to highlight it.
  • I am also outputting the time of the query, so that the "XX minutes" format remains meaningful without having to query every single minute.
  • The line with date_default_timezone_set serves to ensure correct DST for $timestamp.
  • As a future development, I plan to supplement the "XX minutes" format with actual projected time, replacing it with "HH:MM (XX min)" and retaining accentuation.
  • As another exercise, I plan to change color accentuation depending on the remaining time - highlighting buses that are still reachable given the walking/running distance to the stop, and dimming the buses that aren't.

I admit that this method, as implemented, is quick-and-dirty and extremely brute-force, and is rather vulnerable to the underlying data format changes. A much more robust way would be to have the PHP parse the JSON "the proper way" and either process it using dedicated JSON functions, or simply echo the HTML result in its entirety, using jQuery to interact with it. However, my approach has worked surprisingly well for over 6 months already - and "if it ain't broken, won't fix it".