Exporting GnuCash Data to PowerBi

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!

Preventing Misuse of Data Between Tenants and Landlords

tenants and landlordsAll companies must abide by the data protection act, and as such have strict rules to follow to ensure that they do not leak sensitive client or otherwise information which could be deemed insecure or negligent.

For my example, we will use a property lettings company to illustrate where policy may be implemented.

External Policy Implications (Data Protection Act)

A company that lets, sells and rents houses will have many types of information that they will keep in their database, for example;

Their landlord customers data

  • Personal Details (Name, Bank details).
  • Address of the property they live at.
  • Address of the property they are selling or letting.
  • Contact details of for maintenance contractors.
  • Letting Agreement.
  • Tenancy Agreement and permissions.

Their lettings customers data

  • Customer details (Name, Bank details).
  • Address of the property.
  • Conditions of their contract.
  • How long they have lived at the property.

These are just two sets of multiple tables a letting company may keep about agreements between tenants and landlords. The data protection should first prevent the following;

  • Information about the landlord being given to the tenant without the landlord’s permission, if the landlord has requested that, for example, their address is not shared with tenants.
  • Tenants accessing the database to view other tenant information.
  • The public accessing the database.
  • The public being able to change the database.
  • The data is not kept for longer than needed.
  • The data is not backed up.
  • Proper access control restricts access to the information.
  • Data is not shared with other parties. (see Internal Policy Implications for exceptions)
  • The data is obtained lawfully. Stealing or asking for information about a customer’s data should not be tolerated without their consent.

These are just a few examples of the principles of the data protection act that prevent the data being used unlawfully.

In addition it should also not be the responsibility of the landlord to hold information that relates to the property lettings company, This falls under keeping data secure as it could be argued that data that is not held by the company but is crucial to the agreement is the responsibility of the letting company, as if there was to be a dispute between the tenant and the landlord, it would be hard to retain the information if the landlord has the only copy.

Additionally, data that is old or outdated should be deleted or updated, if a lettings company was to retain information of past customers, if they were to face a breach, they could worsen the damage if the data leaked was harmful to a past customer.

Internal Policy Implications

In addition to the data protection act preventing the direct breach of client data, the computer misuse act should prevent the unauthorised access to systems that are publicly available as it is necessary that the property lettings company take the necessary precautions to ensure that the data is kept secure from anyone except those are permitted to see it through some form of access control.

Landlords are however allowed to pass the names of clients on to third parties so long as it is to ensure that proper billing addresses and such are directed to the client accordingly.

It is not appropriate to provide a landlord with a tenant’s references without first contacting the tenant that they (the lettings company) wish to do so.

Landlords cannot disclose to the public tenants who are in arrears as this is information about individuals, this can only be provided to tenants or anyone who is responsible legally for the tenant.

In general, landlords should make clear to tenants when they sign the tenancy when and how their information will be given out. Information about a tenant should be considered very personal and in cases where the data is needed to be disclosed in an emergency, it should be given out only with proper consideration for the law.

Critical Path Analysis

Critical Path Analysis has ties with corporate strategy and human resources. It enables a business to gain a competitive advantage by effectively planning their time to pursue a goal. This can be especially important when they are trying to be first to market or when the process requires a lot of routine steps. It is also relevant in the computing sector and neural networks.

Detailing the steps involved can help to create a better picture of the complexity of a project. To do this, Critical Path Analysis Diagrams are used to break down the project into stages.

Critical Path Nodes

Each stage is made up of nodes, and each node is made of three numbers, the nodes are the circles in the image above (there are two) and have a path between them, Each path has the project that will be completed on the top and the time on the bottom.

Each node has a number on the left half, the earliest start time on the top and the latest finishing time on the bottom. Some diagrams also denote the direction of the path, however, it usually starts on the left, and ends on the right.

Some Critical Path Analysis Diagrams can become complex and take a long time to complete, some stages may also require few or many steps to complete, it is for this reason that Critical Paths are also shown on a diagram.

Critical Path with Four Nodes

In the diagram above, there are four nodes and therefore more work to be completed, however having a top and bottom section has so far proven redundant. It is only when there is an alternate path that these nodes become useful.

Critical Path Analysis Network with a denoted Critical Path

We can now see that having the latest finish time has meant that the project now cannot be finished until the ninth day as the task ‘Air Transport’ Requires a longer time period than the other tasks. This is why the fourth node now reads ‘9’ on the latest finish time. Additionally, there is now dashes along the Critical Path as this task must be completed in order for the project to remain on time.

Critical Path Advantages

Finding the critical path of a project allows a business too;

  • Conduct better contingency planning as they can see clearly where projects have the potential to get held up in the process and aren’t slowed down due to failures in the system.
  • Optimize workflow for employees as they will be able to better manage the time they need to complete a task, should a task have a long lead time, the task could be delayed without affecting the progress of the project, for example, a node having a quicker ‘earliest start time’ than other tasks.
  • Allows a job to be completed in the shortest amount of time possible.
  • It can help to reduce risk on projects that have complex time dependencies.

Critical Path Disadvantages

  • However, it does not account for the likelihood of failure of a task where it was poorly planned from the beginning.
  • It does not account for the fact that a project may eventually have stages that need changing as time progresses or situations change.
  • It depends on the accuracy of the projects predicted time-scale.
  • It may not account for other factors like cost or trade-off.
  • Resources may not be as flexible in the long term as previously planned.