Investment history

I have a spreadsheet that I do by hand by updating it once a week but I would like to be able to create the whole thing starting with a report or two from Quicken and then add in the other columns I'm interested in by hand. So… if I could recreate the spreadsheet I have in 15 minutes, that would be sweet.
The three key fields I need are: total investment value, amount of cash, and the cost basis of the stocks.
I find that I can get the total investment value over time by month (for example) from the Network by Month report. I can go to the "start of time" (which is 1/1/2017 in this case) and get the cost basis and cash at that time but I can't figure out how to determine those value for each month.
In a Summary Report, I can get lines that report such things as Investments Buy and Investments Sell. And I thought I could use those monthly values plus the initial value of cost basis to calculate the cost basis at each month but I could not get the numbers to work out.
If I take the previous Cost Basis and subtract out the entry in a Category Summary report under Other ⇒ Investments ⇒ Total Investments for the year, I don't get the Cost Basis starting at the next year. Curiously, if I add in the line for Income ⇒ Investments ⇒ Realized Gain/Loss, I get very close. The biggest error is $188 for the year and twice it comes out exactly correct. But that doesn't make any sense to me at all.
So… this has turned into two questions. My main objective is to find the three values mentioned above over time. For example what is the cost basis of my investments at the start of each week.
My secondary objective would be to 100% understand what goes into the lines under Income ⇒ Investments and Other ⇒ Investments because it isn't obvious to me.
Comments
-
Ah… I figured out a little bit. The entry for Other ⇒ Investments ⇒ Sell (which is a positive number) can be split into two components: the original Cost Basis plus Realized Gain/Loss. So… new Cost Basis will be the original Cost Basis minus the number for Sell plus the number under Realized Gain/Loss. Now things make sense except for the small discrepancies noted which can be positive and negative.
0 -
I am going to conclude if I can get others to agree with me that I have found a bug.
I have an entry for $9.41 of type Dividend Reinvest. This shows up in a Transaction Report under Dividend Income but it does not show up under Other ⇒ Investments ⇒ Buy. But, isn't that basically what happened? Dividend Reinvest is basically a two part thing. One is a Dividend Income of $9.41 in this case, and the other part is the purchase (Buy) of shares for the same amount. Seems to me that $9.41 entry should show up under Other ⇒ Investments ⇒ Buy.
I also don't see a way to get a report by "Type" of transaction such as Dividend Reinvest.
0 -
I think you might be right. I created a test account and added some shares in a MMF. At the end of January I received $5 in dividends that were automatically reinvested, and $10 in dividends that were reinvested by me:
When I run the report, I see that I had $15 in Dividend Income (correct) but only $10 in investment purchases:
The $5 purchase via automatic reinvestment is not shown; it seems like it should be on there somewhere.
2 -
Is there a way to get support involved and open a ticket?
0