XcelRookie
New Member
- Joined
- Sep 5, 2017
- Messages
- 5
I've tried using SUMIF and SUMPRODUCT - maybe there's another function better suited to the task? I'm trying to get a calculation, for each day, for the combined total of realized and unrealized profits for a complete portfolio of numerous stocks. I'm using Excel 2016. To view the sample worksheet, please see the file in the attached URL. What I'm trying to do is to get a calculation, for each day, for the combined total of realized and unrealized profits for a complete portfolio of numerous stocks. I'm using Excel 2016. Here's my attempt to articulate all the formula arguments:
STEP 1 - CALC THE UNREALIZED PROFITS
1. IF QUERY DATE => ENTRY DATE AND IF THE QUERY DATE IS<exit date="" or="" exit="" is="" blank,="" then="" calc="" the="" unrealized="" profit="" as="" of="" query="" by="" taking="" entry="" price="" and="" subtracting="" it="" from="" mtm="" (mark="" to="" market)="" multiply="" that="" sum="" #="" shares.="" return="" an="" error="" if="" required="" blank.
2. IF QUERY DATE< ENTRY DATE THEN SKIP THOSE LINES.
STEP 2 - CALC THE REALIZED PROFITS
1. IF QUERY DATE => ENTRY DATE AND IF THE QUERY DATE IS =>EXIT DATE, THEN PICKUP THE CORRESPONDING VALUES IN THE REALIZED PROFIT COLUMN AND SUM THOSE. (IN THIS CASE, NO NEED TO RETURN AN ERROR IF MTM PRICE IS BLANK WHICH IT MAY OR MAY NOT BE).2. AGAIN, IF QUERY DATE< ENTRY DATE THEN SKIP THOSE LINES.
STEP 3 - SUM THE RESULTS OF THE CALCS FOR UNREALIZED AND REALIZED PROFITS INTO A SINGLE OUTPUT VALUE
Clarification Notes:
1. Note that I've added the Result Calculation Columns K & L just for illustration purposes, all those calcs are intended to take place in the cells above the Query Dates.2. Values associated with Entry Dates that are > than the Query Date are never to be considered in these calculation.
3. Realized profit included above is driven off of fields that weren't included in this sample (so don't try to make sense of them).
*Sometimes (like in cell J12) there will be values in the MTM Price column even if the trade has been exited - I assume that those values will not cause any problems for the above calcs .
HOPE THIS MAKES SENSE - ANY HELP OR SUGGESTIONS GREATLY APPRECIATED!</exit>