Cost Basis

vk8tx

Hi, I have been investing in mutual funds since the early 1990's. After the initial investment opening the accounts I would purchase more shares monthly and reinvested dividends and capital gains. For the past few years I stopped reinvesting dividends and capital gains, instead I take them as cash. I am trying to export all the fund's transaction history to Excel but keep getting different summaries. What is the best way to export the Quicken history to allow for results similar the cost basis in: "Reports/Investing/Portfolio Value & Cost Basis"? Tnx

NotACPA

Why not simply "Print" that report to a tab delimited Text file ... and  then import it into Excel?

q_lurker

The report you cite (Reports/Investing/Portfolio Value & Cost Basis) does not present transactions.  The report only presents cost basis and value at specific intervals.  I presume you are trying to cross-check that type of information based on the actual transactions.

"Reports / Investing / Investment Transactions" will list all the transactions and will subtotal by Security, Account, and other criteria.  But that report can be voluminous.  You may want to limit to one account at a time.  You may want to limit the scope of the transactions (Actions or Categories) depending on your intentions.  Familiarize yourself with the various customization options.  

I find the easiest way to move report information into Excel is to use the Export icon / Copy to Clipboard (Ctrl-C), then switch to Excel and Paste (Ctrl-V) [Control key sequences assuming your Quicken is set to use Windows standards.] 

vk8tx

NotACPA - I did try printing the file and importing. My problem was inconsistency, one time it would include (under Amount column) the invested amount (purchases, dividends, capital gains; what I want) and other times it would not include invested amounts (monthly purchases) but did include dividends and cap gain.

q_lurker - Yes, I expected there was a report to allow me to cross-check the data. I had not tried the "Investment Transitions" but did and it copied ALL my accounts as you mention (including the ones I've sold). Is there a way to only get transactions for one account? I could copy each open account to a new Sheet or a new Excel file if needed.
I have tried the report "Investment Activity" but it doesn't include the capital gains I've paid over the years (it only provides dividends and monthly purchases). Could some of my inconsistent results be due to some cases using BoughtX for monthly purchases while other time I used Added? I believe all dividend and capital gain purchases have been listed correctly (i.e., ReinvDiv, ReinvSh, ReinvLg) in each account.

q_lurker

vk8tx said:

... 
q_lurker - Yes, I expected there was a report to allow me to cross-check the data. I had not tried the "Investment Transitions" but did and it copied ALL my accounts as you mention (including the ones I've sold).

Is there a way to only get transactions for one account?

You don’t sell accounts; you sell securities. But yes, you can generate the report for a single account. Use the gear icon to customize the report. You can then paste that report into whatever part of Excel you choose. 

vk8tx said:

... 

q_lurker - ... 
Could some of my inconsistent results be due to some cases using BoughtX for monthly purchases while other time I used Added? I believe all dividend and capital gain purchases have been listed correctly (i.e., ReinvDiv, ReinvSh, ReinvLg) in each account.

I don’t know what inconsistencies you are seeing, but Bought vs Added could be a factor. Do the Adds include the cost basis info?  Edit an Add Shares transaction to see where that info gets entered. 

I am not sure where you are going with this exercise but some institutions are not as rigorous as Quicken on cost basis in tax deferred accounts. 

Jim_Harman

From the title of your post, perhaps you are trying to understand the details of your cost basis.

One way to do this is to use an Investing > Portfolio view. The Tax Implications view is a good starting point, or you can customize the columns, accounts, and securities to include. Next to Group by: pick Accounts. At the bottom of the page, click on Expand all.

Below each security this will show the details of each tax lot you currently hold. This will show the purchase date, and depending on the columns you have selected for the view, the number of shares in the lot, purchase price per share, cost basis, gain/loss in dollars and %, holding period (long or short term), etc.

If you want to see your holdings as of a different date, you can change the As of: date at the top.

If you want the listing to include lots you have sold, click on Options at the top right and pick Show closed lots.

You can print or export this data by going to File > Print portfolio or clicking on Ctrl-P.

This view provides a good way of doing a sanity check on your holdings and comparing to your broker's records. If the lots in Quicken are different from the broker's, you may have selected the wrong lots to sell in Quicken. If the cost basis for any of the lots is zero or the gain/loss is way out of line, you should check the corresponding Bought or Added transactions.

NotACPA

The Cost Basis for any security lot is determined when you purchase that lot, either by using a BUY transaction or one of the REINV___ transactions.

The Cost Basis for the entire  security position is reduced when you sell a portion of the security.

BUT NOTE, that unless you specify otherwise, your brokerage/firm will almost always use FIFO in selecting what specific lots  to sell  ... and unless you select the exact same lots (and quantities of each lot) for  what you record in  Q, then the brokerage's "cost basis" and  Q's cost basis are going  to differ.

vk8tx

q_lurker - Yes, securities: all my Quicken data are mutual funds. I started investing in them in the early 90's. I have used the gear icon and the output is were the inconsistencies are found. Sometimes it lists the amount invested (purchase, Div, CG) and on other mutual funds it only lists Div and CG. The "BoughtX" only has one date. The "Added" has a second date for "required for calculating tax". I see I have mix of both which, I think, implies you use BoughtX with first purchase and then use Added on all future purchases (dollar cost averaging). Do you agree? If so, I can go back into the entries and change as needed. This exercise is not for tax-deferred accounts (IRA, 401k, etc). The exercise is to determine how much I invested and how much tax I've already paid. Thus, when I finally sell I won't be sending more tax money to the IRS than necessary.

vk8tx

Jim_Harman - I am trying to understand all of the investments (purchases, Div, CG) in each mutual fund so I can ensure the Quicken Cost Basis is the same as my calculation. I have been investing in a dozen mutual funds since the early 90's and want to ensure I don't overpay the IRS tax when I sell each fund. I am using the Standard View and see the collapsed view showing Gain/Loss. I assume Gain is the amount beyond the investments (i.e., purchases, Div, CG) which I would report to IRS after selling. I did add Cost Basis to the view and it makes it better, thanks. I think I need to update several of my transactions because some of my purchase transactions were entered as Added while others were entered as BoughtX. I do see how I can export the data better.

vk8tx

NotACPA - For my Buy transactions I see I've entered some as Added while others are listed BoughtX. I'm starting to think I need to be consistent with transactions. "Added" has a 2nd date for tax purposes so would it be correct to use BoughtX on the initial purchase and Added for all subsequent purchases? Of the dozen mutual funds I have I've only sold one lot so far so all future sells will be the whole fund when cash if needed.

q_lurker

Wow.  Where to even start on this --- 

The exercise is to determine ... how much tax I've already paid. 

Nothing in this exercise will determine how much tax you have paid.  That depends extensively no your entire tax return, so put that objective out of your mind.  But I suspect what you are really after in that direction is to identify what capital gains you have already realized (and maybe or maybe not paid taxes on) so you don't report too large a gain as a part of future sales.  But if you have only sold one lot, that already realized (and taxed?) gain should be easy to identify. 

I am trying to understand all of the investments (purchases, Div, CG) in each mutual fund so I can ensure the Quicken Cost Basis is the same as my calculation. 

I would be more comfortable with you getting all the Quicken transactions to properly match the real world transactions.  With that done, Quicken will tell you the correct gain/loss information.  From your presentation and terminology used so far, I would be skeptical of your calculation. 

I see I have mix of both which, I think, implies you use BoughtX with first purchase and then use Added on all future purchases (dollar cost averaging). Do you agree?

Not likely.  An Added Shares would generally be a more rare event.  Any time you have sent money to the MF family or brokerage to add to your investment (dollar cost averaging), those would become Bought Shares transactions.  The other common circumstance is that up to four types of cash distributions from the MF might be reinvested.  Those too add to your cost basis but would not normally be Add Shares transactions.  They would be ReinvDiv, ReinvLg, ReinvSht, or ReinvInt (for dividend payments, LT and ST CapGain  distributions, and interest payments).  You might choose in Quicken to receive those payments as cash in your transaction list and then use the cash to Buy Shares as an alternative to the Reinvest transactions.  
 
Finally on that aspect, one COULD choose to not maintain the records that $X was sent each month to Buy shares and instead simply Add the shares that were truly bought at that time with that $X, but I would not consider that the right way to handle things.  Maybe it works OK for you that way, not for me.  If using the Add Shares transactions, you need to make sure the cost basis of those added shares is properly entered for each such transaction.

Of the dozen mutual funds I have I've only sold one lot so far ...

With that in mind, you might have Quicken generate a Capital Gains Report for that account, that security, that time frame.  That will tell you what Quicken understands you have told the program about that sale and how much gain Quicken would be showing you should have reported to the IRS that year.  Your actual tax return might have reported something different in which case, I would choose to edit the sale transaction accordingly or otherwise make adjustments to your date.  

You have repeatedly mentioned BoughtX transactions.  Those are simply a variation of a standard Bought transaction where the cash source for the buy is a different account than the one where the shares are being bought.  Now there is a class of accounts in Quicken referred to as Single Mutual Fund accounts where those ___X type transactions are required.  It would be helpful (for these discussions) to know if you chose to use those types of accounts.

 

vk8tx

I misspoke on tax. What I meant is my need to find what liability I've had over the past 30 years (i.e., total dividends and capital gains I've been taxed on already). That value plus my initial purchase ($1,000) and all monthly purchases ($100) should be , I believe, my cost basis. That value subtracted from my sell value (selling each MF) should be my final tax liability.

What Quicken transactions (e.g., Bought, Added, Div, etc) would you recommend for a person tracking a single mutual fund (all mine are set that way) who makes an initial purchase ($1000), adds monthly purchases ($100), and reinvests all dividends and capital gains. It sounds like I used incorrect transactions but it seemed logical (i.e., Quicken transaction "Buy - Shares Bought" and "Add - Shares Added").

Over the past few years I've taken distributions (Div, ShCG, LtCG) as cash so I wouldn't use them in cost basis.

How can you properly enter cost basis of added shares if not using Added?

Thanks for the suggestions, I know it would be much better discussing this in person or on the phone.

NotACPA

Any reinvested Dividends, Cap Gains, etc ALSO add to your cost basis.

They're exactly the same as if you received the payout in cash, and sent the check back with the instructions "Buy More".

AND, such actions add a new LOT to your holding ... although Fidelity (if that's your fund company) reports these incorrectly in Retirement accounts.

Jim_Harman

To answer your question directly, you have apparently set up "Single Mutual Fund" accounts in Quicken. These accounts cannot hold cash, so every purchase should be a BoughtX. Distributions that were reinvested should be ReinvXXX, where XXX is Div, Int, Lg, or Sh. 

Distributions received in cash should be DivX, IntX, CGLongX, CGShortX.

When you sell securities, it should be a SoldX.

q_lurker

a person ... reinvests all dividends and capital gains. ... 

Over the past few years I've taken distributions (Div, ShCG, LtCG) as cash so I wouldn't use them in cost basis.

Noting the contradiction of those two statements, I think you meant for many years you reinvested; in recent years you have switched to taking some of those distributions as cash.  

As Jim noted, every purchase (the original $1000 and the monthly $100 amounts) should be treated as BoughtX with the X referring to the source as some other account.  

But I'll add on here that we (users like Jim, NotACPA, myself and others) have a bias that Quicken handles all our finances.  So in your case, I would have a 'checking' account transferring the $100/month to the various SMF accounts going back 30 years.  Each of those would have generated the BoughtX in the MF account and I would have edited each of those at the time to reflect the correct number of shares that were bought when that $100 reached the fund.  I have no idea how you handle your Quicken, your finances, or these specific past transactions.  It makes a difference.  The Add Shares could work for you, but it is not a choice I would make.

The initial purchase, each of the monthly dollar cost averaging contributions and each of the reinvestment actions contributed to your cost basis.  If you give Quicken the right information, it will give you the right answer.

vk8tx

I've only used Quicken for tracking investments so no other function (checking, spending, banking, etc.) was used. When purchasing the initial MF investment and follow-on monthly purchases I sent the MF company my check. Dividends and cap gains were always reinvested. I've attached several years of transactions for one MF. Please note I used Added when I purchased monthly additions.

q_lurker: You can guess when I retired because afterwards I started taking the cash (i.e., putting in money market in Schwab, what I called cash).

Jim_Harman - Are you suggesting I change all my "Added" to "BoughtX" related to this attached picture? If so, what would you do to ensure cost basis is captured?

Being a very limited Quicken user should I only use BoughtX, DivX, IntX, CGLongX, CGShortX, and finally SoldX for my transactions?

Jim_Harman

You can keep the Added's if you want and the cost basis should be computed correctly because it appears that you have recorded the cost of each addition correctly.

But the Added's do not tell Quicken where the cash for the purchases came from. I suppose that makes sense if you do not have any bank account in Quicken to transfer from. When you started having the distributions sent to you and not reinvested, do you have an account in Quicken where the money was sent?

Most of us have our bank accounts in Quicken as well, so when we send money to an investing account and use it to buy a security it would be recorded as a BoughtX. This transfers the money from a bank account and uses it to buy the security all in one transaction. In the bank account you will see an Xout directed to the investing account.

q_lurker

That setup (no 'other' accounts) similarly begs the question as to where the 'X' does refer?  The BoughtX has to reference an account.  What account is being referenced?  Similarly, with the CGLongX transaction.  As Jim asked -- Where in Quicken are you directing that disbursement?  I would guess for the later, you have a Schwab 'Cash' account that you may or may not reconcile with the real world.   

So again, you being a "very limited Quicken user" asking very active Quicken users how to do things is a bit of a misfit.  Our ways may not mesh with your ways. 

But to get back to your objective - Cost Basis --  A snip from my file:
  

 This is from a Portfolio View (Ctrl-U) focused on one security in one account.  The view is grouped by "Account".  I have clicked a + next to the security name to expand the view to show all lots of that security that I own.  Quicken is showing me a summary including the cost basis or every lot I currently own.  That would seem to be what you are after.  It is not particularly important which lots were Buys and which are reinvestments, but I can see the pattern to know that info. 

If you have adequately (accurately) entered the information into Quicken, what you are after is readily available. You can further customize that view (and others) to show a wide variety of relevant information.       

Jim_Harman

I second @q_lurker's comment.

As a matter of fact it is exactly what I recommended you do a couple of days ago. Have you tried this? What was the result?

vk8tx

Jim_Harman - I will keep the Added transactions as you indicate. I will plan to change all Boughtx transactions to Added. I don't have a bank account in Quicken so when I take MF cash (sent to Schwab MM) my transaction Transfer Account is listed as Cash.
q_lurker - My BoughtX transactions points to an old Fidelity account I had (closed now). Disbursements go to Quicken's Cash account (actually Schwab MM that I don't reconcile). As a limited Quicken user where in Quicken.com should I ask basic questions? Yes, your view showing Cost Basis (and a previous suggestion) should provide all the data I need. I should be able to Export what is needed.

q_lurker

... where in Quicken.com should I ask basic questions? 

This is exactly the place for you to ask questions.  My poorly expressed point was to encourage you be extensive in your setup to the questions you ask.  We have bounced around a lot in this discussion between how we might approach things, how you have approached things, and what you are really trying to get at. 

I am reminded of a story from long ago.  My wife asked as friend in college how long it took him to get home.  He said something like 6 hours.  It was only when my wife was driving the same route that she realized he drove a Corvette; she didn't.  He had a different perspective.  Who you ask and what you ask are important parts of getting good answers and interpreting those answers.

Jim_Harman

For example you started with asking about how to export the transaction history but a title of "Cost Basis" and it took several exchanges before we were able to figure out what you really wanted to know.

Starting with something like "How can I validate Quicken's cost basis data?" might have gotten you a more direct answer.