Beancount advancement Thread

nugget · March 4, 2020, 10:35pm

Hey all,
i finally got the bean-import working. At least for one of my banks, and the others are going to follow once i have time to do the code. If anyone needs help with this, I can now provide

bext challange: smart importer! anyone got this working? i could need some help^^

tarioch · March 5, 2020, 6:22am

What are you struggling with?

See https://github.com/beancount/smart_importer/blob/master/README.rst

What I do is to only apply smart importer in my main.import config. E.g. for postings

from your_custom_importer import MyBankImporter
from smart_importer import apply_hooks, PredictPostings

my_bank_importer = MyBankImporter(‘whatever’, ‘config’, ‘is’, ‘needed’)
apply_hooks(my_bank_importer, [PredictPostings()])

CONFIG = [
my_bank_importer,
]

Are you using fava for importing or run on the cmdline? If you run on cli you need to provide your existing beancount file with the -e flaf

nugget · March 5, 2020, 10:46am

hey @tarioch ,
thanks for asking, really appreciated! I am not sure if this is the right platform (i saw your github contribution to the smart importer…) I am solely interested in the postings prediction (= second account required to balance the transaction)

I think i followed the readme instructions in detail, and i have a working minimal example with all involved files available here. I start with my working non-smart-baseline:

 (base) @:~/importers$ bean-extract Config.py bankstatement.csv 
2017-01-02 * "randomtext" "Lastschrift randomtext52"
  Assets:Liq:DKB:EUR  -990.85 EUR

2017-01-02 * "randomtext" "Lastschrift randomtext53"
  Assets:Liq:DKB:EUR  -635.16 EUR
[...]

nice, it does the job. of course, the importer does not attempt to assign the target accounts.
now, modyfying only the config file applying what is stated in the readme of the github:

(base) @:~/importers$ bean-extract Config_smart.py bankstatement.csv 
ERROR:root:Importer DKB ECImporter.extract() raised an unexpected error: extract() takes 2 positional arguments but 3 were given
ERROR:root:Traceback: Traceback (most recent call last):
  File "/home/anaconda3/lib/python3.7/site-packages/beancount/ingest/extract.py", line 191, in extract
    allow_none_for_tags_and_links=allow_none_for_tags_and_links)
  File "/home/anaconda3/lib/python3.7/site-packages/beancount/ingest/extract.py", line 69, in extract_from_file
    new_entries = importer.extract(file, **kwargs)
  File "/home/anaconda3/lib/python3.7/site-packages/smart_importer/hooks.py", line 41, in patched_extract_method
    imported_entries = unpatched_extract(file, existing_entries)
TypeError: extract() takes 2 positional arguments but 3 were given
;; -*- mode: beancount -*-

where the line imported_entries = unpatched_extract(file, existing_entries) conflicts with the definition of the extract function of the importer : def extract(self, file_):
here is where I am stuck for the moment. would i have to update the beancount-dkb ec importer’s extract function accordingly?

config.py:

    from beancount_dkb import ECImporter, CreditImporter
    from smart_importer import apply_hooks, PredictPayees, PredictPostings
    IBAN_NUMBER = 'DE23420304575000845261795' # your real IBAN number

    DKBEC=ECImporter(
            IBAN_NUMBER,
            'Assets:Liq:DKB:EUR',
            currency='EUR',
            file_encoding='UTF-8',
        )

    # apply_hooks(DKBEC, [PredictPostings()])    # for smart, uncomment his line
    CONFIG = [DKBEC]

tarioch · March 5, 2020, 4:23pm

Can you add -e yourexisting.beancount to the bean-extract call? Smart importer needs access to the existing trx for learning.

tarioch · March 5, 2020, 5:46pm

You also need to use the full extract interface which actually contains one more argument, so it should be

def extract(self, file, existing_entries):

even if you don’t need the existing_entries

nugget · March 5, 2020, 10:09pm

nice! i think i got it working,

although i have no clue what changed.

I have one more question: now that i have a file with the output of smart_importer, what is the workflow to add this to my ledger? just append to my ledger and manually text-edit those entries that are wrongly predicted?

I see you have a SVM going in there… i recently got introduced into the topic. I’d be interested in what is the rationale behind picking an svm vs. other methods

[edit] besides all the transactions, I also have some other output mixed in, such as

;; -*- mode: beancount -*-
**** /home//importers/bankstatement.csv

is it intended that i clean this up with just another regex call? or is there a clean built-in version?

tarioch · March 6, 2020, 5:43am

Add

from beancount.ingest import extract

extract.HEADER = ‘’

To your import config to avoid that noise.

I actually use fava for importing, fava will show me a dialog with the entries that are imported and then allows me to edit them before saving.

nugget · March 6, 2020, 11:51am

brilliant, i got it to work!
didnt have time to explore it in depth, but fava does give me the import dialogs. let’s see if I leke it more than text editor.

so, to wrap up “major” things i had to puzzle out besides rtfm:

importer: the beancount-DKB importer from the web seemed to have slight missmatches with the format of my bankstatements. this included added / different characters in the head line and "empty lines that are not empty in my statements. but now i know how the importer works and i will come up with one for postfinance soon. revolut and IB shall follow. also, i had to add the existing_entries=None argument to the definition of the extract() function of both DKB importers.
lots of the hassle went into me not having a clean python environment, and modules being loaded and not found from all over my file system. getting that into order helped significantly (conda…)
running the examples from https://github.com/beancount/beancount/tree/master/examples/ingest/office helped alot understanding and making sense of command line output.

tarioch · March 6, 2020, 12:02pm

See also my importers on

I have importers and other plugins packaged as a pypi package. So you can simply do

pip install tariochbctools

and use the importers. Amongst others there’s already ones for revolut and dividends on interactive broker.

nugget · April 6, 2020, 4:48pm

would there be anyone who could explain the cost/price issue in conjunction with selling assets to me? i already spent an aweful amount on time on this, but i can’t get behind it.

this minimal example will thrown an error “No position matches”. can you help me? Thanks!

option "operating_currency" "CHF"
option "title" "My test Ledger"
plugin "beancount.plugins.implicit_prices"

1970-01-01 open Assets:Invest:IB:USD  USD
1970-01-01 open Assets:Invest:IB:VTI:VTI     VTI
1970-01-01 open Expenses:Invest:IB:Fees:USD  USD

2019-08-01 * "VTI" "sell -10 VTI @ 15 USD"
  Assets:Invest:IB:VTI:VTI         -10 VTI {15.00 USD}
  Assets:Invest:IB:USD                        150.00 USD             

2019-07-01 * "VTI" "buy 6 VTI @ 11 USD"
  Assets:Invest:IB:VTI:VTI            6 VTI {11.00 USD}
  Assets:Invest:IB:USD                       -66.00 USD             

2019-06-01 * "VTI" "buy 4 VTI @ 8 USD"
  Assets:Invest:IB:VTI:VTI            4 VTI {8.00 USD}
  Assets:Invest:IB:USD                       -32.00 USD

Bojack · April 6, 2020, 5:06pm

2016-10-07 * "" "Shares VUSA:xswx"
  Assets:CornerTrader:CHF                         -40208.27 CHF
  Assets:CornerTrader:VUSA                             1000 VUSA {40.10 CHF, 2016-10-11}
  Expenses:Fees                                       48.12 CHF
  Expenses:Tax:StampDuty                              60.15 CHF

2016-10-07 * "" "Shares VEUR:xswx"
  Assets:CornerTrader:CHF                         -20670.66 CHF
  Assets:CornerTrader:VEUR                              700 VEUR {29.45 CHF, 2016-10-11}
  Expenses:Fees                                       24.74 CHF
  Expenses:Tax:StampDuty                              30.92 CHF

2016-10-21 * "" "Shares VUSA:xswx"
  Assets:CornerTrader:CHF                         -19053.40 CHF
  Assets:CornerTrader:VUSA                              470 VUSA {40.43 CHF, 2016-10-25}
  Expenses:Fees                                       22.80 CHF
  Expenses:Tax:StampDuty                              28.50 CHF

2018-07-11 * "" "Shares VUSA:xswx"
  Assets:CornerTrader:CHF                          95614.84 CHF
  Assets:CornerTrader:VUSA                            -1000 VUSA {40.10 CHF, 2016-10-11} @ 52.39 CHF
  Assets:CornerTrader:VUSA                             -470 VUSA {40.43 CHF, 2016-10-25} @ 52.39 CHF
  Assets:CornerTrader:VUSA                             -360 VUSA {42.69 CHF, 2016-11-28} @ 52.39 CHF
  Expenses:Fees                                      115.05 CHF
  Expenses:Tax:StampDuty                             143.81 CHF
  Income:CornerTrader:Gains

nugget · April 6, 2020, 5:11pm

would you kindly share whether you enter these lot dates & prices manually or by script? if so, can you share it?

Bojack · April 6, 2020, 5:12pm

i did it by hand. or precisely, via a manual excel conversion

Gesk · April 6, 2020, 6:19pm

If you are selling all of them you can also write this:

  Assets:US:VT                                          -111 VT {} @ 111.11 USD

tarioch · April 6, 2020, 6:32pm

You can also use something like lifo or fifo strategy and then you don’t need to specify which ones to reduce. See

Tino · April 6, 2020, 7:55pm

You are trying to sell 10 VTI that where bought at 15.00 USD. You don’t have any. You have only 6 at 11$ and 4 at 8$. You should use:

-10 VTI {} @15.00 USD

More complex, if you want to sell 7 at 15$ you must specify which one are you selling:

-6 VTI {11.00 USD} @15.00 USD
-1 VTI {8.00 USD} @15.00 USD

Or other combination. You can also say the ones bought at one date like did @Bojack.

After in the selling you must add a leg to put the gains or losses! If not is not going to match:

2019-08-01 * "VTI" "sell -10 VTI @ 15 USD"
  Assets:Invest:IB:VTI:VTI         -10 VTI {} @15.00 USD
  Assets:Invest:IB:USD                        150.00 USD   
  Income:Invest:VTI

Because you are selling 10 VTI with cost 98$ in the first line and putting in your account: 150$… you had 52 $ of gains that must be somewhere.

Regards.

nugget · April 7, 2020, 1:57am

YEY! My first somewhat-presentable open source publication!
so here is my new IBKR importer for beancount, with some documentation and code annotations:

it automatically formats IBKR flex queries to pretty much what @Bojack posted above.

happy scripting everyone!

my intent is to couple this with some chronjob to finally automate everything in the workflow besides cross-checking fresh beancount-statements and enjoying looking at the data!

and thanks all for your help, without it would have taken much longer

Tino · June 27, 2020, 9:48pm

Hi!

Sometimes you could need to make more complex searches or accounting. I would like to propose you a script to help to

export the ledge to a PostgreSQL database.

The file import_csv.sh do:

Delete (if exist) the old table with all the transactions
Generate the CSV from the current bean file
Create the table in the database
Import the data to the table
Erase all the spaces in front and behind all the string entries.

All the information and some examples for translate the bean-query to psql commands are here:

MrCheese · August 5, 2020, 2:54pm

I currently face an issue that fava can’t convert my prices of Bitcoin from USD to CHF. I guess because there are two conversion steps involved. Does anybody have a finanzen.ch bean-price module by any chance? Or how do you get your BTC prices?

tarioch · August 5, 2020, 3:25pm

See

There’s two price sources in there which allow you to fetch btc prices bitstamp and alphavantagefx

Also there is a plugin in there which dynamically calculates price in base ccy generate_base_ccy_prices(e.g. if you have a price for BTC in USD and the price for USD to CHF, then it will generate a price for BTC in CHF)