gcconv.py 11KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395
  1. # coding=utf-8
  2. """Convert Bank statement CSV to Gnucash-acceptable format"""
  3. from collections import namedtuple
  4. ParserState = namedtuple('ParserState', 'ths nxt')
  5. def valid_formats():
  6. return _parsers.keys()
  7. def convert_file(fpath, fmt, encoding='utf-8'):
  8. try:
  9. parser_class = _parsers[fmt]
  10. except KeyError:
  11. raise ValueError('unsupported format: %s' % fmt)
  12. else:
  13. parser = parser_class()
  14. parser.parse_file(fpath, encoding)
  15. return parser.format_csv()
  16. def _unquote(value):
  17. if len(value) < 2:
  18. return value
  19. if value[0] == value[-1] == "'":
  20. return value[1:-1]
  21. if value[0] == value[-1] == '"':
  22. return value[1:-1]
  23. return value
  24. class BaseTransaction(object):
  25. """A CSV-imported transaction"""
  26. pass
  27. class GCTransaction(object):
  28. """A Gnucash-acceptable transaction"""
  29. def __init__(self, date, deposit, withdrawal, description):
  30. self.date = date
  31. self.deposit = deposit
  32. self.withdrawal = withdrawal
  33. self.description = description
  34. def __str__(self):
  35. """Naive CSV line printer"""
  36. values = [self.date, self.deposit, self.withdrawal, self.description]
  37. return ";".join(['"%s"' % v for v in values])
  38. class BaseTransactionParser(object):
  39. """A statement parser"""
  40. name = "__NONE__"
  41. def __init__(self):
  42. self.transactions = []
  43. self.state = ParserState(ths=False, nxt=False)
  44. def is_open(self):
  45. return self.state.ths
  46. def is_closed(self):
  47. return not self.state.ths
  48. def parse_file(self, fpath, encoding='utf-8'):
  49. lines_read = 0
  50. with open(fpath) as f:
  51. for line in f.readlines():
  52. line = line.decode(encoding).strip()
  53. lines_read += 1
  54. self.state = self.advance(line, lineno=lines_read)
  55. if self.is_open():
  56. self.transactions.append(self.parse_line(line).to_gc())
  57. def format_csv(self):
  58. return "\n".join([
  59. unicode(t).encode('utf-8')
  60. for t in self.transactions
  61. ])
  62. class MbankTransaction(BaseTransaction):
  63. """Item in an Mbank CSV"""
  64. # (a) Datum uskutečnění transakce
  65. # (b) Datum zaúčtování transakce
  66. # (c) Popis transakce
  67. # (d) Zpráva pro příjemce
  68. # (e) Plátce/Příjemce
  69. # (f) Číslo účtu plátce/příjemce
  70. # (g) KS
  71. # (h) VS
  72. # (i) SS
  73. # (j) Částka transakce
  74. # (k) Účetní zůstatek po transakci
  75. # (l) (empty column at the end)
  76. def __init__(self, line):
  77. fields = [self._cleanup_field(f) for f in line.split(";")]
  78. self.date_r = self._convert_date(fields.pop(0)) # (a)
  79. self.date_b = self._convert_date(fields.pop(0)) # (b)
  80. __ = fields.pop() # (l)
  81. self.new_balance = self._parse_currency(fields.pop()) # (k)
  82. amount = self._parse_currency(fields.pop()) # (j)
  83. if amount >= 0:
  84. self.amountd = amount
  85. self.amountw = 0
  86. else:
  87. self.amountd = 0
  88. self.amountw = -amount
  89. self._scrap = fields # (c-i)
  90. def _cleanup_field(self, field):
  91. x = ' '.join(_unquote(field).strip().split())
  92. return '' if x == '-' else x
  93. def _convert_date(self, text):
  94. day, month, year = text.split('-')
  95. return '-'.join([year, month, day])
  96. def _description(self):
  97. def type_interesting(T):
  98. boring_types = [
  99. u'PŘÍCHOZÍ PLATBA Z MBANK',
  100. u'ODCHOZÍ PLATBA DO MBANK',
  101. u'PŘÍCHOZÍ PLATBA Z JINÉ BANKY',
  102. u'ODCHOZÍ PLATBA DO JINÉ BANKY',
  103. u'PLATBA KARTOU',
  104. ]
  105. if not T:
  106. return False
  107. if T in boring_types:
  108. return False
  109. return True
  110. def message_interesting(M):
  111. boring_messages = [
  112. u'PŘEVOD PROSTŘEDKŮ',
  113. ]
  114. if not M:
  115. return False
  116. if M in boring_messages:
  117. return False
  118. return True
  119. type_, message, party, number, ks, vs, ss = self._scrap
  120. dpt = u' DATUM PROVEDENÍ TRANSAKCE: '
  121. if dpt in message:
  122. message, __ = message.split(dpt)
  123. out = []
  124. if type_interesting(type_):
  125. out.append(type_)
  126. if message_interesting(message):
  127. out.append(message)
  128. if party:
  129. out.append(party)
  130. return " / ".join(out)
  131. def _parse_currency(self, text):
  132. """Read Mbank currency format"""
  133. num = text.replace(' ', '')
  134. num = num.replace(',', '.')
  135. return float(num)
  136. def to_gc(self):
  137. """Convert to GCTransaction"""
  138. return GCTransaction(
  139. date=self.date_r,
  140. deposit=self.amountd,
  141. withdrawal=self.amountw,
  142. description=self._description()
  143. )
  144. class MbankTransactionParser(BaseTransactionParser):
  145. def parse_line(self, line):
  146. return MbankTransaction(line)
  147. def advance(self, line, lineno=None):
  148. """Choose parser state according to current line and line no."""
  149. if self.state.nxt:
  150. return ParserState(ths=True, nxt=False)
  151. if self.is_closed() and line.startswith(u'#Datum uskute'):
  152. return ParserState(ths=False, nxt=True)
  153. if self.is_open() and not line:
  154. return ParserState(ths=False, nxt=False)
  155. return self.state
  156. class FioTransaction(BaseTransaction):
  157. # ID operace
  158. # Datum
  159. # Objem
  160. # Měna
  161. # Protiúčet
  162. # Název protiúčtu
  163. # Kód banky
  164. # Název banky
  165. # KS
  166. # VS
  167. # SS
  168. # Poznámka
  169. # Zpráva pro příjemce
  170. # Typ
  171. # Provedl
  172. # Upřesnění
  173. # Poznámka
  174. # BIC
  175. # ID pokynu
  176. def __init__(self, line):
  177. fields = [self._cleanup_field(f) for f in line.split(";")]
  178. __ = fields.pop(0)
  179. self.date = self._convert_date(fields.pop(0))
  180. amount = self._parse_currency(fields.pop(0))
  181. if amount >= 0:
  182. self.amountd = amount
  183. self.amountw = 0
  184. else:
  185. self.amountd = 0
  186. self.amountw = -amount
  187. self.currency = fields.pop(0)
  188. assert self.currency == 'CZK'
  189. self._scrap = [self._cleanup_field(f) for f in fields]
  190. def _cleanup_field(self, field):
  191. x = ' '.join(_unquote(field).strip().split())
  192. return '' if x == '-' else x
  193. def _description(self):
  194. (
  195. party_acc,
  196. party_accnname,
  197. party_bankcode,
  198. party_bankname,
  199. ks,
  200. vs,
  201. ss,
  202. note1,
  203. message,
  204. type_,
  205. who,
  206. explanation,
  207. note2,
  208. bic,
  209. comm_id
  210. ) = self._scrap
  211. out = []
  212. if message:
  213. out.append(message)
  214. return " / ".join(out)
  215. def _convert_date(self, text):
  216. day, month, year = text.split('.')
  217. return '-'.join([year, month, day])
  218. def _parse_currency(self, text):
  219. return float(text)
  220. def to_gc(self):
  221. """Convert to GCTransaction"""
  222. return GCTransaction(
  223. date=self.date,
  224. deposit=self.amountd,
  225. withdrawal=self.amountw,
  226. description=self._description()
  227. )
  228. class FioTransactionParser(BaseTransactionParser):
  229. def parse_line(self, line):
  230. return FioTransaction(line)
  231. def advance(self, line, lineno=None):
  232. """Choose parser state according to current line and line no."""
  233. if self.state.nxt:
  234. return ParserState(ths=True, nxt=False)
  235. if self.is_open() and line.startswith(u'"ID operace";"Datum";'):
  236. return ParserState(ths=False, nxt=True)
  237. return self.state
  238. class RaifTransaction(BaseTransaction):
  239. # (a) Transaction Date
  240. # (b) Booking Date
  241. # (c) Account number
  242. # (d) Account Name
  243. # (e) Transaction Category
  244. # (f) Accocunt Number [SIC]
  245. # (g) Name of Account
  246. # (h) Transaction type
  247. # (i) Message
  248. # (j) Note
  249. # (k) VS
  250. # (l) KS
  251. # (m) SS
  252. # (n) Booked amount
  253. # (o) Account Currency
  254. # (p) Original Amount and Currency
  255. # (q) Original Amount and Currency
  256. # (r) Fee
  257. # (s) Transaction ID
  258. def __init__(self, line):
  259. self._fields = [self._cleanup_field(f) for f in line.split(";")]
  260. self.date = self._convert_date(self._fields[0])
  261. amount = self._parse_currency(self._fields[13])
  262. if amount >= 0:
  263. self.amountd = amount
  264. self.amountw = 0
  265. else:
  266. self.amountd = 0
  267. self.amountw = -amount
  268. self.currency = self._fields[14]
  269. assert self.currency == 'CZK'
  270. def _cleanup_field(self, field):
  271. x = ' '.join(_unquote(field).strip().split())
  272. return '' if x == '-' else x
  273. def _description(self):
  274. (
  275. transaction_date,
  276. booking_date,
  277. src_account_number,
  278. src_account_name,
  279. transaction_category,
  280. dst_account_number,
  281. dst_account_name,
  282. dst_ransaction_type,
  283. message,
  284. note,
  285. vs,
  286. ks,
  287. ss,
  288. booked_amount,
  289. account_currency,
  290. original_amount_and_currency,
  291. original_amount_and_currency,
  292. fee,
  293. transaction_id,
  294. ) = self._fields
  295. out = []
  296. if message:
  297. out.append(message)
  298. if note and note != message:
  299. out.append(note)
  300. if dst_account_name:
  301. out.append(dst_account_name)
  302. else:
  303. out.append(dst_account_number)
  304. out.append("VS:%s" % vs)
  305. return " / ".join(out)
  306. def _convert_date(self, text):
  307. day, month, year = text.split('.')
  308. return '-'.join([year, month, day])
  309. def _parse_currency(self, text):
  310. return float(text.replace(',', '.').replace(' ', ''))
  311. def to_gc(self):
  312. """Convert to GCTransaction"""
  313. return GCTransaction(
  314. date=self.date,
  315. deposit=self.amountd,
  316. withdrawal=self.amountw,
  317. description=self._description()
  318. )
  319. class RaifTransactionParser(BaseTransactionParser):
  320. def parse_line(self, line):
  321. return RaifTransaction(line)
  322. def advance(self, line, lineno=None):
  323. """Choose parser state according to current line and line no."""
  324. if self.state.nxt:
  325. return ParserState(ths=True, nxt=False)
  326. if self.is_closed() and line.startswith(u'Transaction Date;Booking'):
  327. return ParserState(ths=False, nxt=True)
  328. return self.state
  329. _parsers = {
  330. 'CzMbank': MbankTransactionParser,
  331. 'CzFio': FioTransactionParser,
  332. 'CzRaif': RaifTransactionParser,
  333. }