search

beancount / fava

Fava - web interface for Beancount
https://beancount.github.io/fava/
MIT License
1.96k stars 286 forks source link

Balance Sheet at previous points in time #793

Closed g40matt closed 4 years ago

g40matt commented 6 years ago

Hi - new convert to fava/beancount from the quicken world. Loving it so far.

I noticed that when I pull an "At Market Value" balance sheet in fava and specify a date in the past in the filters, the USD accounts show the balance as of the specified date (as expected), however, accounts with commodities seem to show the current market value instead of the market value effective at the date the balance sheet is filtered for.

In my opinion, the "At Market Value" selector should show the market value as of the time of the balance sheet for commodity accounts.

g40matt commented 6 years ago

Messed around with this more using the entries below... seems like there's an issue displaying unrealized gains and possibly related to the time filter. I put together a short example and identified around 10 or so incorrect reports and charts in Fava.

Example and correct results:

Fava results: Balance Sheet report incorrect under the following scenarios:

  1. "At Market Value" selected, no filters: the "Net Worth" line chart shows $425 (vs $575 which is the correct current net worth). Unrealized gains appear to be excluded from the line chart. The Assets/Liabilities/Equity table below correctly shows $575 Asset and -$575 Equity. *Note, when the Time filter is used the line chart works correctly. A time filter of 2017-12-31 renders a net worth of $575.
  2. "At Market Value" selected, no filters: the "Equity" graphic shows "NaN" instead of -$150 unrealized gains and $425 Previous Earnings. Note, when the Time filter is used (Eg. set to 2017-12-31), the Equity graphic shows the $425 previous earnings but still does not show the -$150 unrealized gains.
  3. "At Cost" selected, "2017-12-31" time filter. The line chart shows an incorrect result of $575. Net worth at cost is the original $425 of income/cash (of which $50 was invested at cost into BBG). Net worth is therefore $375 cash + 100 shares of BBG at $0.50/share cost ($50) = $425.
  4. "At Market Value" selected, "2017-08-31" time filter. The Assets/Liabilities/Equity chart incorrectly shows USD assets of $575 and an unrealized gain of $150. [The line chart shows the correct net worth]. On Aug 31, the price directive from Aug 1 is in effect. Therefore, the net worth should be $425 ($375 cash + 100 shares of BBG at $1.25/share ($125) = $500.

Trial Balance report incorrect under the following scenarios:

  1. "At Market Value" selected, no filters. Table below does not show any Equity (eg. Earnings - Previous $425 or the Unrealized Gains of $150.) To note, Fava does represent the unrealized gains in the Assets column in green text. Note, when the time filter is used, the Equity table updates to show the Earnings-Previous ($425), but still does not show unrealized gains.
  2. "At Market Value" selected, "2017-08-30". Table values Beanbag stock at $200, which is incorrect. The Beanbag stock on Aug 30 should use the Aug 1 price directive of $1.25 ($1.25x 100 shares = $125). The unrealized gains are also omitted from the table. The chart above the table also shows the wrong numbers as well.

Account (Assets:Stock) report incorrect under the following scenarios:

  1. "At Market Value" or "At Cost" and no filters. The "Balances (monthly/quarterly/etc)" table only shows the month/quarter when the asset was purchased and three prior.
  2. "At Market Value", and a Time filter ("2017"). The "Balances (monthly) report shows July 2017 at $125. July 2017 should follow either the Jan 1 price directive ($0.50) or the purchase price ($0.50) x 100 shares = $50. The August 2017 correctly shows $125 (the Aug 1 price directive is $1.25 x 100 shares = $125). Note, I changed the price directives to be effective on the 2nd day of the month instead of the 1st day and the tables seem to work correctly. The Aug 1 price directive should not show in the July column, etc.!
  3. "At Market Value". The "Changes (monthly) tables do not show changes for unrealized gains. For example, the Aug 2017 change is +$75 (at market value, as the 8/1 price directive of $1.25 replaces the previous price directive of $0.50. Therefore the change at market value is $0.75 x 100 shares = $75.
  4. The graphs above the tables also are incorrect in a variety of these scenarios.

Interestingly, the "Holdings" report seems to show the correct values. most of the issues above seem to be related to each other, and since "Holdings" is correct, I hope it's an easy fix!

option "operating_currency" "USD" 2000-01-01 open Assets:Cash 2000-01-01 open Assets:Stock:Beanbag 2000-01-01 open Income:Wages 2017-04-01 "Paycheck" Assets:Cash 425.00 USD Income:Wages -425.00 USD 2017-07-01 "Buy 100 BBG" Assets:Stock:Beanbag 100 BBG { 0.50 USD} Assets:Cash -50 USD 2017-01-01 price BBG 0.50 USD 2017-08-01 price BBG 1.25 USD 2017-09-01 price BBG 2.00 USD

aumayr commented 6 years ago

Thanks @g40matt for the very detailed bug report!

Out of curiosity: Does bean-web show the correct numbers?

g40matt commented 5 years ago

Been away for a while. a few follow ups on my report from a few months ago. I've spent alot of time importing an old Quicken file and updating my accounts. I've got about 15 years of data loaded up, and almost 14,000 transactions. I'm still learning, but very impressed with this tool!

1) The total at the bottom of the Balance Sheet accounts appears to be using the most recent price directive even if "At Market Value" is selected. This creates a situation where the number next to "Assets", for example, in the Balance sheet, shows correctly at the market value as of the date selected in the time filter, but the total number for Assets at the bottom of the table is different. We probably dont even need the total at the bottom level of the table. Bean-Web has a similar problem.

2) I can't sort in the journal screens, even though it looks like sorting was added in an earlier release. Is sorting working properly?

2) Some of my issues noted in the earlier bug report appear to be a result of how Beancount handles Price directives. They're active the day AFTER the date of the directive. This is odd, because usually stock prices refer to closing prices on a given day. So if someone said "What are my stocks worth on 3-31", you need to actually pull up 4-1 (and hope you have no trades on 4-1). Alternatively we can load the opening prices on a given day on price directives, but thats also odd.

3) The "Change" tables don't update for unrealized gains, but the "Balance" tables do update (albeit with the funky price directive notation above in point 3).

4) There are a number of quirks in the line charts and tables, but none are particularly meaningful. (eg. equity almost never shows unrealized gains in the lines or tables).

5) The export tool doesn't appear to utilize the filters. No matter how I have my filters set to, if I click Export, I get all of my entries (a 64,000 row beancount file).

6) Looking forward to being able to export journals and reports to csv!

yagebu commented 5 years ago

Thanks for the once more detailed comments :) I'm addressing some of your points in #902: 1. Removed the totals from the trees, I didn't find them useful either. 2. and 6. were simple bugs that should be fixed.

The "Change" tables don't update for unrealized gains, but the "Balance" tables do update (albeit with the funky price directive notation above in point 3).

Never use those really, so they're quite low-priority for me. Fixes would be welcome :)

Looking forward to being able to export journals and reports to csv!

Well you already can if they're queries. For other export features a PR would be required since I'm not going to work on that anytime soon (or maybe ever)