@Blake how do you categorize the initial purchase of an asset? I understand splitting the sale into basis and gain/loss, but what about the initial purchase?
And to clarify, did you say in #3 that if one’s brokerage account is linked, both sides of these transactions tracked and therefore net to $0?
It seems people have differing opinions on how to categorize initial investment transactions. Some prefer to call it an Expense, others prefer to call it a Transfer. Both kind of make sense and kind of don’t. That what’s confusing.
What about a fourth Transaction Type called Investments, with various asset classes as Groups (stocks, bonds, notes, real estate, etc.), and actual assets as Categories (VTI, BND, ACME preferred stock, 123 Magnolia, 456 Baker, emergency fund, etc.). Seems like it would allow one to track granular Position History (as they’re just Category filters), the Transfer Type always nets out to $0, and one can set up auto-cat rules for specific asset buys.
Can someone point out the flaws in this idea? How might it go wrong? Unnecessary complexity? Is there something obvious and simple I’m missing?
Ultimately, I’m trying to bridge the gap between tracking cash flow and managing my portfolio in one place. A single source of truth. Pipe dream? Possible? How do you do it?
I’d love if there were a concise recap here for others who might come across this thread and have the same question that’s the benefit of the community - the knowledge sharing - just so you don’t have to get in the habit of doing long zoom calls with every person who reads this.
A written post and a written recap would take way too long to write up. That’s why we agreed to Zoom. I anticipated it would become a free flowing conversation which it did. We discussed many other topics not included in the post which I suspected would happen. I was not worried about my time investment. It took what it took. Tiller now has a new excited user.
The main thing that’s relevant to this thread is I wasn’t aware that when you purchase or sell an investment within a brokerage or retirement account that two entries are recorded. One with the cash leaving the account, one with the stock (or whatever) entering the account, both with equal and opposing values. I thought investments would litter my Transfers bucket with transactions that didn’t net to $0, so I got to thinking about different ways to categorize these transactions. No need.
Categorize the purchase of assets within brokerage or retirement accounts as Transfers.
(I’m building my setup now. I’ll post it in Show and Tell when it’s ready, and link it back here.)
I’m not seeing the equal and opposite values show up; just the inflow (for a sale) or outflow (for a purchase). Anything you did to ensure these both showed up automatically? I was about to go through and start adding (manually) all of the necessary offsetting transactions to ensure transfers do in fact net to zero, but then saw your post and would prefer to save the (significant) amount of time that would entail.
@Blake thoughts would be greatly appreciated. I only see the outflow (for a purchase) or inflow (for a sale), not both. So unless there’s a way to start getting both automatically, or unless I add a manual transaction every time an automatically downloaded one occurs, I’ll end up with out-of-balance transfers in any period where an investment account transaction occurs. Wouldn’t be a big deal except for dividends/re-investments which occur frequently (I almost never trade, so it’s not actual buy/sell transactions I’m focused on).
It may be that that one side of the transaction doesn’t show up automatically due to the data feed and your specific institution. This could vary by institution, I don’t have enough data to say one way or another. I think if Jack is receiving them automatically it’s a feed issue, not a configuration of the sheet necessarily.
Blake has decided to move on from Tiller Money and is no longer actively participating here in the community.
I agree with Heather. If you’re not seeing the other side of the transaction it may be a feed issue.
I personally use Coda to track my finances. (Still not presentable yet, but I’ll post it when it is.) Not sure you might hack your way around this problem in Sheets without manually entering values. But in Coda you could create a view of the master table and run an automation that reads something like “every time a new row is created in this table, duplicate it and multiply the transaction value by -1.” Maybe there’s a similar formula you could run in Sheets?
I’m not familiar with Coda. But I don’t think it would be easy to create an automation in Sheets; too many conditions have to be met in order for it to be appropriate to add a row for the transfer offset.
Seems like the manual process is the only viable option, unless @heather you’ve got a better suggestion?
i suggest having many transfer categories, one called transfer - sales, one called transfer - purchases, one called transfer - dividends received, and one called transfer - reinvestment. neither each nor all transfer categories coded to type = transfer need to zero out. regarding the last two, this is what you will get when you have auto-reinvest turned on in your brokerage account. one will be positive and the other will be negative. you can use other transfer categories if you want to track gain/loss in these retirement accounts. you can do something similar to track gain/loss in taxable accounts which you will want to do because you will need this gain/loss for tax return purposes.
Thanks, @Blake. The idea of many transfer categories makes good sense to me and I like the ones you’ve suggested. I have a lot of others for things like real estate investments, private equity investments, etc., but all for accounts that don’t have automatic data feeds. Getting my public equity investment accounts (which do have data feeds) set up is what’s raising my questions/concerns on this topic.
I understand that neither each transfer category nor all need to zero out, but if they don’t then when I’m reviewing the transaction activity for a given time period it’s hard to know for sure that there aren’t any data errors in that period. You’re suggesting that I just let the imbalanced transfer categories remain imbalanced?
I was a Quicken user many years ago and always liked that it was basically accounting software with double-entry bookkeeping. I’ve always struggled with systems like Tiller or Mint that don’t try to do any form of reconciliation. Thoughts?
an imbalance, as you call it, does not connote errors. your public equity accounts are no doubt unique. i am willing to discuss offline as we would need to get much more specific for me to advise further.
The imbalance itself is not an error, of course. But the benefit of double-entry bookkeeping in which transfers net to zero is that it makes it easier to identify when an error has occurred because an imbalance in transfers would imply that a transaction was missing or incorrect.
Connecting offline sounds good. Please let me know when and how you’re most easily available.
that’s the benefit of the community - the knowledge sharing - just so you don’t have to get in the habit of doing long zoom calls with every person who reads this. 