Category: Projects

Tracking My Position all the Time

Aidan Crane

I have a long standing interest in monitoring my daily doings, I previously thought about putting a GPS tracker in my car, a good idea for both tracking my position and also checking to see if it has gone walkies (is stolen). I decided against doing this a while ago due to exams and lack of time to set the whole thing up. But I did decide to have a crack at this idea later with my phone.

The way I chose to go about this power hungry task was to use an app like IFTTT to record my position to a spreadsheet. But then I heard about owntracks, a purpose-built app for tracking your position. After setting up the owntracks server, MQTT and owntracks recorder. I now have a pretty good way of determining where I am at one time or another and have been using it for about 6 months.

My phone is a OnePlus 3 and as I said the app is fairly power hungry. Currently owntracks uses ~39% of my battery’s capacity based on 1 day 1 hour uptime. Although this seems fairly high I have accepted it as a fair compromise untill I can finish another GPS project I have in the works.

Here’s my experience of such a setup so far,

  • Battery usage increased significantly to a point where some users may not find it acceptable.
  • I had to turn off battery optimization and advanced battery optimization or else the app would stop.
  • The app does not always capture my location all of the time, sometimes it may be a few hours before it reports a significant change despite being in move monitoring mode or significant changes mode.
  • The performance and recording of positions in the app isn’t as good if the app does not have internet access all the time, so if data is switched off in my phone, the recording of the position isn’t as good as if it were on all the time, sometimes there can be large gaps where there was no internet access where it drops out.
  • My use case was for later analysis of the data, not realtime.
  • Owntracks recorder is not very featured but it does the job.
Oneplus3 Battery usage with owntracks

Exporting GnuCash Data to PowerBi

Aidan Crane

Some things are better if you do them yourself. I mainly did this project to keep a running ledger of the changes that I would need to keep track of the account balance data.

GnuCash is great, but when I export my accounts data the CSV file isn’t easily translated with power-query automatically. I decided that because I need to keep track of my transactions, this problem was best suited with a little program to calculate my net inflows and outflows using a python program, I also decided halfway through the project that I wanted to hook it up to my graphing backend, a silly idea – but a fun one to see my spends go up and down. I decided that I’d publish it here so that in the future I would find it a lot easier.

Traditionally if you wanted to export GnuCash data from CSV to PowerBi, you’d be better off using their inbuilt power query, however, I wanted to implement a ledger system, something that I don’t think can be accomplished directly in PowerBi without some scripting, and in the future I want to be able to change platforms if I need to because I no longer have a licence for PowerBi or want to use something else like excel or free equivalents like Google Sheets. I reckon that if done properly Google Scripts could make everything run automagically from an upload, but I don’t have my reports ready yet for that to happen.

# Extracted from here https://github.com/aidancrane/GnuCash-CSV2CSV-for-PowerBi/blob/master/convert_to_powerBI.py
import csv
from re import sub
from decimal import Decimal
import time
# If we only want the transactions values, set this to true and they will print to console, they do not save to accounts_out.csv!
numbersOnly = False
# Anyone thats not me will want this to be false, this is used to show the transaction data on a live graph I use for scratching around with.
smoothieChartEmulation = False
sessionCookie = "39494goodluckguessingthispartlololol213232expiresanyway"
# Leave this here so that Notepad++ and Atom auto-suggest it.
# Date,Account Name,Number,Description,Notes,Memo,Category,Type,Action,Reconcile,To With Sym,From With Sym,To Num.,From Num.,To Rate/Price,From Rate/Price
if (smoothieChartEmulation):
import requests
# Negative Numbers are bracketed when exported from GNUCash so we need to fix that for the float data type.
def convert_if_negative(number):
returnNumber = str(number)
if ("," in returnNumber):
if ("(" in returnNumber):
returnNumber = returnNumber[1:]
returnNumber = returnNumber[:-1]
returnNumber = returnNumber.replace(",", "")
returnNumber = 0 - round(float(returnNumber), 2)
returnNumber = str(returnNumber).replace(",", "")
if ("(" in returnNumber):
returnNumber = Decimal(sub(r'[^\d.]', '', returnNumber))
return (0 - round(float(returnNumber), 2))
return returnNumber
# open accounts.cvs, our exported file.
with open("accounts.csv", "r") as csvIn:
reader = csv.DictReader(csvIn)
entries = []
runningTotal = float(0)
# Save
for row in reader:
if (row["Account Name"] == ""):
pass
else:
runningTotal = runningTotal + float(convert_if_negative(row["To Num."]))
if (numbersOnly):
print(str(round(runningTotal, 2)))
else:
if (smoothieChartEmulation):
payload = {'random_graph': runningTotal}
r = requests.get('https://dash.infinityflame.co.uk/dash/flex.php', params=payload, cookies={'PHPSESSID': sessionCookie})
print(str(convert_if_negative(row["To Num."])) + " Description: "+ row["Description"] + " Account Balance: " + str(round(runningTotal, 2)))
entries.append([row["Date"],row["Description"],row["Category"],str(convert_if_negative(row["To Num."])),str(round(runningTotal, 2))])
# Save what we care about to our new csv for power BI   
with open('accounts_out.csv', mode='w', newline='') as csvOut:
titles = ["Date","Description","Destination","Transaction","Account Balance"]
writer = csv.writer(csvOut, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
writer.writerow(titles)
for transaction in entries:
writer.writerow(transaction)

I used python because it’s quick to debug in Atom and I know it well. The way this snippet is used is to export an account from GnuCash, then use this program to output the final CSV to be taken in by PowerBi, I’ve chained these steps together. The final result is stored in accounts_out.csv.

Thanks!

Opening up my Tile Mate

Aidan Crane

Bluetooth trackers are a great addition to anyone’s cluttered key chain, Last year I got a Tile Mate as a present. A nifty little key-chain Bluetooth connected sounder for when I loose my keys, or when I loose my phone.

So far given one years use I have maybe used it seriously to find my phone 2 or 3 times, it works very well for it. Anyway now that I’ve had a years use, the Tile app decided they wanted more money as the battery was getting low. Interestingly enough in the newest versions of the Tile Mate the battery is replaceable, however in this version (replaceable or not) the method to open the case is rather destructive.

The Tile Mate Main board, front and back case housing.

Forgive the image, it was rather late at night, As you can see there was a replaceable CR2025, so changing the battery was as simple as replacing it with a battery I had spare. I wonder what ’62’ means?

I couldn’t get the data-matrix to scan on my phone app unfortunately, but the board is very pretty and its clear a lot of thought has gone into keeping the size small. I like the little Pogo pins on the right for the piezo on the lid and the little pcb antenna. Also featured, the timing crystal oscillator, microprocessor and what looks like a version number for the board.

Anyway, then I put it all back together nice and snug inside its now fairly tarnished case and used some 5-second-fix glue to glue it back together and jobs-a-gooden, almost. It seems like the message on the app still persists.

Anyways, I’m still happy with it, it’s served its time and I think I’ve broken it a little too much to replace it again. In the future perhaps with the latest version, tile could offer some sort of slot so that the battery can be replaced a little less destructively (or maybe I did it wrong, oops). Nevertheless I don’t advise doing it yourself. TTFN, Aidan.

Cya later, Mate.

Gigabit and More on my Home Network

Aidan Crane

In a recent pursuit to have the fastest speeds possible at home, I have started to upgrade and replace a large section of my home network.

Ensuring That My Network Runs Fast at its Core

Barring the routing technologies at home the most crucial part of my network is my server, an old Dell Optiplex 780 salvaged from an office clear-out it now spends its days hosting my media, files and applications. One of the first things I did was replace all of the cabling around my network. Most being CAT5 the fastest speeds possible were only 100Mbps, this was a noticeable bottleneck when I checked iPerf3 as the switch its connected to, the router and my PC were all gigabit connected and clearly an oversight on my part. Replacing the cable from it to the switch instantly jumped the speed to gigabit. Yay!

Getting Rid of Old Hardware

Another decision I made was to remove some of my old hardware that was no longer up to the task of Gigabit+ speed. That for me meant swapping out a dumb switch capable of 100Mbps max for a new 8 port gigabit one, oddly I bought the 100Mbps one for £10 and sold it for £14 on Ebay so I guess theres money in old switches.

I then replaced its cables too and viola, 1 Gbps throughput on the whole wired network!

What does the future hold?

After looking at transfer speeds between my server and PC, I think the next step is to replace the Optiplex and my PC, as they are now at the point where their age is starting to be a performance problem, although they remain somewhat performant at their daily duties they are both starting to age poorly with the increasing demand from myself for hosting my projects.

I also want to try SFTP+ but I don’t yet have the SSDs or Hard-drives to necessitate it.

My Meraki Home Network

Aidan Crane
Cisco Meraki MR18
Cisco Meraki MR18

If you have ever wanted to use WiFi reliably in your own home, most homelabbers and computer enthusiasts will eventually learn that using consumer equipment to achieve fast speeds and low ping can be a route that many take in vain, whilst mesh networking appliances look promising for home applications, the ever vast expanding market of power-line networking adapters and WiFi range extenders have lead consumers astray and often leave them unhappy with their network performance due to patchy and inconsistent behavior caused by devices looking for the ‘strongest’ signal despite it not necessarily being the fastest or most optimal.

Having an ISP issued router has never put me off fiddling before, having being issued a Netgear WNR3500L V2 it wasn’t long before I decided to flash Tomato on it and leave Netgear Genie hopefully forever.

Having moved on with my life I quickly became unhappy with the performance of the WiFi and decided it needed sprucing up a little bit, running two routers on two sides of the house were becoming tiresome due to patchiness and being connected to one side of the house while at the other requiring manual adjustment. It was time to buy something enterprise to set-and-forget. Alas enterprise comes with a hefty price tag.

Rather than going mainstream and buying something like Ubiquiti or MikroTiK, popular routers with the home lab community. I decided to take a different approach to save a buck. Having flashed firmware on many devices in the past, such as Arduino, ESP8266s or even other routers I decided to get my hands on the enterprise CISCO Meraki MR18, a subscription model router with a hefty price tag at the current time of £308.38 for a three-year license. Would I be paying that? Absolutely not.

I picked one up off eBay for a fairly high market price of £17.99 which came with a wall bracket and no plug or license. Why so cheap compared to the £308.38 price tag? Well, it turns out Meraki are giving them away free to anyone who sits through their webinar, and once the license runs out it’s my understanding that they essentially become paperweights. Once it arrived I fired it up just to see what it used to be called and see if I could get a hit on WiGLE’s database to see where this thing came from, unfortunately, 0 hits means my best guess is that it came from somewhere around Aylesbury, UK. The location of the Ebay E-Recycler the SSID was generic “Guest Access” and “Staff Internet”.

Once I cleaned up the router it looked brand new, the power cable I got from my drawer of many cables and began to study the thing, the brushed aluminum backing had the screws hidden behind rubber feet, an easy adversary I had the thing open in no time.

Meraki MR18 opened
Big RF Shields 🙂

I particularly like the bendy lightpipe for the RGB leds.

Bendy Light Pipe

The UART pins were neatly placed and populated with pins at the top of the AP and easy to flash with.

I decided to opt for flashing OpenWRT on to it, basically because at the time they were the only ones supporting this particular device and it would only really be acting as an Access Point, with my Tomato router doing all the hard work.

Thanks for reading. Enjoy this picture of my neighbors cat.

Tabitha the cat

Transitioning from Google Map Editor to OpenStreetMap

Aidan Crane

After using Google Maps for quite a while, I enjoyed areas that had buildings populated on the map, Google does a very good job of this, however since Google Map Maker closed, I felt somewhat disenfranchised with their mapping system, It became a lot harder to edit their maps and the experience is just not as good as Map Maker was.

screenshot showing local shops and residential estate on google maps
A Google Maps screen capture of my neighborhood.

Then one day I discovered OpenStreetMaps and the simplicity of their map editor filled the hole in my heart that Map Maker left. I found that Open Street Maps iD editor was both friendly to use and better than Map Maker, the iD editor had a much more intuitive interface and the wiki made the transition generally easy and enjoyable.

screenshot showing local shops and residential estate on open street map
An Open Street Maps screen capture of my neighborhood.

As with any change, there was a little bit of a learning curve but the wiki was informative and allowed me to transition from my amateur interest in the topic of cartography into a full-blown contributor to the eco-system. I also have started using the JOSM editor a little and have found that its tools have made contributing much easier for bulk operations and mainly use the iD editor for tidy-up or casual map corrections or contributions.

Why Open Street Map?

Contributing to a closed system like Google maps was never really a problem for me using my local knowledge to correct open times and add photos to locations, but once I discovered Open Street Maps, I sort of feel like there’s no going back. There are a lot of reasons I like Open Street Map,

  • Its Open, the maps can be used in any application, the license only requires that OSM is credited when your application is public. (Copyleft Attribution-Share-Alike)
  • Its used in a lot of applications that I use already, updating the map on OSM will improve those applications too. 🙂
  • Editing polygons seem to be a lot easier than Google Map Editor.
  • Using an Open system would be preferable over something that in the future could potentially become a closed source or paid for.

I intend to update my local area, as Open Street Maps relies on community contributions it is apparent that it has not been updated in a while. Watch this space.

Woodmansy, Beverley UK Weather Current Temperature and Humidity

Aidan Crane

Hey Thanks for checking out my project, or discovering it organically!

Whats the weather?

This page shows the output of my outdoor box DHT22 Temperature sensor, powered by two 100w solar panels it can sometimes run out of power, especially late at night on miserable days. The data is updated in 15-minute intervals based on three samples. The data is monitored and transmitted from an esp8266 to my web-server, which is then logged in a google spreadsheet and then published here, only the last 500 entries are kept in the chart, which makes the whole chart 15 hours long.

The data on this chart can be better viewed here -> Link

The data on this chart can be better viewed here -> Link

If you would like historical data, please contact me.

The Cheap Raspberry Pi Security Camera

Aidan Crane

One of the great things about the Raspberry Pi is the community that works to create really great projects. I have setup a Raspberry Pi B looking out my windows. It faces the front door so can see anyone coming down the street and toward the door. I had a couple cheap $2 webcams lying around so I set them up looking out the windows. The total cost of the entire setup is about $10, minus the cost of the Pi itself, I also think that the Pi is a little underpowered for the task as occasionally the thing will stop working after several weeks, the camera still records the video, but the web-interface has to be reloaded in order to get the thing working again.

Capture

IMG_20160418_181953

Overall, I’d say that this project was ineffective for its purpose because unfortunately, the cameras would not record movement accurately enough, and sometimes would record several hours of minimal movement. An IP camera would likely be more cost-effective and better suited for the task, the Pi I used was simply underpowered to monitor two webcams and crashed after about 2 weeks of working continuously. Viewing the files showed that although it captured movement and video, some were corrupt, glitch or only captured about 3 usable frames. It did, however, show a good live view of what was going on outside the house, with about a 4-second delay.

Overall I would say that a Raspberry Pi as a webcam security camera on the cheap is not a good idea, the main contributing factor being that it was not able to keep the program running and often would save garbage to the SD card. If I were to do this again I would not use two for definite as it simply did not work effectively enough to actually increase security, It would often record trees moving for hours and sometimes one camera would freeze up entirely.