Finance

How to Split Transactions in GNUCash Using SQL: Step-by-Step Guide

If you use GNUCash for your personal accounts you may decide that you want to split out a particular group of transactions into a new account or a subaccount in your expenses.

Unfortunately there is no easy way to achieve this in the GUI and so you can either sit there manually clicking and changing them by hand or you can resort to using a bit of SQL to do the work for you.

First, it is likely you are using the default file format which is compressed XML, so the first step is to save a copy in the SQLite format. Don’t delete your old file though, just in case you royally mess things up.

Next step, download a copy of DB Browser for SQLite and install this on your machine, then open the file you just saved.

Navigate to the ‘Browse Data’ tab and look at the ‘accounts’ table, here you will see a list of all your accounts. You want to find the ‘guid’ of the account where your transactions currently live and also the one you want to transfer them to.

Next you want to go to the ‘Execute SQL’ tab and use the query below as an example. This will move any transactions in the old account with a description containing ‘Student Loan Payment’ into the new account.

You may want to test just the SELECT portion of this first before the update to ensure it returns all the items you expect and only those items.

UPDATE splits
SET account_guid = 'GUID OF NEW ACCOUNT'
WHERE 
    tx_guid IN (
        SELECT 
            t.guid
        FROM
            transactions AS t
        JOIN
            splits ON t.guid = splits.tx_guid
        WHERE 
            t.description LIKE '%Student Loan Payment%'
            AND splits.account_guid = 'GUID OF OLD ACCOUNT' 
    );
AND
	account_guid = 'GUID OF OLD ACCOUNT'
Computing, Programming

NuKeeper YAML Example

Having rolled out the use of NuKeeper across multiple repos in Azure DevOps I thought I would share an example YAML build definition to save others similar headaches to what I have had, also a few handy tips on what I learned the hard way.

The main issue I had was ensuring the credentials were correctly passed through, you need to set the environment variable ‘NuKeeper_azure_devops_token‘ with the value $(System.AccessToken).

Another issue I had was that I had set the checkout to do a shallow clone / fetch to save time on bigger repos, this did not play nicely with NuKeeper and I was getting an error ‘NuKeeperException : Git Cannot checkout branch’

trigger: none # you don't likely want this firing every time someone pushes a branch

schedules:
- cron: 0 4 * * 1,2,3,4,5 # 4am Mon-Fri
  always: true # you want this to fire regardless of changes to this repo
  branches:
    include:
    - refs/heads/master
    
pool:
  vmImage: windows-latest

steps:
- checkout: self
  persistCredentials: True
- task: NuKeeper@0
  displayName: NuKeeper
  env:
    NuKeeper_azure_devops_token: $(System.AccessToken) # this is important!
  inputs:
    arguments: --consolidate --maxpackageupdates 10 --change minor --age 0 --include ^CompanyNugetPrefix
    feed: /87ed4aa7-4a9f-4ea0-89f5-a9782a2067ae #GUID of your products nuget feed
Programming

SSL certificate problem: unable to get local issuer certificate

If you are connecting to an on premises TFS instance and you are using Git in Visual Studio 2017 then you may get the error “SSL certificate problem: unable to get local issuer certificate”.

To get around this you want the inbuilt Git to use WinSSL / the builtin Windows Security Store. You can do this in the following way:

  1. Close all instances of Visual Studio
  2. Open a command prompt as administrator
  3. cd to the installation directory for VS2017’s Git – C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\CommonExtensions\Microsoft\TeamFoundation\Team Explorer\Git\mingw32\bin
  4. Run the command: git config --system http.sslbackend schannel
  5. Reopen Visual Studio

This should modify the following file (by default)

C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\CommonExtensions\Microsoft\TeamFoundation\Team Explorer\Git\mingw32\etc\gitconfig

Health

Blood test detects melanoma skin cancer while it’s easily treatable

Recently read an article on New Scientist about trails of a new blood test that detects melanoma skin cancer while it’s still in the early stages.

As someone who has had skin cancer I think this a great discovery and hopefully will be able to detect these problems earlier so that people do not have the same issue that I did with regards to being misdiagnosed. Originally the first consultant I saw said that my mole that had changed was nothing to worry about, it wasn’t until 9 months later after it had changed more and I had forced my GP to refer me again was it finally diagnosed as melanoma and dealt with.

I would hope in the future that someone in the same situation could take this blood test to see if the anti-bodies exist and have the problem dealt with much earlier and avoid misdiagnosis in early stages of the disease.

Tesla

Tesla Road Trip to Scotland

Got back home a couple hours ago after completing just over 1200 miles in the last 10 days driving up to Scotland and touring around, odometer started at a mere 500 miles and now at over 1750!

Over this journey I met several Tesla owners from the Tesla Owners Group UK and learned a lot more about my car! 

First thing is that charging really isn’t a chore, with a 300+ mile drive up to Dundee the cars range far exceeded our bladders and our ability to not want a break. Even when doing a quick pit stop for a pee and to grab a drink you can add a lot of miles on a Super Charger!

The biggest issue we had was rain, the wipers suck and I will be looking to replace them shortly. I guess they don’t get rain in California? Driving in the dark with the rain lashing down on narrow windy A roads is not at all fun as your visibility is terrible, very much missed my Skoda at that point!

Once the weather cleared up boy is the Tesla fun to drive on windy Scottish roads! We had a lovely drive from Dundee to the Glenfiddich distillery. The fun continued for the rest of our holiday and I absolutely love this car aside from when the roads are very narrow and you fear losing a wing mirror to a lorry!

TACC is my favourite thing I think, it makes journeys so much easier. Not driven other cars with this so no idea how it compares to other brands, but I find it almost perfect aside from the handful of occasions it has got confused whilst passing a lorry on a motorway and braked slightly.

My AP2 auto-pilot was great on motorways and dual carriageways. It was a bit of a mixed bag on single laned roads though, it impressed me a few times on the A9 by handling the road becoming a dual carriageway and then merging again later. But a few times computer just said no and dumped me back in control, I think I am happy to fully trust it on major roads, minor roads it’s still not quite there yet!

Driving back home today was interesting, we did a quick pit stop at Abington and then a 90% charge at Gretna Green whilst we ate food. Whilst the sat nav wanted us to charge again at Leeds it eventually changed it’s mind to Barnsley, and then we just ignored it and arrived home with 18% charge. It seems my 75D is far more efficient than it seems to estimate.

All in all we had a great first road trip in the Tesla, and we’ve learned a lot more about it and charging. Attached an assortment of obligatory charging photos from our trip 

As a Tesla owner, I can do you a favour when you get a Tesla. The only way to get free Supercharging is to use this link: http://ts.la/kris3228

Tesla

New Car Detail @ Reflectology

Picked up my Tesla Model S from Russell at Reflectology today, the paintwork is at a whole new level now compared from when I collected it from the showroom. The amount of ‘depth’ in the paintwork is simply astounding, you almost get vertigo staring into the bonnet!

Four days of hard work have gone into this, correcting every minor blemish on the car inside and out, multiple layers of ceramic coating on the paintwork, each wheel taken off and also coated multiple times inside and out and all the interior also treated.

The ceramic coating applied on the final layer was Siramik Diamas Professionali, this should keep the car easy to clean and dreamy to look at for many many years (last car was good after 3+ years)

Would happily recommend Russell Briggs to anyone near J30 of the M1, so Nottingham / Derby / South Yorkshire.

As a Tesla owner, I can do you a favour when you get a Tesla. The only way to get free Supercharging is to use this link: http://ts.la/kris3228

Tesla

Tesla CHAdeMO adaptor

Yesterday I had my Tesla CHAdeMO adaptor delivered, this will be our lifeline when we go on holiday to Scotland in a few weeks time as the furthest north Tesla Super Charger is in Dundee.

This adaptor is a serious bit of kit weighing in at about 2.3kg and costing £358. On one end you have a female CHAdeMO socket and the other you have a male type 2 connector which is what the Tesla takes.

The adaptor can handle up to 125 amps of DC voltage which gives a maximum of 43.75kW for 350v packs such as my 75kWh, and 50kW for 400v packs such as the 100kWh. I believe these are more theoretical limits though and you usually obtain 35-40kW from most UK charging stations.

Whilst it evidently is built to handle some serious power I believe that a decent amount of the cost is to cover licencing fees to the CHAdeMO Association along with covering the R&D costs of the communication layer.

Effectively the adaptor is talking to the car to understand it’s voltage, state of charge (SoC) and what power input it can handle based on this and it’s temperature. It then takes this data and translates it into a standard the CHAdeMO charger understands. So it’s a little more than a couple of meaty adaptors connected with a big thick cable.

As a Tesla owner, I can do you a favour when you get a Tesla. The only way to get free Supercharging is to use this link: http://ts.la/kris3228

Tesla

Tesla arrived!

Finally collected my Tesla Model S from the new Tesla Leeds service centre! Arriving quite early on a bank holiday Monday as there was very little traffic we sat outside patiently waiting for the store to open. After waiting 3 months since ordering the car what’s another 30 minutes?

The wait had certainly been interesting, spending a lot of my time on the Tesla Owners Group UK Facebook group (TOGUK) learning more about the car, the community and tips and tricks.

Another thing that happened was seeing the constant changes that Tesla do to their cars. Unlike normal manufacturers that usually only make changes once a year, Tesla are constantly making changes.

These changes can be mildly annoying once you’ve placed your order, but when you find out the car you ordered will now do 0-60mph in 4.2s instead of 5.2s it is a very pleasant surprise, especially when it’s at no extra cost.

Then they bundle some of the optional extras you’ve added such as Premium Sound and Sub-Zero Pack into the Premium Upgrades Pack and also reduced the base price of the car. You read about this on the TOGUK Facebook group and on TMC and you’re very annoyed that your car is now about 4 grand cheaper… then you find out they’ve applied a £4,800 discount to your order so it’s all good and gravy!

Eventually all this time fades away and you’re stood outside the service centre talking to Tris who will be doing your handover, patiently waiting for the store manager to arrive and unlock the doors.

Eventually a few minutes later you’re enjoying a cuppa and che

cking over all the documents before being taken in to the showroom and your lovely Tesla is awaiting you. As I’d been quite particular about the detailing done on my car, as I’m handing it over to Russell of Reflectology, it wasn’t covered for a grand reveal as they normally do.

My partner and I then proceed to check the car over inside and out for any issues using Will Fealey’s checklist and a few things my detailer mentioned. We picked up a few minor things but nothing that wouldn’t be rectified by Russell. Then Tris spent time walking us through the vast amount of options on that lovely big screen, how all the controls work and answering all our questions.

About 30 minutes later and I’m sat in the driver’s seat making some final adjustments before setting off back home in my brand new Tesla. Flick the drive mode to D, light press on the accelerator and I’m silently moving off down the road. Even though I took one out for a test drive 3 months ago this still feels weird and a little eerie.

Joining onto a slip-road I put my foot down a little to get up to motorway speed, but that noise of an engine revving up to it’s power-band is not there, just a silent thrust of power and I’m doing 70mph with ease.

Following my girlfriends car down the M1 motorway back home it’s a lovely cruising vehicle, so very relaxed and poised, I can certainly see how people clock up so many extra miles in a Tesla. After about 30 miles the Traffic Aware Cruise Control has calibrated and I flick it on. Now the car is keeping a very steady safe distance behind the vehicle in front and suddenly driving just become a million times better, this feature is going to make my commute immensely more pleasurable!

As I approach home I have come to a complete stop at a roundabout, I know on the other side is a dual carriageway, a 70mph limit, as I set off I slowly accelerate around and then boot it down the road.

OH MY GOD!

It’s at exactly this moment I understand what the “Tesla grin” is, that immense joy of accelerating at such a pace in near silence. I have a grin from ear to ear and worry for a second whether my face will ever go back to normal, this car is phenomenal, yes it cost a fortune, yes I will worry about parking it, but my lord is it fantastic to drive whether it’s cruising on a motorway or shooting off like a stealthy rocket.

As a Tesla owner, I can do you a favour when you get a Tesla. The only way to get free Supercharging is to use this link: http://ts.la/kris3228

Politics

Banning diesel and petrol cars from 2040

In 1994 your phone would have looked like this.

If someone had told you that in 23 years time your phone would:
• Be thinner than a pencil
• Be 1000x more powerful than your 486 computer
• Able to instantly play music from over a million CD’s
• Play videos at 24x the resolution of a VHS tape
• Allow you access to more data than the encyclopaedia Britannica at the touch of a button

You would have said it’s not possible, that things don’t move that quickly?

Why are some people thinking it’s so crazy we’ll all be driving electric cars by 2040??

Tesla

Ordered a Tesla Model S

I have been considering switching to an electric car for quite some time but been waiting on more news on upcoming models, having seen more finalized information on the Tesla Model 3 I realized it’s not likely to work for my needs and no other companies really have anything that will suit our use.

I’ve debated a Tesla Model S quite a bit since my skin cancer diagnosis last year and with my 1 year anniversary coming up and various improvements to my financial situation I decided to bite the bullet and order a Tesla Model S 75D in Deep Blue.

Car is expected to arrive in late August – September so a long wait ahead of me so plenty of time to read up more!