Converting MoneyDashboard's export file to a CSV - for Firefly III and others

As I mentioned last week, MoneyDashboard is shutting down. They are good enough to provide a JSON export of all your previous transactions.

It is full of entries like this:

 JSON{

    "Account": "My Mastercard",

    "Date": "2020-02-24T00:00:00Z",

    "CurrentDescription": null,

    "OriginalDescription": "SUMUP *Pizza palace, London, W1",

    "Amount": -12.34,

    "L1Tag": "Eating Out",

    "L2Tag": "Pizza",

    "L3Tag": ""

},

{

    "Account": "American Express",

    "Date": "2019-01-11T00:00:00Z",

    "CurrentDescription": null,

    "OriginalDescription": "Work Canteen,Norwich",

    "Amount": -5,

    "L1Tag": "Lunch",

    "L2Tag": "",

    "L3Tag": ""

}

Let's write a quick bit of Python to turn that into CSV. This will turn the above into two separate files.

My Mastercard.csv:

 CSVData,       Description,                       Destination,  Amount

2020-02-24, "SUMUP *Pizza palace, London, W1", Pizza palace, -12.34

And American Express.csv:

 CSVData,       Description,            Destination,  Amount

2019-01-11, "Work Canteen,Norwich", Work Canteen, -5

I didn't make much use of MoneyDashboard's tagging, so I've ignored them. The destination (which is the name of "opposing bank" in Firefly III speak) ignores the payment processor like SUMUP or PAYPAL and anything after the first comma.

It also sorts the CSV into date order. It's not very efficient, but you'll only run it once.

 Python 3import json

import csv

import os

from datetime import datetime



#   Read the file

json_file = open( "md.json" )

data = json.load( json_file )

transactions = data["Transactions"]



#   Loop through the transactions

for transaction in transactions:

    #   Get the filename

    filename = transaction["Account"]



    #   Format the date

    date = datetime.strptime(transaction["Date"], "%Y-%m-%dT%H:%M:%SZ")

    formatted_date = date.strftime("%Y-%m-%d")



    #   The description

    description = transaction["OriginalDescription"]



    #   The destination is everything after the first " *" (if it exists) and before the first comma

    #   For example: "SUM *Pizza Place, London" becomes "Pizza Place"

    destination = description.split(',')[0]

    if " *" in destination:

        destination = destination.split(" *")[1]



    #   Monetary amount

    amount = transaction["Amount"]



    #   Create the file if it doesn't exist

    if not os.path.exists(f'{filename}.csv'):

        with open(f'{filename}.csv', mode='w', newline='') as file:

            writer = csv.writer(file)

            writer.writerow(["Date", "Description", "Destination", "Amount"])



    #   Read the file and split the header from the existing data

    with open(f'{filename}.csv', mode='r', newline='') as file:

        reader = csv.reader(file)

        existing_data = list(reader)

        header = existing_data[0]

        data_rows = existing_data[1:]



    data_rows.append( [formatted_date, description, destination, amount] )



    #   Sort the data by the first column (string)

    sorted_data = sorted(data_rows, key=lambda x: x[0])



    #   Save the file back again

    with open(f'{filename}.csv', mode='w', newline='') as file:

        writer = csv.writer(file)

        writer.writerows([header] + sorted_data)

Run that against your MoneyDashboard export and you can then import the CSV files into MoneyDashboard, GNUCash, or anything else.