Investment Transactions Report - Portfolio Value incorrect

[Glenn2]

I am FM 11.2. I have started trading Futures and as a result am entering large value SELL transactions and then several days later entering large value BUY transactions. The Sell decreases a holdings account and increases a cash account and the opposite for a Buy. When I run a report or graph over a portfolio containing the Holding and the Cash account I would expect a Buy or a Sell to have nil impact on the Portfolio Value. This has always worked for me but I have now discovered that when I have several of these transactions overlapping the Portfolio Value is incorrect for several days until the final transaction is entered. As a result my portfolio looks as though I have made or lost money when I have not. This Portfolio Value is visible in the Investment Transactions report and the Stacked Value graph, both of which are showing this anomaly. I wish I could upload a screenshot to show this.

I should also say that it is annoying that the Portfolio Value in the Investment Transactions report does not update for each transaction but only for each day. Is this also a but or intended to be like this.

[Mark]

Hi Glenn2,

We are currently working on upgrading this forum software so that you will soon be able to upload attachments. I agree this would be very helpful.

I would not expect your portfolio value to have to wait a few days to settle out. It should work as you describe, where whenever you record a sell of a future, you have a corresponding entry of a buy for the same amount in your cash, on the same date. This should keep the portfolio value correct for each day. You might double check that you have your default cash account feature turned on. See the properties of your sub-portfolio. Look at an investment transaction report for a given day, and make sure for every buy, you have a corresponding sell of cash, and vice-versa for sells.

Yes, the Investment Transaction report shows the end of day, not end of transaction, value in the balance column. This is as intended, but it would be good to make it at least optionally to work the other way.

[Glenn2]

Thanks for your reply.
I have duplicated this problem as follows which in my mind is telling me there is a problem with the software. Can you try the following and see if you get the same results and tell me if this is correct or not.
1. Create a new new portfolio
2. Create a stock in the portfolio called "stock". It does not matter what the settings are.
3. Create an account in the portfolio called "account". Again it does not matter what the details are.
4. Set the portfolio's derfault cash account to "account".
5. Enter the following transactions.
a) Sell 1 stock at $100,000 on 5 July
b) Sell 1 stock at $100,000 on 6 July
c) Buy 1 stock at $100,000 on 7 July
d) Buy 1 stock at $100,000 on 8 July
For each allow the default account to be posted to.
When I look at the data regisetr all is as I would expect.
The Investment Transaction report however looks as follows:

Date Transaction Investment Price Shares Amount PortfolioValue
5Jul Buy Account 1 100000 100000 99999
5Jul Sell Stock 100000 -1 -100000 99999
6Jul Buy Account 1 100000 100000 -40000
6Jul Sell Stock 100000 -1 -100000 -40000
7Jul Sell Account 1 -100000 -100000 0
7Jul Buy Stock 100000 1 100000 0
8Jul Sell Account 1 -100000 -100000 0
8Jul Buy Stock 100000 1 100000 0

The Portfolio Value on 5 & 6 Jul makes no sense at all. Remember that the portfolio, Account and Stock are brand new with no other postings. Can you explain what is going on? Can you duplicate this at your end?

[Mark]

Hi Glenn2,

I suspect the problem has to do with a difference in your purchase price and the market price. When you created the investments in steps 2 and 3, what opening price did you create it with, and what date?

Note, on July 5 you are selling the stock for $100,000 at a price of 100000 and 1 share. This means your market value is -1 share times whatever market price you have recorded. The market price is not necessarily the same as your sell price. If you already have a market price recorded on a particular date, and then record a transaction, the transaction price does not overwrite your currently recorded closing market price.

Create yourself a Portfolio Value report on July 5th, and you'll be able to see why the market value is 99999, instead of the expected 0. I'm guessing it is because your "Account" and "Stock" both have a market price of 1 on this date, so "Account" is worth $100,000, but "Stock" is only worth -$1, instead of the expected -$100,000.

To view/edit the market pricing, open up the Data Register with the Data Type set to "Prices". For your "Stock", you would want the price on July 5th to be 100,000 in order to have a portfolio value of $0 as you expect. I'm guessing it is $1 instead of $100,000.

If you don't get a portfolio value that you expect on any other particular date, create a Portfolio Value report on that date, so you can see why it is being reported at that value. You can't see this from just the Investment Transaction report.

[Glenn2]

Thanks, that has resolved my test situation. I then took this logic back to my real data and found some anomalies. I have fixed these and now my Portfolio Value makes sense.

The unfortunate thing is that I first found an issue with Portfolio Value due to a key graph I use ie Stacked Value by sub-portfolio. Even though I have corrected the Portfolio Value, the graph still shows big swings. I have found that if I look at the Stacked Value Graph by Investment, Investment Goal, Symbol, Currency or Tax Status I get a graph showing the correct Portfolio Values over time. If I however look at the Stacked Value Graph by Asset Allocation, Investment Type or Sub-Portfolion I get a different Portfolio Value over time. All 3 graphs show the same thing ie big spikes around the time I am buying and selling futures. Any ideas on why the Portfolio Value would vary for different types of Stacked Value graphs?

[Mark]

Hi Glenn2,

Is the total portfolio value different between these different stack graph types, or just the allocation within the portfolio? The total should be the same for all cases, but the allocation may of course be different. You could create a Portfolio Value report again with 2 dates, the day of the spike, and the day before/after, where there is no spike. This should help find the reason.

[Glenn2]

The total portfolio value is what is different between the 2 sets of graphs. Obviously also the allocation within the graphs is different but that is what you would expect. I have spent a bit of time looking into it and cannot see why this is the case. I have started using the Portfolio Value graph instead which matches the correct Stacked Value graphs.
thanks for all your help

[Mark]

Hi Glenn2,

I've just sent you an email. If you'd like, please email me a backup of your database by doing "File / Backup..." and send it to me at that email address.

[Glenn2]

Okay thanks. I have just sent you a reply to your email address <deleted>
regards