Trying to calculate a daily percentage change

[UpToTheGills]

Hi, firstly, if nothing else, thanks for taking the time to read my question even if my explanation is poor and/or you are unable to help.

Usually I can figure out the solution from googling but this particular problem has proved a little more difficult.

In summary, I have a trading journal with a worksheet to input details of trades and a worksheet to display daily statistics. What I can't figure out is how to calculate the percentage gain or loss compared to the previous trading day (which isn't necessarily the day before).

I've figured out how to calculate total gain/loss from any particular day but I can't figure out how to look up the last running total figure from before the day in question.

Existing formula that isn't quite working:

=IF(OR(ISBLANK(W5),W6=0,COUNTBLANK(W6)),"",(SUMPRODUCT(--(INT('TradeInputSheet'!$B$3:$B$328)=W5),'TradeInputSheet'!$T$3:$T$328)/(SUMPRODUCT(--(INT('TradeInputSheet'!$B$3:$B$328)<W5),'TradeInputSheet'!$W$3:$W$328))))

Ignore the first part of the code - the part in question in from (SUMPRODUCT...

Screenshots below - thanks in advance for any pointers.

So, hopefully the images will help with explaining what I am attempting to achieve.
Take the date, e.g. 25-Aug-22, total up all the gains/losses for that date on the Trade Input Worksheet (Total A), then calculate that gain or loss compared to last total of the previous day (B).

 

[FormR]

Hi, instead of trying to retrieve the previous days running total, can't you sum up all the column T values with a date before the date in W5?

Using SUMIFS() - something like this.

=SUMIFS(TradeInputSheet!$T$3:$T$328,TradeInputSheet!$B$3:$B$328,">="&W5,TradeInputSheet!$B$3:$B$328,"<"&W5+1)/SUMIFS(TradeInputSheet!$T$3:$T$328,TradeInputSheet!$B$3:$B$328,"<"&W5)

 

[UpToTheGills]

Thanks for your reply.

I can't get that to work unfortunately. Having looked at the syntax of SUMIFS, in the first statement, any reason why you can't use just "="&W5 instead of ">="&W5 and "<"&W5+1?

Have amended slightly the code and used below but no dice so far.

=SUMIFS('TradeInputSheet'!$T$3:$T$328,'TradeInputSheet'!$B$3:$B$328,">="&W5,'TradeInputSheet'!$B$3:$B$328,"<"&W5+1)/SUMIF('TradeInputSheet'!$W$3:$W$328,"<"&W5,'TradeInputSheet'!$B$3:$B$328)

So, if I understand correctly, the above formula is stating:
Add together any figures in Column T on the TradeInputSheet that are greater than or equal to the date in cell W5 on stats sheet as well as any figures that are less than the W5 date + 1

then

divide by the sum total of all the figures in Column W on TradeInputSheet (title A/c Balance) that are less than the W5 date, i.e. to match the total of 55.92.

 

[FormR]

Hi,

Have amended slightly the code and used below but no dice so far.

Did you try it un-adulterated? Is your running total in column W not the same as summing the column T rows for the row it is on and all the rows below it?

So, if I understand correctly, the above formula is stating:
Add together any figures in Column T on the TradeInputSheet that are greater than or equal to the date in cell W5 on stats sheet as well as any figures that are less than the W5 date + 1

No, it's saying sum the column T rows where date in column B is greater than or equal to the date in W5 AND is less than the date in W5+1 - you need to do it like this as the dates in column B also contain the time.

any reason why you can't use just "="&W5 instead of ">="&W5 and "<"&W5+1?

Yes - see above.

divide by the sum total of all the figures in Column W on TradeInputSheet (title A/c Balance) that are less than the W5 date, i.e. to match the total of 55.92.

I meant to use column T and not column W (see first point/question)

 

[UpToTheGills]

Did you try it un-adulterated?

Yes

Is your running total in column W not the same as summing the column T rows for the row it is on and all the rows below it?

No, it isn't but I've just figured out why - Column T (Net P&L) sum of all amounts that relate to a date less than W5 (25-Aug-22) also needs a balancing figure added that is the sum total of all deposits & withdrawals to the account… which I have on a separate worksheet, Account Transactions.

If I incorporate that, I can get to the total I need:
-5,844.08 = Column T sum of amounts for days less than W5
5,900.00 = total from Account Transactions

= 55.92

I'll need to get rid of the negative I think though.

you need to do it like this as the dates in column B also contain the time

Ahh, gotcha.

I think I can get this to work now but that will have to wait until after dinner…

 

[FormR]

I think I can get this to work now

 

[UpToTheGills]

Working formula is as follows:

=SUMIFS('TradeInputSheet'!$T$3:$T$328,'TradeInputSheet'!$B$3:$B$328,">="&W5,'TradeInputSheet'!$B$3:$B$328,"<"&W5+1)/('Account Transactions'!$D$40-ABS(SUMIFS('TradeInputSheet'!$T$3:$T$328,'TradeInputSheet'!$B$3:$B$328,"<"&W5)))

So the latter part of the formula needs to pull in the total of all deposits & withdrawals before taking away the sum of Column T for days less than W5. I suspect this will break if the Account Transactions figure is less than the sum of Column T, which would perhaps necessitate a check in the formula beforehand to see if that was the case but I'll leave that for the time being as that isn't likely to happen in my case unfortunately!

Thanks for helping me get there.

 

[UpToTheGills]

OK, I'm going to have to qualify this - the above formula works… up to a point but then breaks down and I discovered there are two separate issues.

When copying the formula into the necessary cells, I realised that pulling in the total from Account Transactions only works until the reference date is before a transaction. This can be overcome by inserting a SUMIF:

SUMIF('Account Transactions'!$B$3:$B$39,"<="&W5

The second issue is that the calculation needs to change to correctly calculate the balance.

For example, if W5 is July 1st:

6,024.53 = Column T sum of amounts for days less than W5 i.e. it is positive not negative like further upthread.
6,400.00 = total from Account Transactions

so they need to be added not subtracted to make the correct A/c Balance.

So far, I haven't managed to get my head around figuring how to put this into a formula but then I thought this is all getting horribly complicated surely there must be a simpler way to do this by looking up the first date that is less than W5 in Column B, i.e. 'TradeInputSheet'!$B$3:$B$328,"<"&W5 that will pull in the corresponding value on that row from 'TradeInputSheet'!$W$3:$W$328?

I've tried using VLOOKUP but that won't work and I'm wondering if that is because I am using an input date in a column of dates that are formatted with time.

 

[UpToTheGills]

Update - it is possible with SUMIF and SUMIFS, I'd just needed to work on the maths a bit more…

Final full working formula:

=IF(OR(ISBLANK(W5),W6=0,COUNTBLANK(W6)),"",SUMIFS('TradeInputSheet'!$T$3:$T$328,'TradeInputSheet'!$B$3:$B$328,">="&W5,'TradeInputSheet'!$B$3:$B$328,"<"&W5+1)/(SUMIF('TradeInputSheet'!$B$3:$B$328,"<"&W5,'TradeInputSheet'!$T$3:$T$328)+SUMIF('Account Transactions'!$B$3:$B$39,"<="&W5,'Account Transactions'!$D$3:$D$39)))

 

[FormR]

Hi, I see it got a bit more complicated. If you wanted to go back to your plan A and retrieve the value directly from column W of the "TradeInputSheet" - then you could try like this:

=INDEX(TradeInputSheet!$W$3:W$328,MATCH(MAXIFS(TradeInputSheet!$D$3:D$328,TradeInputSheet!$D$3:$D$328,"<"&W5),TradeInputSheet!$D$3:D$328,0))