(*
Dear kMyMoney developers,
I came across KMyMoney while looking for a replacement for the recently
deceased QuickBooks Desktop.
Having gone to the trouble of converting my accounts from Quickbooks to
GnuCash, I was a little disappointed
that the “open Gnucash XML file” feature didn’t seem to work. I offer you a
crude standalone converter which someone
might be able to work up into a general purpose importer. Comments welcome. At
the moment I don’t have non UK
currencies to work with. The extrapolation should be a simple exercise.
Regards,
Jonathan
*)
(* License: GNU General Public Licence Version 2 *)
(* AUTHOR: Dr Jonathan Kimmitt *)
(* can be compiled with "ocamlfind ocamlopt -linkpkg -package unix,xml-light -g
translate.ml -o mymoney" *)
open Xml
let unm = ref None
let cnvtim (tm:Unix.tm) = Printf.sprintf "%.2d-%.2d-%.2d" (tm.tm_year+1900)
(tm.tm_mon+1) tm.tm_mday
let cnv t = Scanf.sscanf t "%d-%d-%d %d:%d:%d" (fun yr mn dy hr min sec ->
let
x={Unix.tm_year=yr-1900;tm_mon=mn-1;tm_mday=dy;tm_hour=hr;tm_min=mn;tm_sec=sec;tm_wday=0;tm_yday=0;tm_isdst=false}
in
let _, tm = Unix.mktime x in cnvtim tm)
type item = {
mutable tim_posted: string;
mutable tim_entered: string;
mutable num: string;
mutable desc: string;
mutable tran_id: string;
mutable split_id: string;
mutable recon: string;
mutable recon_date: string;
mutable value: string;
mutable quantity: string;
mutable split_guid: string;
mutable action: string;
mutable memo: string;
}
let empty_item () =
{tim_posted=""; tim_entered=""; num=""; desc=""; tran_id=""; split_id="";
recon=""; recon_date=""; value=""; quantity=""; split_guid=""; action="";
memo="" }
let copy_item {tim_posted; tim_entered; num; desc; tran_id; split_id; recon;
recon_date; value; quantity; split_guid; action; memo } =
{tim_posted; tim_entered; num; desc; tran_id; split_id; recon; recon_date;
value; quantity; split_guid; action; memo }
let scan1 value = Scanf.sscanf value "%f/%f" (fun a b -> Printf.sprintf "%.2f"
(a /. b))
let cnts = ref []
let acch = Hashtbl.create 255
let payeeh = Hashtbl.create 255
let trancnt = ref 0
let mymoney acclst tranlst payees = Element ("KMYMONEY-FILE", [],
[Element ("FILEINFO", [],
[Element ("CREATION_DATE", [("date", "2024-01-05")], []);
Element ("LAST_MODIFIED_DATE", [("date", "2024-01-05")], []);
Element ("VERSION", [("id", "1")], []);
Element ("FIXVERSION", [("id", "5")], [])]);
Element ("USER", [("name", ""); ("email", "")],
[Element ("ADDRESS",
[("zipcode", ""); ("county", ""); ("telephone", ""); ("street", "");
("city", "")],
[])]);
Element ("INSTITUTIONS", [("count", "0")], []);
Element ("PAYEES", [("count", string_of_int (List.length payees))], payees);
Element ("COSTCENTERS", [("count", "0")], []);
Element ("TAGS", [("count", "1")],
[Element ("TAG",
[("id", "G000001"); ("name", "tags"); ("closed", "0");
("tagcolor", "#000000")],
[])]);
Element ("ACCOUNTS", [("count", string_of_int (List.length acclst))],
acclst);
Element ("TRANSACTIONS", [("count", string_of_int (List.length tranlst))],
tranlst);
Element ("KEYVALUEPAIRS", [],
[Element ("PAIR", [("key", "kmm-baseCurrency"); ("value", "GBP")], []);
Element
("PAIR",
[("key", "kmm-id");
("value", "{93976d56-6861-4c85-aa47-b6c7b4e20175}")],
[])]);
Element ("SCHEDULES", [("count", "0")], []);
Element ("SECURITIES", [("count", "0")], []);
Element ("CURRENCIES", [("count", "1")],
[Element ("CURRENCY",
[("type", "3"); ("id", "GBP"); ("name", "British Pound");
("scf", "100"); ("saf", "100"); ("rounding-method", "7");
("pp", "4"); ("symbol", "£")],
[])]);
Element ("PRICES", [("count", "0")], []);
Element ("REPORTS", [("count", "0")], []);
Element ("BUDGETS", [("count", "0")], []);
Element ("ONLINEJOBS", [("count", "0")], [])])
let payee name id = Element ("PAYEE",
[("id", id); ("name", name); ("reference", "");
("matchignorecase", "1"); ("matchingenabled", "1"); ("email", "");
("matchkey", "^$"); ("usingmatchkey", "0")],
[Element ("ADDRESS",
[("state", ""); ("telephone", ""); ("street", ""); ("city", "");
("postcode", "")],
[])])
let find_payee name =
if Hashtbl.mem payeeh name then
Hashtbl.find payeeh name
else
let id = Printf.sprintf "P%.6d" (1 + Hashtbl.length payeeh) in
let _ = Hashtbl.add payeeh name id in id
let _ = List.iter(function
| Element ("ACCOUNT",
([("type", acctype); ("description", _); ("id", id);
("name", accname); ("lastreconciled", _); ("opened", _);
("institution", _); ("lastmodified", _); ("parentaccount", "");
("number", _); ("currency", "GBP")]), _) ->
Hashtbl.add acch id (accname,acctype,[],ref [], id)
| _ -> failwith "acc''")
[Element
("ACCOUNT",
[("type", "9"); ("description", ""); ("id", "AStd::Asset");
("name", "Asset"); ("lastreconciled", ""); ("opened", "");
("institution", ""); ("lastmodified", ""); ("parentaccount", "");
("number", ""); ("currency", "GBP")],
[]);
Element
("ACCOUNT",
[("type", "10"); ("description", ""); ("id", "AStd::Liability");
("name", "Liability"); ("lastreconciled", ""); ("opened", "");
("institution", ""); ("lastmodified", ""); ("parentaccount", "");
("number", ""); ("currency", "GBP")],
[]);
Element
("ACCOUNT",
[("type", "12"); ("description", ""); ("id", "AStd::Income");
("name", "Income"); ("lastreconciled", ""); ("opened", "");
("institution", ""); ("lastmodified", ""); ("parentaccount", "");
("number", ""); ("currency", "GBP")],
[]);
Element
("ACCOUNT",
[("type", "13"); ("description", ""); ("id", "AStd::Expense");
("name", "Expense"); ("lastreconciled", ""); ("opened", "");
("institution", ""); ("lastmodified", ""); ("parentaccount", "");
("number", ""); ("currency", "GBP")],
[]);
Element
("ACCOUNT",
[("type", "16"); ("description", ""); ("id", "AStd::Equity");
("name", "Equity"); ("lastreconciled", ""); ("opened", "");
("institution", ""); ("lastmodified", ""); ("parentaccount", "");
("number", ""); ("currency", "GBP")],
[])]
let dbgacc = ref None
let othcnv = ref None
let cnvacc = function
| "BANK" -> "AStd::Asset"
| "EXPENSE" -> "AStd::Expense"
| "INCOME" -> "AStd::Asset"
| "ASSET" -> "AStd::Asset"
| "EQUITY" -> "AStd::Equity"
| "LIABILITY" -> "AStd::Liability"
| "RECEIVABLE" -> "AStd::Asset"
| "PAYABLE" -> "AStd::Liability"
| oth -> othcnv := Some oth; failwith "othcnv"
let rec root_typ typ' guid =
match Hashtbl.find acch guid with
| ("Root Account", "ROOT", [], _, _) ->
(try let std = cnvacc typ' in let pth,typ,x,refcnt,p = Hashtbl.find acch std
in typ with _ -> "1")
| (nam', typ',
[Element
("act:parent", [("type", "guid")],
[PCData parent_guid])], _, _) -> let ptyp = root_typ typ'
parent_guid in
ptyp
| oth -> dbgacc := Some oth; failwith "root_typ"
let validate maxlen nam =
let nam' = ref (String.concat " and " (String.split_on_char '&' nam)) in
String.iter (fun ch ->
nam' := String.concat "" (String.split_on_char ch !nam');
) "!@£$%^&*()_+=-{}[]|\"':;/?<>\"~`,.";
String.sub !nam' 0 (min (String.length !nam') maxlen)
let rec traverse tranlst = function
| Element ("gnc:account", [("version", "2.0.0")], lst) ->
let accname = ref "" in
let acctype = ref "" in
let attr = ref [] in
let guid = ref "" in
List.iter (function
| Element ("act:name", [], [PCData accname']) -> accname := accname'
| Element ("act:id", [("type", "guid")], [PCData guid']) -> guid := guid'
| Element ("act:type", [], [PCData acctyp']) -> acctype := acctyp'
| Element ("act:commodity", [],
[Element ("cmdty:space", [], [PCData "CURRENCY"]);
Element ("cmdty:id", [], [PCData "GBP"])]) -> ()
| Element ("act:commodity-scu", [], [PCData "100"]) -> ()
| attr' -> attr := attr' :: []) lst;
Hashtbl.add acch !guid (!accname,!acctype,!attr,ref [], Printf.sprintf
"A%.6d" (1 + Hashtbl.length acch))
| Element ("gnc:transaction", [("version", "2.0.0")], trnlst) ->
let item = empty_item () in
incr trancnt;
List.iter (function
| Element ("trn:id", [("type", "guid")], [PCData tran_id]) -> item.tran_id
<- tran_id
| Element ("trn:currency", [],
[Element ("cmdty:space", [], [PCData "CURRENCY"]);
Element ("cmdty:id", [], [PCData "GBP"])]) -> ()
| Element ("trn:num", [], [PCData num]) -> item.num <- num
| Element ("trn:date-posted", [], [Element ("ts:date", [], [PCData
date_posted])]) -> item.tim_posted <- cnv date_posted
| Element ("trn:date-entered", [], [Element ("ts:date", [], [PCData
date_ent])]) -> item.tim_entered <- cnv date_ent
| Element ("trn:description", [], []) -> ()
| Element ("trn:description", [], [PCData desc]) -> item.desc <- desc
| Element ("trn:slots", [], slots) -> List.iter (function Element
("trn:slot", [], lst) ->
List.iter (function
| Element ("slot:key", [("type", "guid")], [PCData split_id]) ->
item.split_id <- split_id
| Element (kw, xmlns, lst) -> failwith kw
| PCData txt -> failwith txt) lst;
| oth -> unm := Some oth) slots
| Element ("trn:splits", [], splits) -> let split_lst = ref [] in List.iteri
(fun ix -> function Element ("trn:split", [], lst) ->
List.iter (function
| Element ("split:id", [("type", "guid")], [PCData split_id]) ->
item.split_id <- split_id
| Element ("split:reconciled-state", [], [PCData recon]) -> item.recon
<- recon
| Element ("split:reconcile-date", [], [Element ("ts:date", [],
[PCData recon_date])]) -> item.recon_date <- recon_date
| Element ("split:value", [], [PCData value]) -> item.value <- scan1
value
| Element ("split:quantity", [], [PCData quantity]) -> item.quantity
<- scan1 quantity
| Element ("split:account", [("type", "guid")], [PCData split_guid])
-> item.split_guid <- split_guid
| Element ("split:action", [], [PCData action]) -> item.action <-
action
| Element ("split:memo", [], [PCData memo]) -> item.memo <- memo
| Element (kw, xmlns, lst) -> failwith kw
| PCData txt -> failwith txt) lst;
let pth,typ,_,_,id = Hashtbl.find acch item.split_guid in
split_lst := Element ("SPLIT",
[("id", Printf.sprintf "S%.4d" (ix+1)); ("reconcileflag", "0");
("memo", validate 64 item.memo);
("bankid", ""); ("shares", item.quantity); ("value", item.value);
("price", "1/1"); ("payee", find_payee item.desc);
("account", id); ("reconciledate", ""); ("action", "");
("number", "")], []) :: !split_lst;
| oth -> unm := Some oth) splits;
tranlst := (Element ("TRANSACTION",
[("id", Printf.sprintf "T0000000000%.8d" !trancnt);
("commodity", "GBP"); ("memo", "item.desc");
("entrydate", item.tim_entered);
("postdate", item.tim_posted)],
[Element ("SPLITS", [], List.rev !split_lst)])) :: !tranlst
| oth -> unm := Some oth) trnlst
| Element ("gnc:commodity", [("version", "2.0.0")], lst) -> ()
| Element ("gnc:count-data", [("cd:type",
("book"|"commodity"|"account"|"transaction" as ty))], [PCData cnt]) ->
cnts := (ty, cnt) :: !cnts;
| Element ("book:id", [("type", "guid")], [PCData book_id]) -> ()
| Element ("slot:key", [], [PCData key]) -> ()
| Element ("slot:value", [("type", "string")], [PCData slot_value]) -> ()
| Element ("gnc:transaction" as kw, _, _) as trn -> unm := Some trn; failwith kw
| Element (kw, xmlns, lst) as x -> unm := Some x; List.iter (traverse tranlst)
lst; unm := None
| PCData txt -> failwith txt
let timcmp ((tim,_), _) ((tim',_), _) = if tim=tim' then 0 else
int_of_float(ceil(tim -. tim'))
let cnt' typ = match List.assoc_opt typ !cnts with Some n -> int_of_string n |
None -> 0
let _ =
let tranlst = ref [] in
let xml = parse_file Sys.argv.(1) in
let _ = traverse tranlst xml in
let tranlst = List.rev !tranlst in
let accs = cnt' "account" in assert(accs+5=Hashtbl.length acch);
let trans = cnt' "transaction" in assert(!trancnt == trans);
let acclst = ref [] in
let rec typ_helper typ' guid = (let pth,typ,x,refcnt,p = Hashtbl.find acch
guid in
if typ = "ROOT" then Hashtbl.find acch (cnvacc typ') else
pth,typ,x,refcnt,p) in
Hashtbl.iter (fun guid (nam',typ',attr,refcnt',id') -> match attr with
| [Element ("act:parent", [("type", "guid")], [PCData parent_guid])] ->
let pth,typ,_,refcnt,_ = typ_helper typ' parent_guid in
refcnt := Element ("SUBACCOUNT", [("id", id')], []) :: !refcnt
| [] -> ()
| oth -> failwith "parent") acch;
Hashtbl.iter (fun guid (nam',typ',attr,refcnt',id') -> match attr with
| [Element ("act:parent", [("type", "guid")], [PCData parent_guid])] ->
let pth,typ,_,_,pid = typ_helper typ' parent_guid in
acclst := Element ("ACCOUNT",
[("type", root_typ typ parent_guid); ("description", ""); ("id",
id');
("name", validate 24 nam'); ("lastreconciled", "");
("opened", "2024-01-05"); ("institution", ""); ("lastmodified", "");
("parentaccount", pid); ("number", ""); ("currency", "GBP")],
[Element ("SUBACCOUNTS", [], !refcnt')]) :: !acclst;
| [] ->
acclst := Element ("ACCOUNT",
[("type", typ'); ("description", ""); ("id", id');
("name", validate 24 nam'); ("lastreconciled", "");
("opened", "2024-01-05"); ("institution", ""); ("lastmodified", "");
("parentaccount", ""); ("number", ""); ("currency", "GBP")],
[Element ("SUBACCOUNTS", [], !refcnt')]) :: !acclst;
| oth -> failwith "parent") acch;
let payees = ref [] in
Hashtbl.iter (fun k x -> payees := payee (validate 64 k) x :: !payees)
payeeh;
let fd = open_out Sys.argv.(2) in
let _ = output_string fd ("<?xml version=\"1.0\"
encoding=\"utf-8\"?>\n<!DOCTYPE KMYMONEY-FILE>\n"^to_string_fmt (mymoney
!acclst tranlst !payees)) in
close_out fd;
!acclst, tranlst
