• CSV to gnucash via QIF with the help of awk

    A simple task, i have a database with income information that is logged from a cash register. I have a number of columns that say the daily total, products sold, cash amount, credit card amounts etc. For accounting reasons i want to import this directly into GNU cash and avoid all manual entry. In the past i have use CSV/TXT formats but these cannot handle the split automatically.

    In GNUCash i want to put the daily total in income, and transfer as a split to the cash account and the Accounts Receivable account to handle the money owed by the credit card company. CSV/TXT import can’t handle split but QIF can.

    My example sql :-

    select DATE(DATE), NETSALE_value,PO_1_value,CASH_IN_D_value+CHEQUE_IN_D_value,CHG1_IN_D_value from fin_report_Z1 where DATE>"2017-04-5" AND DATE<"2018-04-06" INTO OUTFILE '/tmp/blah.txt' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

    This will produce a file in /tmp called blah.txt with a number of columns. the ones i care about here are NETSALES, PaidOut, Cash+Cheque total, and Credit card total. I want to send the Cash+Cheque total to the cash account and the credit card total to the A/R account, also i want to handle any paid out by transferring to a liabilities account. Next come AWK

    BEGIN {
    print "!Account"
    print "NIncome:Sales:UK:Shop"
    print "D"
    print "TOth L"
    print "^"
    print "!Type:Oth L"
    print "D"$1
    print "T-"$2
    print "PShop End Of Day"
    print "SAssets:Accounts Receivable:FDMS Incomming"
    print "ECredit Card"
    print "$-"$5
    print "SCash"
    print "ECash"
    print "$-"$4
    if ($3!=0) {
    print "SLiabilities:PaidOut"
    print "EPaid Out"
    print "$-"$3
    print "^"

    And to invoke the entire thing:-

    rm /tmp/blah.txt
    mysql -D stock -u username -p --batch < /root/exportsales.sql awk -f /root/exportsales.awk /tmp/blah.txt > 201718.qif

    it will prompt your your mysql password then dump the finished qif in the current folder. Importing in to GNU cash is just using the qif importer, ensure the date format is correct then ensure account names match your layout and it works!

  • MySQL/MariaDB to excel

    Sometimes you just need to export some data and have it ready in a spreadsheet to email to sales and marketing. Yes i know a csv is a better storage format but the objective is to send the data in excel format so that people use to *just* using excel can carry on doing their work.

    Turns out this is really easy to do on a linux server.

    First create some sql query, for example pulling sales data from a sales table, and using the stock_details table to get its name where the data is a specific brand.. It does not matter really its going to return a fixed no of columns based on the query, lets call this query.sql

    use stock;
    select sales.barcode,stock_name,delta from sales,stock_details where sales.barcode=stock_details.barcode AND stock_details.brandid=1;

    Next we have a little script to do the magic (getsales.sh) :-

    dir=$(mktemp -d)
    mysql -u ROUSER --batch < query.sql > "$dir/sales.txt"
    tr '\t' ',' < $dir/sales.txt > $dir/sales.csv
    libreoffice --headless --convert-to xls:"MS Excel 97" $dir/sales.csv --outdir $dir
    echo "sales report" | mutt -a "$dir/sales.xls" -s "Sales report" -- sales@example.com

    This has a number of steps. Firstly run the mysql query in “batch” mode this dumps the results as tab separated values. Next libreoffice choked on tab separated so we use tr to translate to comma separated, may be an infilter option would cure this. Then we feed the comma separated into libreoffice in “headless” mode and this generates the Excel file for us. Finally mutt is used to write an email with the attachment.

    One thing to note, the msql user in this example ROUSER must have prompt-less  READ ONLY access to the required database/table(s) By prompt-less i mean that the password is not required to be entered each time. To achieve this create a file ~/.my.cfg with the following contents


    where thepassword is the actual password for ROUSER.  Remember to set permissions on this file to be read/write only to the user. Also remember to make the user in the database only have read only access to the things it needs for additional safety.

    Now stick that in a cron job and bingo, automatic spreadsheets via email!


  • Reversing a heat pump protocol

    I’ve got an air source heat pump that communicates to a HMI (human machine interface) display and updates it with various status on temperatures, and what the compressors/fans etc are doing. I also have a fault with the system that every few days it trips out with an F6 fault, which is annoying. But to top that off the company that installed it have gone bankrupt so i’m pretty much on my own.

    Looking at the controller it looks like one from http://www.gzasl.com/en/page.html?id=28 but there is little info on their site and i’ve never had any success reaching out to these kinds of companies as they don’t want to deal with individuals and usually have some big organised reseller/importer network.

    Anyway back to the protocol, the HMI is fed from 2 wires, there appears to be a 12-14V differential between them, which would make sense for power, but also i can see on my DVM there is a non dc component. The voltages are floating at about 30v wrt earth so need to be a little careful connecting to them as i’ve no idea what the circuit is on the other end and if its safe to ground one side.

    Looking with a differential scope, we see :-

    Well that looks promising, there is data there.  Looks like two distinct sources as well, one pulling the voltage down, one pumping it up. The starts of the downward blocks are almost 250ms apart so this looks like a regular update message. Lets zoom in …

    What we see is 5 pulses, which may be a SOP marker. Then there are various pulses in other positions until we get back 20ms later to the 5 pulses again. This suggests to me that the 5 pulses are indeed a SOP and the data occurs after this. Start of one pulse to start of next is 400us (2.5Khz)

    Into the land of assumptions now, looking at the last pulse i’ve ever found before we get back to the next SOP marker i think there is room for 40 pulses or 5 bytes. There is room for another 5 pulses but these are always 0 (so far) and i’m assuming they are not important, this could prove to be incorrect.

    I exported the picoscope data as a CSV file and ran it through a C# chopper to detect the SOP and extract the bits and get the following results (showing raw bits and my first guess at byte decoding:-)

     SOP 01110000 0x70 00011100 0x1c 00000000 0x00 00111000 0x38 0000111 0x07 
    SOP 00000000 0x00 00011100 0x1c 00000000 0x00 00000000 0x00 0000000 0x00 
    SOP 01110000 0x70 00000000 0x00 11100111 0xe7 00000000 0x00 0000000 0x00 
    SOP 00000000 0x00 00011100 0x1c 00000111 0x07 00000001 0x01 1100000 0x60 
    SOP 00000011 0x03 10011100 0x9c 11100000 0xe0 00000001 0x01 1100000 0x60 
    SOP 00000000 0x00 00000000 0x00 00000000 0x00 00111000 0x38 0000111 0x07 
    SOP 00000011 0x03 10011100 0x9c 11100111 0xe7 00111000 0x38 0000111 0x07 
    SOP 00000000 0x00 00011100 0x1c 11100000 0xe0 00000000 0x00 0000111 0x07

    I spent some time trying to turn these in to bytes, and then see if i could make sense of any numbers. I was expecting a regular temperature update of 40-50 degrees for 2 different sensors. But nothing really seemed to look like it could be this.

    Then i spotted something odd. the 1 pulses are always in groups of 3, and it appears the 0 pulses are in groups of 2, but its not impossible my chopper has got its timing wrong, it was pretty crude. But it is clear from the scope waveforms this is encoded digital data its not a raw bit stream.

    If we assume then that a 1 is 111 and a 0 is 00 can we chop this up? Looking at above, 4th line we have 0111 this does not fit our rules, may be there is always a 0 following the SOP to provide a break, so this byte is always ignored, if we slice this line using our new rule we get 17 bytes ;-( that is not working. Allowing the rest of the bits to be processed does not help either

    SOP 00-00-00-00-00-00-00-00- 0x00 00-00-00-00-00-00-00-00- 0x00 00-00-00-00-00-00-0
    SOP 00-00-00-00-00-00-00-00- 0x00 00-00-00-00-00-00-00-00- 0x00 00-00-00-00-00-00-0
    SOP 00-00-00-00-00-00-00-00- 0x00 00-00-00-00-00-00-00-00- 0x00 00-00-00-00-00-00-0
    SOP 0111+00-00-00-0111+00-00-00- 0x38 00-00-00-111+00-00-00-0111+ 0xc7 00-00-00-
    SOP 00-00-00-00-00-0111+00-00- 0x1c 00-00-00-00-00-00-00-00- 0x00 00-00-00-00-00-0
    SOP 0111+00-00-00-00-00-00-111+ 0x07 00-111+00-00-00-00-00-00- 0xc0 00-00-00-00-0
    SOP 00-00-00-00-00-0111+00-00- 0x1c 00-0111+00-00-00-0111+00-00- 0x1c 00-00-00-0
    SOP 00-00-00-111+00-111+00-111+ 0x77 00-00-00-00-00-00-111+00- 0x0e 00-00-00-00-0
    SOP 00-00-00-00-00-00-00-00- 0x00 00-00-00-00-00-111+00-00- 0x1c 00-0111+00-00-00-
    SOP 00-00-00-111+00-111+00-111+ 0x77 00-111+00-111+00-00-00-0111+ 0xc7 00-00-00-

    Above i’ve grouped into 2×0 and 3×1 then put a – for a 0 bit and a + for a 1 bit after 8 of them i decode a byte….. there are some problem sequences i can see some 00-0111+ that has broken my assumption of no of zeros and ones in a true bit… this is not the encoding you are looking for….

    Here are my picoscope captured waveforms, these can be loaded in to the picoscope application (free) from picoscope.com

    Heatpump data 1

    Heatpump data 2



  • Simple (c#) message broker using Nanomsg

    I was attempting to do some CanOpen development using CanOpenNode in a 100% windows simulated environment where different apps all pretend to be different nodes and talk to each other via a simulated bus. Now message busses in software are not uncommon things but many are quite heavy going and for the magnitude of the product a think like DBUS was just not needed. Enter Nanomsgnanomsg is a socket library that provides several common communication patterns. It aims to make the networking layer fast, scalable, and easy to use. Implemented in C, it works on a wide range of operating systems with no further dependencies.” It also has support for a BUS type socket so seemed awesome on the surface.

    First problem is the exact implementation of the bus socket is not exactly the same as one would expect when creating an electrical bus. Its simply not possible to just wire all the test apps together and have them talking with out some kind of extra glue in the middle. With nanomsg it is necessary to manually connect every node to every other node to form the bus. Some info is given here but the implementation detail is missed and stumped me for a while.

    If we consider 3 apps all trying to form a bus via IPC then we have to do the following to make the system work :-

    App 1


    App 2


    App 3



    App1 has nothing to connect to so we create a socket with bind, App2 Can connect to app1 but also creates a socket. App3 just binds to app1 and app2’s socket. Nanomsg will take care of the rest and ensure that no matter who broadcasts all nodes receive. Clearly this is only suitable for a fixed implementation where number of nodes and node locations is hard coded. Not really what i had in mind. Enter the message broker. Now i’m not claiming this is efficient or the most elegant way to solve this problem, but its simple and flexible which are the key goals here for the testing i wish to do.

    The idea with the simple message broker is to open a number of sockets (in the example 10) then each app will just connect to the appropriate socket. We can decide which one to connect to as i’m simulating a canopen node bus so each node/app has an ID from 1-127 so that makes a perfect sufix to use on the IPC eg can_id1 can_id2 etc.. If we wanted to be more elegant and remove hardcoded things to the next level we could add an interface to the broker that can be contacted by a new node that allocates a new socket on demand and returns its id. But this is good enough for the my requirements… The broker will listen to each of the multiple sockets then rebroadcast anything it gets to all OTHER sockets. As the sockets are all bi-directional it works as expected for a bus topology

    using System;
    using System.Threading.Tasks;
    using NNanomsg;
    using NNanomsg.Protocols;
    namespace MessageBroker
        class Program
            const int nosockets = 10;
            static BusSocket[] s = new BusSocket[nosockets];
            static NanomsgListener[] l = new NanomsgListener[nosockets];
            static void Main(string[] args)
                Console.WriteLine("Starting message broker");
                for(int x=0;x< nosockets;x++) { s[x] = new BusSocket(); s[x].Bind(string.Format("ipc://can_id{0}",x)); l[x] = new NanomsgListener(); l[x].AddSocket(s[x]); l[x].ReceivedMessage += Listener_ReceivedMessage; } while (true) { Parallel.ForEach(l, (lx) =>
                         lx.Listen(new TimeSpan(0));
            private static void Listener_ReceivedMessage(int socketID)
                //New data on socket
                byte[] payload = s[socketID].ReceiveImmediate();
                //Send new data to everyone other than the sender
                Parallel.ForEach(s, (sx) =>
                    if (sx.SocketID == socketID)
  • Stuck on Pokemon Go loading screen on android [FIXED]

    So since Pokemon Go launched back in the summer there have been many reports of the app getting stuck on the loading screen on android. Many sites have reported the problem to being overloaded login servers and other bugs in the app.

    Many of you will be familiar with the following loading screen.

    Many sites have suggested clearing cache, Restarting, or even reinstalling the app. None of the solutions are working for me.

    I decided to investigate what was going wrong further. I did notice that the web browser was also getting adverts appearing randomly from time to time and clearly this is not correct. Despite some virus checkers giving the tablet the all clear I was very suspicious about a number of apps that I saw running and was not able to stop. One of these apps was called broservice, After managing to force quit and uninstall this App things started to get better for a start the web browser no longer displayed adverts and suddenly Pokémon GO was able to sign in. After some more investigation and removal of some other apps The entire tablet was more responsive and Pokémon GO continue to sign in without issue. 

    My working theory is that the malware was injecting ads directly into the HTTP traffic and this was interfering with Pokémon Go’s login. So as soon as the malware was removed everything returned to normal.

    The worst thing about this problem is that the malware was factory installed on this tablet. The tablet in question was a hipstreet electron and various apps designed to serve adverts and God knows what else were pre-installed and made very difficult to remove.

  • Mains frequency monitoring


    Playing around with measuring the UK mains frequency and got the following results


    where blue is my measurement and yellow is a reference measurement i obtained from a real time tracker on the internet. Pretty good agreement with in 0.01 of a Hz (mostly)

    What i’m really interested in are the variations between blue and yellow, they are not constant and at times look cyclical such as


    What am i seeing there? is that the real time frequency regulation of the mains kicking in and out on 10-20 second duty cycles? as generating capacity is added and removed to regulate the frequency? Or am i detecting local distortions that are offsetting my zero crossing detection and causing error in my frequency graphs?

  • Fake FTDI and esp8266 flashing issues

    So I thought I would try the ESP8266 out at last so got my self a edp8266 module and a USB to 3.3v serial converter.

    After many hours of failing to flash the esp with nodemcu and other firmwares I was on the brink of giving up.The nodemcu flash tool kept letting stuck at anywhere between 1% and 6% no settings made any difference and other flash tools failed in similar ways. I then though to change the serial adapter and happen to have a board with a cp2102 on it so a few soldered on wires later and I had an alternative serial converter. This one worked first time.

    I remembered the whole fake FTDI issue from a while back and started investigating. Firstly my chip had painted on idents which would easily come off with my finger. Real FTDI chips apparently are laser etched. I then downloaded and ran FT_Prog from ftdi’s website and checked the serial no… Turns out it was the most common fake number there was


  • Another stunning success

  • Another one bites the dust

    Don’t think the puck diode thought a lot of the current I stuck though it. Plus it looks badly clamped as conduction clearly started and burned out at a single point from the edge

  • First test of the RaspiHats Isolated IO module


    First tests of the Isolated IO boards from http://raspihats.com/ This is the 6 in/out module featuring 6 relay isolated outputs and 6 opto isolated inputs. 3 Boards are currently being produced. A relay output board, an isolated input board and the mixed board shown here.

    I’ve connected it here to a system under test to simulate a PLC interface for verification. The Raspberry PI is pretending to do the PLC handshake with our equipment to verify its all working to specification.



    Taking to the board from the Raspberry PI is exceeding easy, for a start its a HAT module so supports the auto set up that newer PIs support. The only thing you need to do is enable the PIs I2C from the rasp-config tool (see here https://www.raspberrypi.org/documentation/configuration/raspi-config.md)

    After I2C is enabled ensure you have the following packages installed via apt-get

    sudo apt-get install python-smbus python-pip

    then using pip install the raspihats package

    sudo pip install raspihats

    If you want a video walk through of the setup you can find one below :-


    And here is my python test script simulating some of the PLC. The pupose of this script is to simulate some exteral hardware they we do not have to test the machine against. The particular handshake is defined by our specifications but it shows how easy a test script can be written and the RaspiHats modules used

    from raspihats.i2c_hats import Di6Rly6
    from time import sleep
    import time
    board = Di6Rly6(0x60)
    #force reset
    #board.do_set_state(7,1) # only 6 relays this is forced 0
    while (1):
    print("Waiting for ready signal")
     #wait for ready signal
     print("Not ready")
    print("system is ready")
     print(" .. Moving head to load position and requesting vac")
    board.do_set_state(0,1) # req vac
    #wait for vac on signal
     #do nothing
    print("VAC on requesting a cal cycle")
    board.do_set_state(2,1) # req cal cycle
     #wait for done signal
     #do nothing
    #read passfail
    if board.di_get_state(5)==1:
     print("** ITS A PASS **")
     print("** ITS A FAIL **")
     board.do_set_state(2,0) # clear cal cycle request
    print("robot head in position, signal vac off")
    #move head and turn on robot vac then say our vac is on by deasserting vac req signal
    print("waiting for vac off comfirm")
    #wait for vac off signal
     #do nothing
    print("Cycle complete")
    #loop around and wait for ready signal