Hi.

I'm struggling with math on a formula and was hoping someone would be generous enough to point me in the right direction?

A transactional database. Tracking the trades of a currency trader. Each trade has a duration from a few minutes to several days. Each either closes at a profit or a loss in dollars. I want to find a way of turning the following common language sentences into formulas:

"Based on your trading performance so far, you'll have '$x.xx' by the end of 2008"

"Based on the monthly/annual target you set yourself of $x.xx, you're on track at x% of your target"

Existing fields:

profit dollars

loss dollars

opening timestamp

closing timestamp

target monthly

target annually

rate of compounding

My math/calculation skills peter out at basic aggregate functions... it's tough when that side of one's brain just doesn't work -(

Many thanks in advance

Methinks you've made this more complicated than necessary. You don't need the timestamps and why do you have profit and loss fields, you only need one field (say Income) that can be pos or neg. And I'm not sure how rate of compounding is supposed to come into play.

Let's see, for the first...

Sum the Income field for the year (using a Summary field, or summing from another table) and that'll give you a Year to Date (YTD).

Figure out what today represents as a percentage of the year and plug it all in.

ProjectedIncome =

YTD * DayOfYear(Get(CurrentDate)) / 365

For the annual part of the second you just divide ProjectedIncome by the AnnualTarget.

Monthly isn't much more complicated, just change all the Year stuff to Month (ie MonthToDate, DayOfMonth, etc).

Well, first, thanks for your rapid and invaluable help

I know it seems I made it more complicated than necessary, but I've been round the houses getting this right, and there have been numerous issues of presentation vs calculation that have lead to using separate profit loss fields (I'm using conditional formatting to denote a value's positive/negative state, for instance).

What I don't get is why I wouldn't need timestamps? The trader needs to see some feedback on opening and closing times for trades. Also for searching date ranges. Having said that, I'm a self-confessed FM novice, so there may be a bunch of things that are probably inelegant behind the scenes. At least it looks bitchin' (I'm an interface designer)

thanks again.

Edited January 9, 200818 yr by Guest

You might need those fields for other things, but for this particular feature, you need:

-Income (either positive or negative)

-Trade Date (which can be fed off of Closing Timestamp - I was wrong saying you didn't need this).

-Annual Target

-Monthly Target

It's all working perfectly. Thanks for your help :)