def extract_balances_and_prices(self, file, counter):
new_entries = []
date = self.get_max_transaction_date()
if date:
# balance assertions are evaluated at the beginning of the date, so move it to the following day
date += datetime.timedelta(days=1)
else:
print(
"Warning: no transactions, using statement date for balance assertions."
)
settlement_fund_balance = 0
for pos in self.get_balance_positions():
ticker, ticker_long_name = self.get_ticker_info(pos.security)
meta = data.new_metadata(file.name, next(counter))
# if there are no transactions, use the date in the source file for the balance. This gives us the
# bonus of an updated, recent balance assertion
bal_date = date if date else pos.date.date()
balance_entry = data.Balance(
meta, bal_date,
self.commodity_leaf(self.config['main_account'], ticker),
amount.Amount(pos.units, ticker), None, None)
new_entries.append(balance_entry)
if ticker in self.money_market_funds:
settlement_fund_balance = pos.units
# extract price info if available
if hasattr(pos, 'unit_price') and hasattr(pos, 'date'):
meta = data.new_metadata(file.name, next(counter))
price_entry = data.Price(
meta, pos.date.date(), ticker,
amount.Amount(pos.unit_price, self.currency))
new_entries.append(price_entry)
# ----------------- available cash
available_cash = self.get_available_cash()
if available_cash is not False:
try:
balance = self.get_available_cash() - settlement_fund_balance
meta = data.new_metadata(file.name, next(counter))
bal_date = date if date else self.file_date(file).date()
balance_entry = data.Balance(
meta, bal_date, self.cash_account,
amount.Amount(balance, self.currency), None, None)
new_entries.append(balance_entry)
except AttributeError: # self.get_available_cash()
pass
return new_entries
def extract(self, file, existing_entries=None):
# Open the CSV file and create directives.
entries = []
index = 0
with open(file.name, 'rb') as f:
eml = parser.BytesParser().parse(fp=f)
b = base64.b64decode(eml.get_payload()[0].get_payload())
d = BeautifulSoup(b, "lxml")
date_range = d.findAll(text=re.compile(
'\d{4}\/\d{1,2}\/\d{1,2}-\d{4}\/\d{1,2}\/\d{1,2}'))[0]
transaction_date = dateparse(
date_range.split('-')[1].split('(')[0]).date()
balance = '-' + d.find(src="https://pbdw.ebank.cmbchina.com/"
"cbmresource/22/dyzd/jpkdyzd/xbgbdt/bqhkzz.jpg")\
.parent.parent.find_next_sibling(
'td').select('font')[0].text.replace('￥', '').replace(',', '').strip()
txn_balance = data.Balance(account=self.account_name,
amount=Amount(D(balance), 'CNY'),
meta=data.new_metadata(".", 1000),
tolerance=None,
diff_amount=None,
date=transaction_date)
entries.append(txn_balance)
bands = d.select('#fixBand29 #loopBand2>table>tr')
for band in bands:
tds = band.select('td #fixBand15 table table td')
if len(tds) == 0:
continue
trade_date = tds[1].text.strip()
if trade_date == '':
trade_date = tds[2].text.strip()
# date = datetime.strptime(trade_date,'%m%d').replace(year=transaction_date.year).date()
date = datetime.strptime(trade_date, '%m%d')
if date.month == 12 and transaction_date.month == 1:
date = date.replace(year=transaction_date.year - 1).date()
else:
date = date.replace(year=transaction_date.year).date()
full_descriptions = tds[3].text.strip().split('-')
payee = full_descriptions[0]
narration = '-'.join(full_descriptions[1:])
real_currency = 'CNY'
real_price = tds[4].text.replace('￥', '').replace('\xa0',
'').strip()
# print("Importing {} at {}".format(narration, date))
flag = "*"
amount = -Amount(D(real_price), real_currency)
meta = data.new_metadata(file.name, index)
txn = data.Transaction(
meta, date, self.FLAG, payee, narration, data.EMPTY_SET,
data.EMPTY_SET, [
data.Posting(self.account_name, amount, None, None,
None, None),
])
entries.append(txn)
# Insert a final balance check.
return entries
def extract(self, file):
#parse csv file
if self.file_format_version == 1:
buchungstag, auftraggeber_empfaenger, buchungstext, verwendungszweck, betrag, kontostand, indices, endsaldo = parse_csv_file_v1(
file.name)
elif self.file_format_version == 2:
buchungstag, auftraggeber_empfaenger, buchungstext, verwendungszweck, betrag, kontostand, indices, endsaldo = parse_csv_file_v2(
file.name)
else:
raise IOError("Unknown file format.")
#create transactions
entries = []
for i in range(len(buchungstag)):
postings = self.guess_postings(auftraggeber_empfaenger[i],
float(betrag[i]))
meta = data.new_metadata(file.name, indices[i])
txn = data.Transaction(meta, buchungstag[i], self.flag,
auftraggeber_empfaenger[i],
verwendungszweck[i], data.EMPTY_SET,
data.EMPTY_SET, postings)
entries.append(txn)
#create balance
meta = data.new_metadata(file.name, endsaldo[2])
entries.append(
data.Balance(meta, endsaldo[0] + datetime.timedelta(days=1),
self.account,
amount.Amount(D(endsaldo[1]),
self.currency), None, None))
return entries
def add_account(self, name, date=None, currency=None, initial_amount=None):
date = date or config.DEFAULT_DATE
name = self._format_account_name(name)
self.accounts.append(name)
currencies = currency and [currency]
self.entries.append(
bc.Open(self._get_meta(), date, name, currencies, booking=None))
if initial_amount is not None and initial_amount != 0:
assert currency is not None
self.pad_balances.append(
bc.Pad(self._get_meta(),
date=config.DEFAULT_PAD_DATE,
account=name,
source_account=config.OPENING_BALANCE_ACCOUNT))
self.pad_balances.append(
bc.Balance(self._get_meta(),
date=config.DEFAULT_BALANCE_DATE,
account=name,
amount=bc.Amount(initial_amount, currency),
tolerance=None,
diff_amount=None))
return len(self.accounts) - 1 # index of the account on self.accounts
def entry_from_gl(self, entries: typing.List[Entry]) -> typing.Iterable:
"""Given a single GL_ID and as a list of Entrys
"""
first = entries[0]
postings = []
all_tags = set()
all_meta = {'lineno': 0, 'filename': "", 'gl-id': first.gl_id}
all_links = set()
for entry in entries:
self.new_accounts.add(entry.account)
if first.entry_type == "Balance":
if entry.amount and entry.date.year == 2016:
yield data.Pad(date=entry.date -
datetime.timedelta(days=1),
account=entry.account,
source_account="Equity:OpeningBalances",
meta={
'lineno': 0,
'filename': '',
'note': entry.narration,
'gl-id': entry.gl_id
})
yield data.Balance(date=entry.date,
amount=data.Amount(entry.amount,
entry.currency),
account=entry.account,
tolerance=None,
diff_amount=None,
meta={
'lineno': 0,
'filename': '',
'note': entry.narration,
'gl-id': entry.gl_id
})
else:
posting = data.Posting(entry.account,
data.Amount(entry.amount,
entry.currency),
None,
None,
flag='*',
meta=entry.meta)
all_tags.update(entry.tags)
all_links.update(entry.links)
postings.append(posting)
all_meta.update(posting.meta or {})
if postings:
yield data.Transaction(meta=all_meta,
date=entry.date,
flag='*',
payee=entry.payee,
narration=entry.narration,
tags=all_tags,
links=all_links,
postings=postings)
def check_closing(entries, options_map):
"""Expand 'closing' metadata to a zero balance check.
Args:
entries: A list of directives.
unused_options_map: An options map.
Returns:
A list of new errors, if any were found.
"""
new_entries = []
for entry in entries:
if isinstance(entry, data.Transaction):
for i, posting in enumerate(entry.postings):
if posting.meta and posting.meta.get('closing', False):
# Remove the metadata.
meta = posting.meta.copy()
del meta['closing']
posting = posting._replace(meta=meta)
entry.postings[i] = posting
# Insert a balance.
date = entry.date + datetime.timedelta(days=1)
balance = data.Balance(data.new_metadata("<check_closing>", 0),
date, posting.account,
amount.Amount(ZERO, posting.units.currency),
None, None)
new_entries.append(balance)
new_entries.append(entry)
return new_entries, []
def create_sort_data(self):
account = 'Assets:Bank:Checking'
date1 = date(2014, 1, 15)
date2 = date(2014, 1, 18)
date3 = date(2014, 1, 20)
entries = [
data.Transaction(data.new_metadata(".", 1100), date3, FLAG, None,
"Next day", None, None, []),
data.Close(data.new_metadata(".", 1000), date2, account),
data.Balance(data.new_metadata(".", 1001), date2, account,
A('200.00 USD"'), None, None),
data.Open(data.new_metadata(".", 1002), date2, account, 'USD',
None),
data.Transaction(data.new_metadata(".", 1009), date2, FLAG, None,
"Transaction 2", None, None, []),
data.Transaction(data.new_metadata(".", 1008), date2, FLAG, None,
"Transaction 1", None, None, []),
data.Transaction(data.new_metadata(".", 900), date1, FLAG, None,
"Previous day", None, None, []),
]
for entry in entries:
if isinstance(entry, data.Transaction):
data.create_simple_posting(entry, 'Assets:Bank:Checking',
'123.45', 'USD')
return entries
def balance_assertion(transaction, opening=False, closing=False):
lineno = transaction[0]
line = transaction[1]
balance = _format_number_de(line['Saldo'])
if opening:
# calculate balance before the first transaction
# Currencies must match for subtraction
if line['Währung_1'] != line['Währung_2']:
warnings.warn(
f"{file_.name}:{lineno} "
"opening balance can not be generated "
"due to currency mismatch: "
f"{line['Währung_1']} <> {line['Währung_2']}")
return []
balance -= _format_number_de(line['Betrag'])
balancedate = self._date_from
if closing:
# balance after the last transaction:
# next day's opening balance
balancedate = self._date_to + timedelta(days=1)
return [
data.Balance(
data.new_metadata(file_.name, lineno),
balancedate,
self.account,
Amount(balance, line['Währung_1']),
None,
None,
)
]
def Balances(self,cr):
# generate Balance statements from IBKR Cash reports
# balances
crTransactions = []
for idx, row in cr.iterrows():
currency=row['currency']
if currency == 'BASE_SUMMARY':
continue # this is a summary balance that is not needed for beancount
amount_=amount.Amount(row['endingCash'].__round__(2),currency)
# make the postings. two for deposits
postings=[data.Posting(self.depositAccount,
-amount_, None, None, None, None),
data.Posting(self.getLiquidityAccount(currency),
amount_,None, None, None, None)
]
meta=data.new_metadata('balance',0)
crTransactions.append(data.Balance(
meta,
row['toDate'] + timedelta(days=1), # see tariochtools EC imp.
self.getLiquidityAccount(currency),
amount_,
None,
None))
return crTransactions
def deserialise(json_entry):
"""Parse JSON to a Beancount entry.
Args:
json_entry: The entry.
Raises:
KeyError: if one of the required entry fields is missing.
FavaAPIException: if the type of the given entry is not supported.
"""
if json_entry['type'] == 'Transaction':
date = util.date.parse_date(json_entry['date'])[0]
narration, tags, links = extract_tags_links(json_entry['narration'])
postings = [deserialise_posting(pos) for pos in json_entry['postings']]
return data.Transaction(json_entry['meta'], date, json_entry['flag'],
json_entry['payee'], narration, tags, links,
postings)
if json_entry['type'] == 'Balance':
date = util.date.parse_date(json_entry['date'])[0]
number = parse_number(json_entry['number'])
amount = Amount(number, json_entry['currency'])
return data.Balance(json_entry['meta'], date, json_entry['account'],
amount, None, None)
if json_entry['type'] == 'Note':
date = util.date.parse_date(json_entry['date'])[0]
comment = json_entry['comment'].replace('"', '')
return data.Note(json_entry['meta'], date, json_entry['account'],
comment)
raise FavaAPIException('Unsupported entry type.')
def create_balance_entry(self, filename, date, balance):
# Balance directives will be sorted in front of transactions, so there is no need
# to have a line number to break ties.
meta = data.new_metadata(filename, 0)
balance_entry = data.Balance(meta, date, self.account,
amount.Amount(balance, self.currency),
None, None)
return balance_entry
def extract(self, file, existing_entries):
entries = []
has_balance = False
with StringIO(file.contents()) as csvfile:
reader = csv.DictReader(csvfile, [
'Date', 'Reference', 'PaidOut', 'PaidIn', 'ExchangeOut',
'ExchangeIn', 'Balance', 'Category'
],
delimiter=';',
skipinitialspace=True)
next(reader)
for row in reader:
metakv = {
'category': row['Category'].strip(),
}
exchangeIn = row['ExchangeIn'].strip()
exchangeOut = row['ExchangeOut'].strip()
if exchangeIn and exchangeOut:
metakv['originalIn'] = exchangeIn
metakv['originalOut'] = exchangeOut
elif exchangeIn:
metakv['original'] = exchangeIn
elif exchangeOut:
metakv['original'] = exchangeOut
book_date = parse(row['Date'].strip()).date()
try:
credit = D(row['PaidIn'].replace('\'', '').strip())
debit = D(row['PaidOut'].replace('\'', '').strip())
bal = D(row['Balance'].replace('\'', '').strip())
amt = amount.Amount(credit - debit, self.currency)
balance = amount.Amount(bal, self.currency)
except Exception as e:
logging.warning(e)
continue
meta = data.new_metadata(file.name, 0, metakv)
entry = data.Transaction(
meta, book_date, '*', '', row['Reference'].strip(),
data.EMPTY_SET, data.EMPTY_SET, [
data.Posting(self.account, amt, None, None, None,
None),
])
entries.append(entry)
# only add balance after the top (newest) transaction
if not has_balance:
book_date = book_date + timedelta(days=1)
entry = data.Balance(meta, book_date, self.account,
balance, None, None)
entries.append(entry)
has_balance = True
return entries
def create_balance(filename, date, account, amount_):
balance = data.Balance(
data.new_metadata(filename, ""), # metadata
date, # date
account, # account
amount_, # amount
None, # tolerance
None, # diff_amount
)
return balance
def createBalanceEntry(self, file, date, amt):
meta = data.new_metadata(file.name, 0)
return data.Balance(
meta,
parse(date.strip(), dayfirst=True).date() + timedelta(days=1),
self.account,
amount.Amount(D(amt), 'CHF'),
None,
None,
)
def extract_balance(self, file, counter):
entries = []
meta = data.new_metadata(file.name, next(counter))
for bal in self.get_balance_statement():
if bal:
balance_entry = data.Balance(meta, bal.date, self.config['main_account'],
amount.Amount(bal.amount, self.currency),
None, None)
entries.append(balance_entry)
return entries
def CreateBalance(api, accountId) -> data.Balance:
"""Create a Balance directive for the account."""
acc = api.GetAccount(accountId=accountId)
balances = acc['securitiesAccount']['currentBalances']
mmfund = balances['moneyMarketFund']
shorts = balances['shortBalance']
amt = mmfund + shorts
fileloc = data.new_metadata('<ameritrade>', 0)
date = datetime.date.today()
return data.Balance(fileloc, date, config['asset_cash'],
Amount(D(amt).quantize(Q), USD), None, None)
def balance(file, account_name, currency, statement, ledger):
if len(ledger) == 0:
return None
# Use the last transaction date as the balance assertion date
# (because the pending transactions will post in-between)
date = ledger[-1].date
# The Balance assertion occurs at the beginning of the date, so move
# it to the following day.
date += timedelta(days=1)
units = Amount(statement.balance, currency)
ref = data.new_metadata(file.name, 0)
return data.Balance(ref, date, account_name, units, None, None)
def extract_balance(self, file, counter):
# date = self.ofx_account.statement.balance_date
date = max(ot.tradeDate if hasattr(ot, 'tradeDate') else ot.date
for ot in self.get_transactions()).date()
# balance assertions are evaluated at the beginning of the date, so move it to the following day
date += datetime.timedelta(days=1)
meta = data.new_metadata(file.name, next(counter))
balance_entry = data.Balance(
meta, date, self.config['main_account'],
amount.Amount(self.ofx_account.statement.balance, self.currency),
None, None)
return [balance_entry]
def extract(soup, filename, acctid_regexp, account, flag, balance_type):
"""Extract transactions from an OFX file.
Args:
soup: A BeautifulSoup root node.
acctid_regexp: A regular expression string matching the account we're interested in.
account: An account string onto which to post the amounts found in the file.
flag: A single-character string.
balance_type: An enum of type BalanceType.
Returns:
A sorted list of entries.
"""
new_entries = []
counter = itertools.count()
for acctid, currency, transactions, balance in find_statement_transactions(
soup):
if not re.match(acctid_regexp, acctid):
continue
# Create Transaction directives.
stmt_entries = []
for stmttrn in transactions:
entry = build_transaction(stmttrn, flag, account, currency)
entry = entry._replace(
meta=data.new_metadata(filename, next(counter)))
stmt_entries.append(entry)
stmt_entries = data.sorted(stmt_entries)
new_entries.extend(stmt_entries)
# Create a Balance directive.
if balance and balance_type is not BalanceType.NONE:
date, number = balance
if balance_type is BalanceType.LAST and stmt_entries:
date = stmt_entries[-1].date
# The Balance assertion occurs at the beginning of the date, so move
# it to the following day.
date += datetime.timedelta(days=1)
meta = data.new_metadata(filename, next(counter))
balance_entry = data.Balance(meta, date, account,
amount.Amount(number, currency), None,
None)
new_entries.append(balance_entry)
return data.sorted(new_entries)
def json_to_entry(json_entry, valid_accounts):
"""Parse JSON to a Beancount entry."""
# pylint: disable=not-callable
date = util.date.parse_date(json_entry['date'])[0]
if json_entry['type'] == 'transaction':
narration, tags, links = extract_tags_links(json_entry['narration'])
txn = data.Transaction(json_entry['metadata'], date,
json_entry['flag'], json_entry['payee'],
narration, tags, links, [])
if not json_entry.get('postings'):
raise FavaAPIException('Transaction contains no postings.')
for posting in json_entry['postings']:
if posting['account'] not in valid_accounts:
raise FavaAPIException('Unknown account: {}.'.format(
posting['account']))
data.create_simple_posting(txn, posting['account'],
posting.get('number') or None,
posting.get('currency'))
return txn
elif json_entry['type'] == 'balance':
if json_entry['account'] not in valid_accounts:
raise FavaAPIException('Unknown account: {}.'.format(
json_entry['account']))
number = D(json_entry['number'])
amount = Amount(number, json_entry.get('currency'))
return data.Balance(json_entry['metadata'], date,
json_entry['account'], amount, None, None)
elif json_entry['type'] == 'note':
if json_entry['account'] not in valid_accounts:
raise FavaAPIException('Unknown account: {}.'.format(
json_entry['account']))
if '"' in json_entry['comment']:
raise FavaAPIException('Note contains double-quotes (")')
return data.Note(json_entry['metadata'], date, json_entry['account'],
json_entry['comment'])
else:
raise FavaAPIException('Unsupported entry type.')
def get_member(self, id, name, date, balance):
#name = member_account(name)
name = self.accounts_by_id[id]
if id in self.accounts_by_id and self.accounts_by_id[id] != name:
# Generate rename
txn = bcdata.Transaction(meta={},
date=date,
flag="txn",
payee="",
narration="Rename %(from)s to %(to)s" % {
"from": self.accounts_by_id[id],
"to": name,
},
tags=set("rename"),
links=set(),
postings=[])
bcdata.create_simple_posting(txn, self.accounts_by_id[id], balance,
"EUR")
bcdata.create_simple_posting(txn, name, -balance, "EUR")
self.entries.append(txn)
if self.accounts_by_id[id] not in DONT_CLOSE:
self.entries.append(
bcdata.Close(
meta={},
date=date + datetime.timedelta(days=1),
account=self.accounts_by_id[id],
))
# Disable the balance assertion for the next day
self.last_assertion[name] = date
self.initial_balances[name] = None
self.accounts_by_id[id] = name
elif name not in self.initial_balances:
self.initial_balances[name] = balance
self.last_assertion[name] = date
self.accounts_by_id[id] = name
elif self.last_assertion.get(
name, None) != date and name != "Assets:Cash:Bar":
self.entries.append(
bcdata.Balance({"iline": str(self.line)}, date, name,
bcdata.Amount(-balance, "EUR"), None, None))
self.last_assertion[name] = date
return name
def extract(self, file, existing_entries):
csvfile = open(file=file.name, encoding='windows_1252')
reader = csv.reader(csvfile, delimiter=';')
meta = data.new_metadata(file.name, 0)
entries = []
for row in reader:
try:
book_date, text, credit, debit, val_date, balance = tuple(row)
book_date = datetime.strptime(book_date, '%Y-%m-%d').date()
if credit:
amount = data.Amount(Decimal(credit), self.currency)
elif debit:
amount = data.Amount(Decimal(debit), self.currency)
else:
amount = None
if balance:
balance = data.Amount(Decimal(balance), self.currency)
else:
balance = None
except Exception as e:
logging.debug(e)
else:
logging.debug((book_date, text, amount, val_date, balance))
posting = data.Posting(self.account, amount, None, None, None,
None)
entry = data.Transaction(meta, book_date, '*', '', text,
data.EMPTY_SET, data.EMPTY_SET,
[posting])
entries.append(entry)
# only add balance on SOM
book_date = book_date + timedelta(days=1)
if balance and book_date.day == 1:
entry = data.Balance(meta, book_date, self.account,
balance, None, None)
entries.append(entry)
csvfile.close()
entries = data.sorted(entries)
return entries
def extract_prices(self,
statement: FlexStatement,
existing_entries: list = None):
"""
IBFlex XML Files can contain an object called 'OpenPositions',
this is very useful because it lets us create
- Balance assertions
- Price entries from the Mark
"""
result = []
for position in statement.OpenPositions:
price = position.markPrice
safe_symbol = self.clean_symbol(position.symbol)
# Dates are 12 Midnight, let's make it the next day
date = statement.toDate + timedelta(days=1)
result.append(
# TODO De-Duplicate
data.Price(currency=safe_symbol,
amount=data.Amount(price, "USD"),
date=date,
meta={
'lineno': 0,
'filename': '',
}))
account = self.account_for_symbol(statement, position.symbol)
result.append(
data.Balance(
account=account,
amount=data.Amount(position.position * position.multiplier,
safe_symbol),
date=statement.toDate + timedelta(days=1),
meta={
'lineno': 0,
'filename': ''
},
tolerance=0.5,
diff_amount=0,
))
return result
def deserialise(json_entry):
"""Parse JSON to a Beancount entry.
Args:
json_entry: The entry.
Raises:
KeyError: if one of the required entry fields is missing.
FavaAPIException: if the type of the given entry is not supported.
"""
if json_entry["type"] == "Transaction":
date = util.date.parse_date(json_entry["date"])[0]
narration, tags, links = extract_tags_links(json_entry["narration"])
postings = [deserialise_posting(pos) for pos in json_entry["postings"]]
return data.Transaction(
json_entry["meta"],
date,
json_entry.get("flag", ""),
json_entry.get("payee", ""),
narration,
tags,
links,
postings,
)
if json_entry["type"] == "Balance":
date = util.date.parse_date(json_entry["date"])[0]
raw_amount = json_entry["amount"]
amount = Amount(D(str(raw_amount["number"])), raw_amount["currency"])
return data.Balance(
json_entry["meta"], date, json_entry["account"], amount, None, None
)
if json_entry["type"] == "Note":
date = util.date.parse_date(json_entry["date"])[0]
comment = json_entry["comment"].replace('"', "")
return data.Note(
json_entry["meta"], date, json_entry["account"], comment
)
raise FavaAPIException("Unsupported entry type.")
def deserialise(json_entry):
"""Parse JSON to a Beancount entry.
Args:
json_entry: The entry.
Raises:
KeyError: if one of the required entry fields is missing.
FavaAPIException: if the type of the given entry is not supported.
"""
# pylint: disable=not-callable
if json_entry['type'] == 'Transaction':
date = util.date.parse_date(json_entry['date'])[0]
narration, tags, links = extract_tags_links(json_entry['narration'])
txn = data.Transaction(json_entry['meta'], date, json_entry['flag'],
json_entry['payee'], narration, tags, links, [])
for posting in json_entry['postings']:
data.create_simple_posting(txn, posting['account'],
parse_number(posting.get('number')),
posting.get('currency'))
return txn
elif json_entry['type'] == 'Balance':
date = util.date.parse_date(json_entry['date'])[0]
number = parse_number(json_entry['number'])
amount = Amount(number, json_entry['currency'])
return data.Balance(json_entry['meta'], date, json_entry['account'],
amount, None, None)
elif json_entry['type'] == 'Note':
date = util.date.parse_date(json_entry['date'])[0]
comment = json_entry['comment'].replace('"', '')
return data.Note(json_entry['meta'], date, json_entry['account'],
comment)
else:
raise FavaAPIException('Unsupported entry type.')
def extract(self, file_):
entries = []
line_index = 0
closing_balance_index = -1
with open(file_.name, encoding=self.file_encoding) as fd:
# Header
line = fd.readline().strip()
line_index += 1
if not self.is_valid_header(line):
raise InvalidFormatError()
# Empty line
line = fd.readline().strip()
line_index += 1
if line:
raise InvalidFormatError()
# Meta
expected_keys = set(['Von:', 'Bis:', 'Saldo:', 'Datum:'])
lines = [fd.readline().strip() for _ in range(len(expected_keys))]
reader = csv.reader(
lines, delimiter=';', quoting=csv.QUOTE_MINIMAL, quotechar='"'
)
for line in reader:
key, value, _ = line
line_index += 1
if key.startswith('Von'):
self._date_from = datetime.strptime(
value, '%d.%m.%Y'
).date()
elif key.startswith('Bis'):
self._date_to = datetime.strptime(value, '%d.%m.%Y').date()
elif key.startswith('Saldo'):
self._balance_amount = Amount(
Decimal(value.rstrip(' EUR')), self.currency
)
closing_balance_index = line_index
elif key.startswith('Datum'):
self._balance_date = datetime.strptime(
value, '%d.%m.%Y'
).date() + timedelta(days=1)
expected_keys.remove(key)
if expected_keys:
raise ValueError()
# Another empty line
line = fd.readline().strip()
line_index += 1
if line:
raise InvalidFormatError()
# Data entries
reader = csv.DictReader(
fd, delimiter=';', quoting=csv.QUOTE_MINIMAL, quotechar='"'
)
for index, line in enumerate(reader):
meta = data.new_metadata(file_.name, index)
amount = Amount(
fmt_number_de(line['Betrag (EUR)']), self.currency
)
date = datetime.strptime(line['Belegdatum'], '%d.%m.%Y').date()
description = line['Beschreibung']
postings = [
data.Posting(self.account, amount, None, None, None, None)
]
entries.append(
data.Transaction(
meta,
date,
self.FLAG,
None,
description,
data.EMPTY_SET,
data.EMPTY_SET,
postings,
)
)
# Closing Balance
meta = data.new_metadata(file_.name, closing_balance_index)
entries.append(
data.Balance(
meta,
self._balance_date,
self.account,
self._balance_amount,
None,
None,
)
)
return entries
def extract(self, file):
entries = []
# Normalize the configuration to fetch by index.
iconfig, has_header = normalize_config(self.config, file.head())
# Skip header, if one was detected.
reader = iter(csv.reader(open(file.name)))
if has_header:
next(reader)
def get(row, ftype):
return row[iconfig[ftype]] if ftype in iconfig else None
# Parse all the transactions.
first_row = last_row = None
for index, row in enumerate(reader, 1):
if not row:
continue
# If debugging, print out the rows.
if self.debug: print(row)
if first_row is None:
first_row = row
last_row = row
# Extract the data we need from the row, based on the configuration.
date = get(row, Col.DATE)
txn_date = get(row, Col.TXN_DATE)
payee = get(row, Col.PAYEE)
fields = filter(None, [
get(row, field)
for field in (Col.NARRATION1, Col.NARRATION2, Col.NARRATION3)
])
narration = ' -- '.join(fields)
tag = get(row, Col.TAG)
tags = {tag} if tag is not None else data.EMPTY_SET
# Create a transaction and add it to the list of new entries.
meta = data.new_metadata(file.name, index)
if txn_date is not None:
meta['txndate'] = parse_date_liberally(txn_date)
date = parse_date_liberally(date)
txn = data.Transaction(meta, date, self.FLAG, payee, narration,
tags, data.EMPTY_SET, [])
entries.append(txn)
amount_debit, amount_credit = get_amounts(iconfig, row)
for amount in [amount_debit, amount_credit]:
if amount is None:
continue
units = Amount(amount, self.currency)
txn.postings.append(
data.Posting(self.account, units, None, None, None, None))
# Figure out if the file is in ascending or descending order.
first_date = parse_date_liberally(get(first_row, Col.DATE))
last_date = parse_date_liberally(get(last_row, Col.DATE))
is_ascending = first_date < last_date
# Parse the final balance.
if Col.BALANCE in iconfig:
# Choose between the first or the last row based on the date.
row = last_row if is_ascending else first_row
date = parse_date_liberally(get(
row, Col.DATE)) + datetime.timedelta(days=1)
balance = D(get(row, Col.BALANCE))
meta = data.new_metadata(file.name, index)
entries.append(
data.Balance(meta, date, self.account,
Amount(balance, self.currency), None, None))
return entries
def extract(self, f, existing_entries=None):
entries = []
row = None
row_date = None
with open_file(f) as fd:
rd = csv.reader(fd, dialect="ccm")
header = True
line_index = 0
for row in rd:
# Check header
if header:
if set(row) != set(FIELDS):
raise InvalidFormatError()
header = False
line_index += 1
continue
if len(row) != 6:
continue
# Extract data
row_date = datetime.strptime(row[0], "%d/%m/%Y")
label = row[4]
txn_amount = row[2]
if txn_amount == '':
txn_amount = row[3]
txn_amount = parse_amount(txn_amount)
# Prepare the transaction
meta = data.new_metadata(f.name, line_index)
txn = data.Transaction(
meta=meta,
date=row_date.date(),
flag=flags.FLAG_OKAY,
payee="",
narration=label,
tags=set(),
links=set(),
postings=[],
)
# Create the postings.
first_posting = make_posting(self.checking_account, txn_amount)
txn.postings.append(first_posting)
# Done
entries.append(txn)
line_index += 1
if line_index > 0:
balance_check = data.Balance(
meta=data.new_metadata(f.name, line_index + 1),
date=row_date.date() + timedelta(days=1),
account=self.checking_account,
amount=parse_amount(row[5]),
diff_amount=None,
tolerance=None,
)
entries.append(balance_check)
return entries
def extract(self, file_):
entries = []
line_index = 0
closing_balance_index = -1
with open(file_.name, encoding=self.file_encoding) as fd:
# Header
line = fd.readline().strip()
line_index += 1
if not self._expected_header_regex.match(line):
raise InvalidFormatError()
# (sometimes) Empty line
line = fd.readline().strip()
line_index += 1
# Meta
lines = [fd.readline().strip() for _ in range(3)]
reader = csv.reader(lines,
delimiter=';',
quoting=csv.QUOTE_MINIMAL,
quotechar='"')
for line in reader:
key = line[0]
value = line[1]
line_index += 1
if key.startswith('Von'):
self._date_from = datetime.strptime(value,
'%d.%m.%Y').date()
elif key.startswith('Bis'):
self._date_to = datetime.strptime(value, '%d.%m.%Y').date()
elif key.startswith('Kontostand vom'):
# Beancount expects the balance amount to be from the
# beginning of the day, while the Tagessaldo entries in
# the DKB exports seem to be from the end of the day.
# So when setting the balance date, we add a timedelta
# of 1 day to the original value to make the balance
# assertions work.
self._balance_amount = Amount(
fmt_number_de(value.rstrip(' EUR')), self.currency)
self._balance_date = datetime.strptime(
key.lstrip('Kontostand vom ').rstrip(':'),
'%d.%m.%Y').date() + timedelta(days=1)
closing_balance_index = line_index
# Another (sometimes) empty line
line = fd.readline().strip()
line_index += 1
# Data entries
reader = csv.DictReader(fd,
delimiter=';',
quoting=csv.QUOTE_MINIMAL,
quotechar='"')
for line in reader:
meta = data.new_metadata(file_.name, line_index)
amount = None
if line['Betrag (EUR)']:
amount = Amount(fmt_number_de(line['Betrag (EUR)']),
self.currency)
date = datetime.strptime(line['Buchungstag'],
'%d.%m.%Y').date()
if line['Verwendungszweck'] == 'Tagessaldo':
if amount:
entries.append(
data.Balance(
meta,
date + timedelta(days=1),
self.account,
amount,
None,
None,
))
else:
description = '{} {}'.format(line['Buchungstext'],
line['Verwendungszweck'])
postings = [
data.Posting(self.account, amount, None, None, None,
None)
]
entries.append(
data.Transaction(
meta,
date,
self.FLAG,
line['Auftraggeber / Begünstigter'],
description,
data.EMPTY_SET,
data.EMPTY_SET,
postings,
))
line_index += 1
# Closing Balance
meta = data.new_metadata(file_.name, closing_balance_index)
entries.append(
data.Balance(
meta,
self._balance_date,
self.account,
self._balance_amount,
None,
None,
))
return entries
def table_to_directives(table: petl.Table,
currency: str = 'USD') -> data.Entries:
"""Convert a petl table to Beancount directives.
This is intended as a convenience for many simple CSV importers. Your CSV
code uses petl to normalize the contents of an imported file to a table, and
this routine is called to actually translate that into directives.
Required columns of the input 'table' are:
date: A datetime.date instance, for the transaction.
account: An account string, for the posting.
amount: A Decimal instance, the number you want on the posting.
Optional columns are:
payee: A string, for the transaction's payee.
narration: A string, for the transaction's narration.
balance: A Decimal, the balance in the account *after* the given transaction.
other_account: An account string, for the remainder of the transaction.
"""
# Ensure the table is sorted in order to produce the final balance.
assert table.issorted('date')
assert set(table.fieldnames()) >= {'date', 'account', 'amount'}
columns = table.fieldnames()
metas = []
for column in columns:
match = re.match("meta:(.*)", column)
if match:
metas.append((column, match.group(1)))
# Create transactions.
entries = []
for index, rec in enumerate(table.records()):
meta = data.new_metadata(f"<{__file__}>".format, index)
units = amount.Amount(rec.amount, currency)
tags, links = set(), set()
txn = data.Transaction(
meta, rec.date, flags.FLAG_OKAY, getattr(rec, 'payee', None),
getattr(rec, 'narration', ""), tags, links,
[data.Posting(rec.account, units, None, None, None, None)])
if hasattr(rec, 'other_account') and rec.other_account:
txn.postings.append(
data.Posting(rec.other_account, None, None, None, None, None))
link = getattr(rec, 'link', None)
if link:
links.add(link)
tag = getattr(rec, 'tag', None)
if tag:
tags.add(tag)
for column, key in metas:
value = getattr(rec, column, None)
if value:
meta[key] = value
entries.append(txn)
if 'balance' in columns:
# Insert a balance with the final value.
meta = data.new_metadata(f"<{__file__}>", index + 1)
balance_date = rec.date + datetime.timedelta(days=1)
entries.append(
data.Balance(meta, balance_date, rec.account,
amount.Amount(rec.balance, currency), None, None))
return entries
