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.