Hi, I was hoping someone can help out with some queries I'm trying to write. I'm trying to get the market value in certain currency of all positions in some account. Closest I was able to get was from example page: http://aumayr.github.io/beancount-sql-queries/ SELECT account, units(sum(position)) AS quantity, cost(sum(position)) AS Book , convert(units(sum(position)), "CAD") AS Market FROM date > 2018-01-01 AND date < 2018-01-31 WHERE account ~ "Assets:Margin" GROUP BY account ORDER BY account
Which produces something like below where A is some stock. | account | quantity | book | market | | Assets:Margin:Cash | 1 CAD | 1 CAD | 1 CAD | | Assets:Margin:A | 100 A | 100 CAD | 105 CAD | I was hoping to get a sum of all positions - the balance. But can't figure out the query. Furthermore one of the posts suggests that convert needs a date otherwise latest price is used (https://groups.google.com/forum/#!msg/beancount/qA9IKJ408tg/OFsN8G_wFgAJ) even with date query. Is that still the case? For withdrawal/deposit query I tried this as a starting point without aggregation: SELECT account, position FROM date > 2018-01-01 AND date < 2018-01-31 WHERE account ~ "Assets:Margin:Cash" AND position > 0 However received a stack dump (beancount 2.2.1): Traceback (most recent call last): File "/usr/lib/python3.7/cmd.py", line 214, in onecmd func = getattr(self, 'do_' + cmd) AttributeError: 'BQLShell' object has no attribute 'do_SELECT' During handling of the above exception, another exception occurred: Traceback (most recent call last): File "/usr/lib/python3.7/site-packages/beancount/query/shell.py", line 271, in run_parser self.dispatch(statement) File "/usr/lib/python3.7/site-packages/beancount/query/shell.py", line 251, in dispatch return method(statement) File "/usr/lib/python3.7/site-packages/beancount/query/shell.py", line 419, in on_Select self.options_map) File "/usr/lib/python3.7/site-packages/beancount/query/query_execute.py", line 275, in execute_query if c_where is None or c_where(context): File "/usr/lib/python3.7/site-packages/beancount/query/query_compile.py", line 121, in __call__ return self.operator(self.left(context), self.right(context)) File "/usr/lib/python3.7/site-packages/beancount/query/query_compile.py", line 121, in __call__ return self.operator(self.left(context), self.right(context)) TypeError: '>' not supported between instances of 'Position' and 'int' The reason I'm working on these queries is I'd like to compute time valued rate of returns (example spreadsheet if someone's interested https://www.bogleheads.org/wiki/Calculating_personal_returns). If anyone has suggestions on different workflow with beancount, please let me know. Thank you. -- You received this message because you are subscribed to the Google Groups "Beancount" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/26a9f33e-7916-49a6-ba8c-23fd30a382da%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
