On Thu, Aug 30, 2018 at 11:38 AM Bill Harris <[email protected]> wrote:
> Martin, > > Thanks. In short (and in my words), I failed to associate assets > properly. When I added both dates and costs to assets, it worked. > > I figured it might be useful to create a "canonical" (for my current > purpose) file that covers what I think I want to do right now: track a set > of related investments through ISO acquisition, splits, spin-offs, and > sales. I've attached the file, and it seems to work. I thought I'd see if > you or others had suggestions of ways to make it more idiomatic Beancount > before I expanded it to my full situation, and I did have a couple of > questions: > > > - Do the options, commodities, and open sections look reasonable? I > think they do. > > Yes. Looks right to me. > > - It seems easy to think I should have a separate "basis" account for > each lot, but that's just the value of the asset (the stock). > > The basis is stored along with each lot, you don't need to account for it any other way. > - This example covers an employee stock purchase plan. As such, some > of the cost of acquisition was borne by the company and some by me. I > needed to initialize a cash account to pay for my shares, but I didn't want > to recreate my entire financial system as of the time, so I simply dumped > an arbitrary $10K into checking. > - Does this seem like an idiomatic way to do that? > > Yes, and no. The method you use is fine, but don't use an arbitrary amount for no reason, that might just be misleading. You can just leave it to zero, with the value of the account going negative (that's more obviously remind you you're not tracking it). > - Do you have a better suggestion? > > Or... choose a real date to start with, and set the balance - as you did - to the real account balance on that date, and there onwards start ingesting the transactions for that account. > > - From the Language Syntax manual, I thought padding might work, but, > if so, I obviously don't understand the pad command (see the commented > lines in "Make cash available"). What am I doing wrong? > > Balance entries are defined to apply first in each day (they're sorted like that). Your Pad entry must have a date before that. Make it on 1/4 and it'll work. Use this to confirm it: bean-query <filename> print > > - The remaining sections--Acquire initial shares, Transfer shares to > book-entry, Stock split, Stock sale, and Spin-off of AB--all seem to work, > so I presume they're okay, right? > > Looks right to me! > - That company also granted "free" "anniversary" shares. I presume I > would treat them the same way, except that the cost of those shares would > all be paid out of income from the company, right? > > I bet you their cost showed up on your W2. Yes. > > - For an ISO, > > huh-ho. I haven't dealt with ISOs in Beancount. (Take my answers with a grain of salt below.) There are some people on this list with ISO, maybe they can chip in with some real experience. I went out and read about those a bit; they look like zero-valued call options vested to you over time. Here's my limited understanding: - you are "granted" some total amount of options - these are "vested" to you over a schedule; they hvae zero value (you can't sell the options, there's no time value, only intrinsic) - if you exercise, you buy (with after tax money) the options; that's the cost basis, e.g. B. - when you exercise them, there's no tax consequence (if the stock at vesting time is worth Sv, you only pay B, and Sv - B is not taxable yet) - say later on, you want to sell those stock and the stock is worth St then - when you actually sell them, you're taxed a special variant of capital gains tax on St - B (Hopefully I got some of this right.) Beancount doesn't do anything special for taxes, it just allows you to track cost basis; it's up to you to know that account so-and-so is to be reported in some particular way. > > - there are two issues: > - Tracking the ISO before it's exercised: I don't have any open > ISOs, and so I can ignore that. > > I would definitely track those as they grant and vest. Do this by creating currencies. I do this myself for tracking shares from my employer (they're RSU, not ISO, but it still applies all the same). I want to know how much remains that's unvested, for instance. Create an an unvested currrency at zero price: 2013-01-28 commodity GOOG.UNVEST name: "Unvested award of Google Class C shares." export: "IGNORE" assetcls: "Unvested" strategy: "N/A" 2013-01-28 price GOOG.UNVEST 0 USD (Ignore the metadata, or if you're really curious check out how I use it to update a Google Sheets doc for investing: http://bitbucket.org/blais/beancount/src/tip/beancount/projects/export.py) Then I have accounts to keep track of total received awards and : 2013-01-28 open Income:US:GoogleInc:Awards GOOG.UNVEST 2014-01-28 open Expenses:GoogleInc:Vested GOOG.UNVEST Then if I receive a new award, that's deposited in a per-award account: 2017-03-20 open Assets:US:GoogleInc:Unvested:C123456 2017-03-20 * "Award C1234567 (for $XXXXX based on stock price of XXXXX USD)" Assets:US:GoogleInc:Unvested:C123456 XX GOOG.UNVEST Income:US:GoogleInc:Awards -XX GOOG.UNVEST Then each time you vest, I receive the stock and,spend away some of the corresponding unvested amounts, as a fictional example: 2017-03-25 * "Conversion into shares" #award-C123456 doc: "2017-03-29.Restricted_Stock_Release_Detail_Report.C123456.pdf" Assets:US:Broker:GOOG 2 GOOG {612.3400 USD} Assets:US:GoogleInc:GSURefund Assets:US:GoogleInc:Unvested:C123456 -5 GOOG.UNVEST Expenses:GoogleInc:Vested 5 GOOG.UNVEST (The above conversion is special to how my employer implements RSUs; they sell a portion of the received shares to convert for taxes and you receive the remaining shares and the cash difference. In this example, 5 shares are vested, 3 are sold to pay for the taxes (on W2), 2 are deposited to the account, and the "GSURefund" account is debited. That account is filled up in another transaction paired with each conversion, for which a pay stub is received that details all the tax amounts (federal, state, medicare, etc.) and net amount deposited to this GSURefund transfer account. The non-converted amount is sent as a check later and I assert the remainder is 0 i.e., refund has been paid.) Anyhow, maybe ignoring the last bit, everything above could remain the same. Here's one example of how you could do this, you may have to get a little creative with commodities: ;; Example tracking of ISO options. ;; Define three commodities: unvested options, vested options, and actual shares. 2013-01-28 commodity ACME.UNVEST name: "Unvested award of Acmeshares." 2013-01-28 commodity ACME.ISO name: "Vested ISO option of Acme shares." 2013-01-28 commodity ACME name: "Acme Inc. equity shares" 2013-01-28 price ACME.UNVEST 0 USD 2013-01-28 price ACME.ISO 0 USD ;; Define accounts to track awards. 2013-01-28 open Income:US:AcmeInc:Awards ACME.UNVEST 2014-01-28 open Expenses:AcmeInc:Vested ACME.UNVEST 2014-01-28 open Expenses:AcmeInc:Exercised ACME.ISO ;; Define broker accounts. These should correspond to real accounts somewhere ;; (e.g., Schwab, ETrade, or otherwise). 2013-01-28 open Assets:US:Broker:Cash USD 2013-01-28 open Assets:US:Broker:Options ACME.ISO 2013-01-28 open Assets:US:Broker:ACME ACME 2013-01-28 open Income:US:Broker:VestedOptions ACME.ISO ;; Track awards like this; 2017-03-20 open Assets:US:AcmeInc:Unvested:C123456 2017-03-20 * "Award C1234567 (for $XXXXX based on stock price of XXXXX USD)" Assets:US:AcmeInc:Unvested:C123456 1000 ACME.UNVEST Income:US:AcmeInc:Awards -1000 ACME.UNVEST ;; When an option vests, spend it and deposit it to your broker account. 2017-03-25 * "Vesting" #award-C123456 Income:US:Broker:VestedOptions -15 ACME.ISO Assets:US:Broker:Options 15 ACME.ISO Assets:US:AcmeInc:Unvested:C123456 -15 ACME.UNVEST Expenses:AcmeInc:Vested 15 ACME.UNVEST ;; Later on when you exercise the option, save the amount you paid as the cost ;; basis. 2017-03-25 * "Exercise" #award-C123456 Assets:US:Broker:Options -10 ACME.ISO Expenses:AcmeInc:Exercised 10 ACME.ISO Assets:US:Broker:ACME 10 ACME {200.00 USD} Assets:US:Broker:Cash -2000 USD > - Tracking shares purchased through an ISO: that's just like the > "anniversary" shares, right? The cost is all borne by the company. > > Here's another example of the same, this time tracking each of the awards with its exercise price as the cost basis and using less accounts (perhaps a bit simpler), taking care not to use USD for unvested, unexercised things: ;; Example tracking of ISO options. ;; Define four commodities: unvested cash, unvested options, vested options, and actual shares. 2013-01-28 commodity USD.UNVEST name: "Unvested dollars (worthless, used to track exercise price of ISOs)." 2013-01-28 commodity ACME.UNVEST name: "Unvested award of Acmeshares." 2013-01-28 commodity ACME.ISO name: "Vested ISO option of Acme shares." 2013-01-28 commodity ACME name: "Acme Inc. equity shares" 2013-01-28 price ACME.UNVEST 0 USD 2013-01-28 price ACME.ISO 0 USD 2013-01-28 price ACME.ISO 0 USD ;; Define accounts to track awards. 2013-01-28 open Income:US:AcmeInc:Awards USD.UNVEST 2014-01-28 open Expenses:AcmeInc:Exercised USD.UNVEST ;; Define broker accounts. These should correspond to real accounts somewhere ;; (e.g., Schwab, ETrade, or otherwise). 2013-01-28 open Assets:US:Broker:Cash USD 2013-01-28 open Assets:US:Broker:Options ACME.ISO 2013-01-28 open Assets:US:Broker:ACME ACME ;; Track awards like this, keeping track of the cost basis; 2017-03-20 open Assets:US:AcmeInc:Unvested:C123456 2017-03-20 * "Award C1234567 (for $XXXXX based on stock price of XXXXX USD)" Assets:US:AcmeInc:Unvested:C123456 1000 ACME.UNVEST {200 USD.UNVEST} Income:US:AcmeInc:Awards -200000 USD.UNVEST ;; When an option vests, spend it and deposit it to your broker account. 2017-03-25 * "Vesting" #award-C123456 Assets:US:AcmeInc:Unvested:C123456 -15 ACME.UNVEST {200 USD.UNVEST} Assets:US:Broker:Options 15 ACME.ISO {200 USD.UNVEST} ;; Later on when you exercise the option, save the amount you paid as the cost ;; basis. 2017-03-25 * "Exercise" #award-C123456 Assets:US:Broker:Options -10 ACME.ISO {200 USD.UNVEST} Expenses:AcmeInc:Exercised 2000 USD.UNVEST Assets:US:Broker:ACME 10 ACME {200.00 USD} Assets:US:Broker:Cash -2000 USD One advantage of that second example is the cost basis. Note that I'm careful not to price it in USD, I wouldn't want the value to show up on the balance sheet, because it's not exercised. You could even automate some of the regular legs by writing a plugin if some of the redundancy bothers you. > > - Finally, in Trading with Beancount, you show a PnL account you've > created to track capital gains, and you associate it with the broker. I > could see wanting to know (in the language of this example) PnL for AA and > AB separately, too. That's easy to do with sub-accounts, but I might also > want to track PnL for AA and AB, even when some of those assets are held in > certificates and the rest at BrokerA. Have you found a best practice for > doing that sort of thing? > > Sum these things as an SQL query. You can use regular expressions in the account name e.g. select account, sum(position) where account ~ 'Assets:Broker.*:PnL' or something like that. You can define those as custom queries with a Query directive, so after that they're easy to run by name, e..g bean-query $L run isopnl Also consider adding some of the more pedantic plugins for more certainty sellgains onecommodity etc. Overall the examples in the files are much simpler than the reality, but the system extends to a lot more, it's quite powerful. For example, these days I'm buying put protection on options and trying to generate income to compensate by selling near-dated calls and puts on indexes, even went through assignment for the first time a few weeks ago, tracking everything has been no problem. The only quirks are that I'm creating a new commodity name for each option, and in the long run that might not make sense, maybe I'll want to automate some of that, either in my importer or with a plugin. OTOH it's convenient because each option purchase has a unique strategy for it (hedging/protection, income, etc.) and I'm able to add metadata to the commodity directive and that metadata gets joined in with the positions and automatically exported to my google sheets, where I have a Strategy column and I can filter the list of positions per-strategy, and even plot the risk profile. In the past I have had cases of dividends, cost basis adjustments, transfers, currency changes, and a bunch of other things. It works. The main missing feature for investments IMO is automatically merging cost basis for doing average cost tracking. Everything else works, despite the fact that sometimes, as in your thread, you may have to provide it with a bit info than it seems. Interpolation could be improved for sure, with more assumptions (sacrificing some generality). I hope this helps, > Bill > > -- > 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/d29804b8-04c3-4dc7-a8e2-25549db1ede0%40googlegroups.com > <https://groups.google.com/d/msgid/beancount/d29804b8-04c3-4dc7-a8e2-25549db1ede0%40googlegroups.com?utm_medium=email&utm_source=footer> > . > For more options, visit https://groups.google.com/d/optout. > -- 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/CAK21%2BhMUT%3DxxiX_3LK_HZm0d7nKO742%3DeR939qVjYMmaU%2B%3DnFQ%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
