The “Highest Return” formula depends on the “Highest Price” Rollup (Aggregation) in comparison to the “Purchase Price”.
You are wanting another formula, “Current Return” related to “Purchase Price”, which means you need another Rollup (aggregation) for the “Current Return” formula to depend on. However, this rollup (aggregation) will require a few preliminary steps to set it up.
You’ll need a Rollup field in your TICKER table to rollup all of the “Stock Values” records, but instead of looking at the “Value” field and returning MAX(values), you will point it at the “Date” field and return MAX(values) [let’s call this field “Max Date”]. Now you’ve got the most recent Date that a value was fetched for that Stock.
Now go back to your “Stock Values” table – we will create a Lookup field here called “Max Date” that looks at the “Stock” linked record field (which is coming from the TICKER table), and returns the “Max Date” field from that table. Now every record in the “Stock Values” table knows what the most recent date that a value for stock of that type was fetched.
So now we can create another formula field that says “if the date of this record matches the date of the most recent value fetch for this stock, display the value fetched here as the current value of the stock”.
Let’s call this field “Current Value?”, and give it this formula:
IF(
IS_SAME(
Date,
{Max Date},
'day' <-- compares the date values to an accuracy of a day; adjust to 'hour' if multiple values are fetched in a single day
),
{Current Price}
)
Format this field as currency; it will be blank for all records except the most recently fetched value for each type of stock, and for that most recently fetched stock of each type, this field will show the current value.
So the final step here is to create that Rollup in the TICKER table that your “Current Return” formula will be based on. Let’s call this Rollup field “Current Value”, point it at the “Stock Values” linked record field, the “Current Value?” table, and use the SUM(values) rollup function. Format as currency. Since there is only 1 value (the most recent value) for any given stock in this rollup, it will always return {Current Value} + 0 + 0 + 0… ie, only the Current Value.
(Note: if you are getting more than one value returned for the same stock in this rollup, and thus getting an inflated “Current Value” since more than one value fetched is being summed here, then adjust that accuracy of ‘day’ in the IS_SAME() formula to be more precise – maybe ‘hour’ or ‘minute’, depending on how often you fetch values)
Now you can create your “Current Return” formula, comparing “Purchase Price” to “Current Value”:
{Current Value} - {Purchase Price}
Formatted as currency and allowing negative numbers.
You can hide all the superfluous fields that were needed to get here, and only show your final comparison formula fields, if all the others are distracting/messy.
Hope that helps!
