Help query market value balance on specific date, and total deposits/withdrawals for ROR calc

[igor.s...@gmail.com]

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.

[Justus Pendleton]

On Wednesday, February 6, 2019 at 10:38:05 AM UTC+7, igor.s...@gmail.com wrote:

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. 

FWIW, I posted a money-weighted returns calculator a few weeks ago.

https://groups.google.com/forum/#!topic/beancount/7GbNHLiN0mg

At the time, I was planning on also adding time-weighted returns but after thinking about it for a while, decided that it was somewhere between impossible & a fool's errand in beancount. The thing with time-weighted returns is you have to have equal-length periods of time. With beancount, that means you rely on the user to update the price database on exact dates. Every 30 days (not every month!). Every day. Or whatever. I don't do that and I'd be surprised if many people are actually that rigorous about it, especially with historical data that they imported into beancount.

Of course, you could just go for "good enough" and not care that the price database isn't fine grained enough to give you equal-length periods of time. I guess you could argue that it is "close enough" (but how far off is it? how do you know?)...but when combined with my general feeling that time-weighted returns are mostly meaningless for individual investors anyway....I ended up not implementing it.

[igor.s...@gmail.com]

Thank you I'll definitely take a look at your money weighted implementation.  

I have to admin I'm blindly trusting the spreadsheet, and I haven't checked the formulas.  The spreadsheet entries are not equal spaced and the unequal periods do not reflect exact date of deposit/withdrawal, so I don't know how far it's off.

[Martin Blais]

On Wed, Feb 6, 2019 at 9:47 AM Justus Pendleton <just...@gmail.com> wrote:

On Wednesday, February 6, 2019 at 10:38:05 AM UTC+7, igor.s...@gmail.com wrote:

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. 

FWIW, I posted a money-weighted returns calculator a few weeks ago.

https://groups.google.com/forum/#!topic/beancount/7GbNHLiN0mg

At the time, I was planning on also adding time-weighted returns but after thinking about it for a while, decided that it was somewhere between impossible & a fool's errand in beancount.

:-)

It's pretty gnarly indeed.

I gave it a shot here:

https://bitbucket.org/blais/beancount/src/tip/experiments/returns/returns.py

I backed out once I started debugging this. I made it too complicated.

But I think the categorization at the top - and it took me a while to come up with it - is sound.

The plan is to revisit one day (before retirement, damnit) and to rewrite simpler and more easily debuggable.

 

The thing with time-weighted returns is you have to have equal-length periods of time.

Not really. You just need to multiply the returns of each period ("geometric linkage").

Basically, whenever there is a cash inflow/outflow, you need the total value before and after.

Periods of return have the value at the beginning (after the flow) and at the end (before the next flow).

You compute the return over each period, multiply them together, then annualize.

 

With beancount, that means you rely on the user to update the price database on exact dates. Every 30 days (not every month!). Every day. Or whatever. I don't do that and I'd be surprised if many people are actually that rigorous about it, especially with historical data that they imported into beancount.

You could write a script that automatically computes the list of active assets and fetches the historical prices.

I once started hacking something like this, here:

https://bitbucket.org/blais/beancount/src/tip/experiments/prices/fetch-lifetime-prices.py

 

Of course, you could just go for "good enough" and not care that the price database isn't fine grained enough to give you equal-length periods of time. I guess you could argue that it is "close enough" (but how far off is it? how do you know?)...but when combined with my general feeling that time-weighted returns are mostly meaningless for individual investors anyway....I ended up not implementing it.

You know something that still continues to amaze me, is that no broker ever reports anything close to either of those.

You can get your initial / final value per period, but that doesn't take into account cash flows (investments), so it's useless.

You can also get the value of a set of fixed positions over periods, but that doesn't reflect your changing allocation over time.

You can get IRR, which is better, but same thing, doesn't show your true return against the market.

Basically, noone has a f' clue what their returns are... This blows my mind.

Totally worth solving properly.

 

--
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 beancount+...@googlegroups.com.
To post to this group, send email to bean...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/5081843f-dced-4df2-8c3f-24dc330fbe2b%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.