Beancount advancement Thread

MrCheese · August 6, 2020, 1:23pm

Thank you that worked.

nugget · August 8, 2020, 9:19am

Dear all,
I just started scripting a little bit I have so many questions! would anyone be up for another hangout? potentially with beer/ coffee in ZH?

First question:

I want to python-script my monthly transaction to the broker, according to the following logic:
"Given my budgeted monthly expenses EX=CHF3000. Every month at day 26, check the balance BAL of Account:Giro. If more than EX, make transaction of BAL-EX from Account:Giro to Account:Giro:ToBroker

What do I have so far?
I managed to load all my data with

from beancount import loader
from beancount.parser import printer

filename = "main.bean"
entries, errors, options = loader.load_file(filename)

and i know i can query the balance with

SELECT account, last(balance), last(date)
WHERE   account = 'Assets:Giro'

the timing i will do with cronjob.

now, how would i get the BQL query info into my python workspace?

[edit]

self-answer:

https://beancount.github.io/docs/api_reference/beancount.query.html#beancount.query.query.run_query

from beancount.query.query import run_query
query="SELECT account, last(balance) WHERE account = 'Assets:Giro'"
run_query(entries, options,query)

nugget · August 8, 2020, 10:01am

second question:

how can i get the latest balance?

SELECT account, balance,  max(date)
WHEREaccount = 'Assets:Giro'

will throw an error

TypeError: unhashable type: 'Inventory'

same with latest(), last(), etc
anybody knows?

tarioch · August 8, 2020, 10:28am

You can use the simple

balances

query.

As an alternative, what I often do is actually using sum queries (balance is nothing but the sum of all transactions until a certain point in time)

See https://beancount.github.io/docs/beancount_query_language.html for more details.

nugget · August 10, 2020, 10:44am

ok that fixed it
but the quest for the latest date came back to me. for examle, find the last time my salary account had any movement. the result should be a single transaction. i seem not to be able to use the last() , lastest() or max() functions on my date column. do you know the trick?

[edit]
ok i worked around by querying any date, and then taking the last entry from results:

entries, errors, options = loader.load_file(filename)
query = "select account, change, date where account = 'Income:Salary'""
result = run_query(entries, options, query, numberify=True)
result[1][-1][1]
>>Decimal('-50.00')

but still, do you know how i can apply those functions to the date column of the BQL query?

tarioch · August 10, 2020, 11:10am

Not sure what you’re running into, this seems to work fine for em

select last(date), account where account ~ 'Income:Salary'

nugget · August 10, 2020, 8:00pm

thanks! this works for me, too. however, adding the balance like

select last(date), balance, account where account = 'Income:Salary'

throws

Traceback (most recent call last):
  File "/home/user/miniconda3/envs/Bean/lib/python3.7/cmd.py", line 214, in onecmd
    func = getattr(self, 'do_' + cmd)
AttributeError: 'QueryShell' object has no attribute 'do_select'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/user/miniconda3/envs/Bean/lib/python3.7/site-packages/beancount/query/shell.py", line 271, in run_parser
    self.dispatch(statement)
  File "/home/user/miniconda3/envs/Bean/lib/python3.7/site-packages/beancount/query/shell.py", line 251, in dispatch
    return method(statement)
  File "/home/user/miniconda3/envs/Bean/lib/python3.7/site-packages/fava/core/query_shell.py", line 85, in on_Select
    c_query, self.entries, self.options_map
  File "/home/user/miniconda3/envs/Bean/lib/python3.7/site-packages/beancount/query/query_execute.py", line 327, in execute_query
    store = agg_store[row_key]
TypeError: unhashable type: 'Inventory'

do you have any idea why this won’t work? It should, no??

next question.1

SELECT account, balance, date
WHERE account = 'Assets:Giro'

works as far as i get a long list of balances for every date in the ledger. adding the line

AND date ='2020-07-25'

results in no output, but also no error. Then I see in the docs the FROM … CLOSE ON syntax. Trying

SELECT account, balance, date
From has_account("")

is not what i am looking for, but it compiles.

SELECT account, balance, date
From has_account("") 
CLOSE ON '2020-07-25'

returns
ERROR: Syntax error near '2020-07-25' (at 61) SELECT account, balance, date From has_account("") CLOSE ON '2020-07-25' ^

can someone explain to me the correct use of FROM … CLOSE ON?

next question.2

I have a child/ paret account structure of

Assets:Giro  ; total of CHF 1'000
Assets:Giro:Provisions ; CHF 200

Assuming my realy Giro account has a total 1’000 CHF, of which CHF 200 are provisions (for some future bills).

when I query the Assets:Giro account, I get CHF 800, which is the amount without the child account. When I look at it in Fava, I see CHF 1000, the combined sum. Both have their good purpose. Can i Query for both of them? How? I saw there is a leaf() function for BQL, but I could not yet get behind it.

Thanks so much in advance

tarioch · August 11, 2020, 7:25am

last() is an aggregation function, what you probably want is

select last(date), last(balance), account where account = 'Income:Salary'

For date queries, you need to convert it to date (it’s all described on https://beancount.github.io/docs/beancount_query_language.html)

select account, balance, date where account = 'Assets:Giro' and date = #'2020-07-25'

Also for the “from close on” stuff I would read the (decent) documentation https://beancount.github.io/docs/beancount_query_language.html

As for aggregation accross child/parents. There’s two ways I’m tackling this (depending on what I need). If I’m only interested on the parent level, I’m normally doing a sum() query and use a regexp for the account. If I’m interested in a tree structure, I build the roll ups afterwards by programming it out.

Beisat · August 31, 2020, 2:44pm

Thanks for those instructions @tarioch Much clearer then yegle’s own instructions.
Worth noting that fava also contains a web editor, so I guess I will try using that on Windows instead of worrying about emacs / spacemacs / vim / atom etc. for a start.

MrCheese · September 1, 2020, 2:19pm

Any idea how to add the TSLA stock split as a beancount transaction?

tarioch · September 1, 2020, 2:57pm

https://beancount.github.io/docs/trading_with_beancount.html

MrCheese · September 1, 2020, 7:14pm

Yep that worked, thanks.

Beisat · September 1, 2020, 7:38pm

So I got beancount / fava running in docker, main.beancount configured and temporally added
2020-01-01 custom “fava-option” “import-config” “import/import.config”
2020-01-01 custom “fava-option” “import-dirs” “import/import-dirs/”

Does anybody have a good example of how to now setup the import.config correctly and load the CashbackDuo importer?

I somehow have a feeling this is as far as one can get without knowing (at least some) python, when looking at the files / importers linked above and reading the manual:/

Thx

nugget · September 2, 2020, 5:57am

@Beisat

I’d make the claim that this most likely true. There might be exceptions, but without abit of debugging you will have a very hard time understanding how they work. If you lke I can attempt to show you => PM me

I made very good experience with VSCode on Windows for the beancount stuff. Syntax Highlight is very nice, auto-completion of accounts work very good and you have all the Tools of an IDE-Text-Editor. For example, you want to rename an account: you can search & replace an account name across all files of your ledger at once.

CryingSofa · September 2, 2020, 7:14am

Yes, VSCode is great. Don’t forget about setting multiple cursors. Saved my butt more than once…

For the ones new to IDEs/VSCode: Ctrl+Shift+P brings up the search over all things VSCode can do (hint: more than you can imagine). Use it a lot when you start out!

Beisat · September 2, 2020, 8:15am

Thanks for the VSCode tip! Will look into it. I see it also can do Python with a Plug-in, so perhaps will also try that

nugget · September 2, 2020, 3:50pm

yes, there is also a python plugin, and not too bad one
mind for the beancount plugin to work with VSCode, i had to do some specific tinkering described here

CryingSofa · September 2, 2020, 4:50pm

If you only care about syntax highlighting, the ‘hledger-vscode’ plugin works out of the box.

Beisat · September 4, 2020, 12:15pm

Got VSCode working, that already makes it all a bit better. Can I somehow have it refactor the file so it fits the suggested indentation perfectly? E.g. I only have a simple space before the booking value currently.

Does anybody else have the issue that under balance sheet / net worth the EUR and USD accounts don’t show a value? How do I give them a temporary CHF balance so they get added to my net worth?

MrCheese · September 4, 2020, 8:51pm

You have to have updated prices entries for the conversion to be working (has to be from the same date as you looking at, I added at least one pricing entry per month per non CHF asset.