def test_create_duplicateprice(self, book_basic):
EUR = book_basic.commodities(namespace="CURRENCY")
USD = book_basic.currencies(mnemonic="USD")
p = Price(commodity=USD, currency=EUR, date=datetime(2014, 2, 22), value=Decimal('0.54321'))
p1 = Price(commodity=USD, currency=EUR, date=datetime(2014, 2, 22), value=Decimal('0.12345'))
book_basic.flush()
assert USD.prices.filter_by(value=Decimal('0')).all() == []
assert USD.prices.filter_by(value=Decimal('0.12345')).one() == p1
from sqlalchemy.orm.exc import NoResultFound
with pytest.raises(NoResultFound):
USD.prices.filter_by(value=Decimal('0.123')).one()
def test_create_basicprice(self, book_basic):
EUR = book_basic.commodities(namespace="CURRENCY")
USD = book_basic.currencies(mnemonic="USD")
p = Price(commodity=USD, currency=EUR, date=datetime(2014, 2, 22), value=Decimal('0.54321'))
# check price exist
np = USD.prices.first()
assert np is p
assert repr(p) == "Price<2014-02-22 : 0.54321 EUR/USD>"
p2 = Price(commodity=USD, currency=EUR, date=datetime(2014, 2, 21), value=Decimal('0.12345'))
book_basic.flush()
assert p.value + p2.value == Decimal("0.66666")
assert len(USD.prices.all()) == 2
def validate(self):
old = self.get_all_changes()
if old["STATE_CHANGES"][-1] == "deleted":
return
if '_quantity_num' in old or '_value_num' in old:
self.transaction._recalculate_balance = True
if self.transaction_guid is None:
raise GncValidationError(
"The split is not linked to a transaction")
if self.transaction.currency == self.account.commodity:
if self.quantity != self.value:
raise GncValidationError(
"The split has a quantity diffeerent from value "
"while the transaction currency and the account commodity is the same"
)
else:
if self.quantity is None:
raise GncValidationError(
"The split quantity is not defined while the split is on a commodity different from the transaction"
)
if self.quantity.is_signed() != self.value.is_signed():
raise GncValidationError(
"The split quantity has not the same sign as the split value"
)
# everything is fine, let us normalise the value with respect to the currency/commodity precisions
self._quantity_denom_basis = self.account.commodity_scu
self._value_denom_basis = self.transaction.currency.fraction
if self.transaction.currency != self.account.commodity:
# let us also add a Price
# TODO: check if price already exist at that tme
from piecash import Price
value = (self.value / self.quantity).quantize(Decimal("0.000001"))
try:
# find existing price if any
pr = self.book.prices(commodity=self.account.commodity,
currency=self.transaction.currency,
date=self.transaction.post_date,
type="transaction",
source="user:split-register")
pr.value = value
except KeyError:
pr = Price(commodity=self.account.commodity,
currency=self.transaction.currency,
date=self.transaction.post_date,
value=value,
type="transaction",
source="user:split-register")
# and an action if not yet defined
if self.action == "":
self.action = "Sell" if self.quantity.is_signed() else "Buy"
def test_create_duplicateprice(self, book_basic):
EUR = book_basic.commodities(namespace="CURRENCY")
USD = book_basic.currencies(mnemonic="USD")
p = Price(commodity=USD,
currency=EUR,
date=date(2014, 2, 22),
value=Decimal('0.54321'))
p1 = Price(commodity=USD,
currency=EUR,
date=date(2014, 2, 22),
value=Decimal('0.12345'))
book_basic.flush()
assert USD.prices.filter_by(value=Decimal('0')).all() == []
assert USD.prices.filter_by(value=Decimal('0.12345')).one() == p1
with pytest.raises(ValueError):
book_basic.validate()
def test_create_duplicateprice_different_source(self, book_basic):
EUR = book_basic.commodities(namespace="CURRENCY")
USD = book_basic.currencies(mnemonic="USD")
p = Price(commodity=USD,
currency=EUR,
date=date(2014, 2, 22),
value=Decimal('0.54321'),
source="user:price")
p1 = Price(commodity=USD,
currency=EUR,
date=date(2014, 2, 22),
value=Decimal('0.12345'),
source="other:price")
book_basic.flush()
assert USD.prices.filter_by(value=Decimal('0')).all() == []
assert USD.prices.filter_by(value=Decimal('0.12345')).one() == p1
# validation should work as different sources
book_basic.validate()
def main():
load_plugins()
parser = argparse.ArgumentParser()
parser.add_argument('-c',
'--config',
dest='configuration',
default='core.yaml',
help='core configuration details of the application')
args = parser.parse_args()
with open(args.configuration) as file_pointer:
configuration = load(file_pointer, Loader=Loader)
session = initialize(configuration['gnucash_file'], read_only=False)
configure_application(configuration.get('global', dict()))
# print "Value: %s" % (get_price_information('VGSTX'), )
for commodity in session.commodities:
if not commodity.quote_flag:
continue
if commodity.namespace == 'CURRENCY':
continue
quote_date, value = get_price_information(commodity.mnemonic)
if value is None:
continue
print 'Setting value of: %s to %s %s for date: %s' % (
commodity.mnemonic, value, currency.get_currency(), quote_date)
Price(currency=currency.get_currency(),
commodity=commodity,
date=datetime.combine(quote_date, datetime.max.time()),
value=Decimal(value),
source='Finance::Quote',
type='last')
session.save()
def import_fx_rates(self, rates: List[PriceModel]):
""" Imports the given prices into database. Write operation! """
have_new_rates = False
base_currency = self.get_default_currency()
for rate in rates:
assert isinstance(rate, PriceModel)
currency = self.get_by_symbol(rate.symbol)
amount = rate.value
# Do not import duplicate prices.
# todo: if the price differs, update it!
# exists_query = exists(rates_query)
has_rate = currency.prices.filter(Price.date == rate.datetime.date()).first()
# has_rate = (
# self.book.session.query(Price)
# .filter(Price.date == rate.date.date())
# .filter(Price.currency == currency)
# )
if not has_rate:
log(INFO, "Creating entry for %s, %s, %s, %s",
base_currency.mnemonic, currency.mnemonic, rate.datetime.date(), amount)
# Save the price in the exchange currency, not the default.
# Invert the rate in that case.
inverted_rate = 1 / amount
inverted_rate = inverted_rate.quantize(Decimal('.00000000'))
price = Price(commodity=currency,
currency=base_currency,
date=rate.datetime.date(),
value=str(inverted_rate))
have_new_rates = True
# Save the book after the prices have been created.
if have_new_rates:
log(INFO, "Saving new prices...")
self.book.flush()
self.book.save()
else:
log(INFO, "No prices imported.")
def __create_price_for(self, commodity: Commodity, price: PriceModel):
""" Creates a new Price entry in the book, for the given commodity """
logging.info("Adding a new price for %s, %s, %s", commodity.mnemonic,
price.datetime.strftime("%Y-%m-%d"), price.value)
# safety check. Compare currencies.
sec_svc = SecurityAggregate(self.book, commodity)
currency = sec_svc.get_currency()
if currency != price.currency:
raise ValueError(
"Requested currency does not match the currency previously used",
currency, price.currency)
# Description of the source field values:
# https://www.gnucash.org/docs/v2.6/C/gnucash-help/tool-price.html
new_price = Price(commodity,
currency,
price.datetime.date(),
price.value,
source="Finance::Quote")
commodity.prices.append(new_price)
def book_transactions(request):
name = request.param
if name and database_exists(name):
drop_database(name)
# create new book
with create_book(uri_conn=name, currency="EUR", keep_foreign_keys=False) as b:
# create some accounts
curr = b.default_currency
other_curr = b.currencies(mnemonic="USD")
cdty = Commodity(namespace=u"BEL20", mnemonic=u"GnuCash Inc.", fullname=u"GnuCash Inc. stock")
asset = Account(name="asset", type="ASSET", commodity=curr, parent=b.root_account)
foreign_asset = Account(name="foreign asset", type="ASSET", commodity=other_curr, parent=b.root_account)
stock = Account(name="broker", type="STOCK", commodity=cdty, parent=asset)
expense = Account(name="exp", type="EXPENSE", commodity=curr, parent=b.root_account)
income = Account(name="inc", type="INCOME", commodity=curr, parent=b.root_account)
tr1 = Transaction(
post_date=date(2015, 10, 21),
description="my revenue",
currency=curr,
splits=[Split(account=asset, value=(1000, 1)), Split(account=income, value=(-1000, 1))],
)
tr2 = Transaction(
post_date=date(2015, 10, 25),
description="my expense",
currency=curr,
splits=[
Split(account=asset, value=(-100, 1)),
Split(account=expense, value=(20, 1), memo="cost of X"),
Split(account=expense, value=(80, 1), memo="cost of Y"),
],
)
tr_stock = Transaction(
post_date=date(2015, 10, 29),
description="my purchase of stock",
currency=curr,
splits=[
Split(account=asset, value=(-200, 1)),
Split(account=expense, value=(15, 1), memo="transaction costs"),
Split(account=stock, value=(185, 1), quantity=(6, 1), memo="purchase of stock"),
],
)
tr_to_foreign = Transaction(
post_date=date(2015, 10, 30),
description="transfer to foreign asset",
currency=curr,
splits=[
Split(account=asset, value=(-200, 1)),
Split(account=foreign_asset, value=(200, 1), quantity=(135, 1)),
],
)
tr_from_foreign = Transaction(
post_date=date(2015, 10, 31),
description="transfer from foreign asset",
currency=other_curr,
splits=[
Split(account=asset, value=(135, 1), quantity=(215, 1)),
Split(account=foreign_asset, value=(-135, 1)),
],
)
Price(commodity=cdty, currency=other_curr, date=date(2015, 11, 1), value=(123, 100))
Price(commodity=cdty, currency=other_curr, date=date(2015, 11, 4), value=(127, 100))
Price(commodity=cdty, currency=curr, date=date(2015, 11, 2), value=(234, 100))
b.save()
yield b
if name and database_exists(name):
drop_database(name)
splits=[
Split(account=asset, value=(-200, 1)),
Split(account=foreign_asset, value=(200, 1), quantity=(135, 1)),
]
)
tr_from_foreign = Transaction(post_date=datetime(2015, 10, 31),
description="transfer from foreign asset",
currency=other_curr,
splits=[
Split(account=asset, value=(135, 1), quantity=(215, 1)),
Split(account=foreign_asset, value=(-135, 1)),
]
)
Price(commodity=cdty,
currency=other_curr,
date=datetime(2015, 11, 1),
value=(123, 100),
)
Price(commodity=cdty,
currency=other_curr,
date=datetime(2015, 11, 4),
value=(127, 100),
)
Price(commodity=cdty,
currency=curr,
date=datetime(2015, 11, 2),
value=(234, 100),
)
b.save()
# args.operation is the name of the import file (.csv).
if args.operation is None:
# export the prices
sys.stdout.write(
"date,type,value,value_num, value_denom, currency,commodity,source\n")
with piecash.open_book(args.gnucash_filename, open_if_lock=True) as book:
sys.stdout.writelines(
"{p.date:%Y-%m-%d},{p.type},{p.value},{p._value_num},{p._value_denom},{p.currency.mnemonic},{p.commodity.mnemonic},{p.source}\n"
.format(p=p) for p in book.prices)
else:
# import the prices
with piecash.open_book(args.gnucash_filename,
open_if_lock=True,
readonly=False) as book:
cdty = book.commodities
importFile = open(args.operation, 'r')
for l in csv.DictReader(importFile):
cur = cdty(mnemonic=l['currency'])
com = cdty(mnemonic=l['commodity'])
type = l.get('type', None)
date = datetime.strptime(l['date'], "%Y-%m-%d")
v = Decimal(l['value'])
Price(currency=cur,
commodity=com,
date=date,
value=v,
source="piecash-importer")
book.save()
# Currencies
fcurr = exportpd['Pos Cur'] != 'EUR'
assets = exportpd['Pot'] != 'LIQUIDITEITEN'
xrates_all = exportpd[fcurr & assets]
xrates = xrates_all[['Pos Cur', 'X Rate', 'Pricing Date']].copy()
xrates.drop_duplicates(inplace=True)
for index, row in xrates.iterrows():
date = datetime.strptime(row['Pricing Date'][:10], '%Y-%m-%d').date()
comm = book.get(Commodity, namespace="CURRENCY", mnemonic='EUR')
currency = row['Pos Cur']
price = row['X Rate']
if add_quote(comm, date, currency):
curr = book.commodities(mnemonic=currency)
logging.info(
f"Add Exchange Rate to {currency} {price} at {date:%d/%m/%Y}")
Price(comm, curr, date, str(price), type='last')
# Shares
groei = exportpd['Pot'] == 'GROEI'
assets = exportpd['Assets'] != 'Obligaties'
aandelen = exportpd[groei & assets]
for index, row in aandelen.iterrows():
date = datetime.strptime(row['Pricing Date'][:10], '%Y-%m-%d').date()
cusip = row['ISIN']
currency = row['Pos Cur']
price = row['Price']
try:
comm = book.commodities(cusip=cusip)
except KeyError:
logging.error(
f"Cannot find entry for {row['Instrument Name']} ISIN: {cusip}"
)
print("%-80s : %12.2f : %12.2f" %
("%s%s" % (tab, str(node)), node.get_balance(), balance))
for child in node.children:
rec_function(child, tab + ' ')
with open_book(book_file, readonly=False, do_backup=True) as book:
# with open_book(book_file, open_if_lock=True) as book:
print(book.default_currency)
for comm in book.commodities:
print(f"Mnemonic: {comm.mnemonic} ")
comm = book.commodities(cusip='LU1863263429')
curr = book.commodities(mnemonic='EUR')
print(comm.mnemonic)
p = Price(comm, curr, date(2020, 10, 12), "1302.44", type='last')
book.save()
"""
print(comm.mnemonic)
print(comm.currency.mnemonic)
for price in comm.prices:
print(f"Price date: {price.date} value: {price.value} {price.currency.mnemonic}/{price.commodity.mnemonic}")
# https://github.com/sdementen/piecash/issues/97 => insert price
for acc in book.accounts:
for sp in acc.splits:
print(sp.transaction.description)
# rec_function(book.root_account)
"""