Hello,
I've got most of the way with this but now I'm stuck.
Throughout, I've been using List.Generate, modifying Phil's RT and GRT functions to suit.
Scenario - For each row of gains, there is a signal which initiates a position.
A signal of 1 initiates a position of 1.
A further signal of 1 adds to the position. There is a maximum position of 2 allowed.
A signal of 0 means no change to position.
A signal of -1 takes current position to 0.
Return is then calculated from gain x position.
The returns are then grouped whilst there is a position and a compounded return for each group calculated.
This is as far as I've got.
What I'd like to achieve next is to reset any position to 0 when its compounded return reaches -1% or less.
This would also reset the grouping and thus the compounded return
So, in effect, any initiated positions would be reset to 0 either with a signal of -1 or a compounded return <= -1% and would not initiate again until the next signal of 1.
For example, in the attached file, on the 24/08/2023 the group comp return was -2.49%. On the 25/08/2023 I want the position to reset to 0 along with the grouping and group comp return. The position would then stay at 0 until the next positive signal on the 31/08/2023.
If anyone could help with this it would be much appreciated.
Andy
To accomplish your goal of resetting the position to 0 when the compounded return reaches -1% or lower, you can modify your fxRunningTotal and fxGroupCompReturn functions to include this logic.
Step 1: Modify fxRunningTotal
You want to ensure that the position is reset when the compounded return hits the -1% threshold or when there is a signal of -1
. Here is how you can modify the function:
fxRunningTotal = (values as list, compReturns as list) as list =>
let
RT = List.Generate(
() => [RT = values{0}, counter = 0],
each [counter] < List.Count(values),
each
try
if values{[counter]+1} = -1 then
[RT = 0, counter = [counter] + 1]
else if compReturns{[counter]} <= -0.01 then
[RT = 0, counter = [counter] + 1]
else if [RT] + values{[counter] + 1} > 2 then
[RT = 2, counter = [counter] + 1]
else
[RT = [RT] + values{[counter] + 1}, counter = [counter] + 1]
otherwise [counter = [counter] + 1],
each [RT]
)
in
RT
Step 2: Modify fxGroupCompReturn
In the compounded return calculation, you need to reset the grouping and compounded return when the threshold is breached. Here’s how you can modify your function:
fxGroupCompReturn = (values as list, grouping as list) as list =>
let
CCR = List.Generate(
() => [CCR = values{0}, counter = 0],
each [counter] < List.Count(values),
each
try
if grouping{[counter]} = grouping{[counter]+1} and [CCR] > -0.01 then
[CCR = (([CCR]+1) * (1 + values{[counter]+1})) - 1, counter = [counter] + 1]
else
[CCR = values{[counter]+1}, counter = [counter] + 1]
otherwise [counter = [counter] + 1],
each [CCR]
)
in
CCR
Step 3: Update the Query with the Modified Functions
You will also need to pass the compounded returns to your fxRunningTotal
function. Here is the revised query:
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
BufferedSignal = List.Buffer(Source[signal]),
BufferedReturns = fxGroupCompReturn(List.Buffer(Source[Gain]), List.Buffer(Source[signal])),
RT = Table.FromColumns(
{
Source[Date], Source[Gain], Source[signal], fxRunningTotal(BufferedSignal, BufferedReturns)
},
{
"Date",
"Gain",
"signal",
"position"
}
),
return = Table.AddColumn(RT, "return", each [Gain]*[position]),
grouping = Table.AddColumn(return, "grouping", each if [position] = 0 then 0 else 1),
GCR = Table.FromColumns(
{
Source[Date], Source[Gain], Source[signal], fxRunningTotal(BufferedSignal, BufferedReturns), return[return], grouping[grouping], fxGroupCompReturn(List.Buffer(return[return]), List.Buffer(grouping[grouping]))
},
{
"Date",
"Gain",
"signal",
"position",
"return",
"grouping",
"group comp return"
}
),
#"Changed Type" = Table.TransformColumnTypes(GCR,{{"Date", type date}, {"Gain", Percentage.Type}, {"return", Percentage.Type}, {"group comp return", Percentage.Type}, {"signal", Int64.Type}, {"position", Int64.Type}, {"grouping", Int64.Type}})
in
#"Changed Type"
Thank you for your step by step solution Catalin, so helpful.
This is my first attempt at custom functions (and List.Generate) and it's been quite difficult to understand.
Could you please explain this line:
BufferedReturns = fxGroupCompReturn(List.Buffer(Source[Gain]), List.Buffer(Source[signal]))
Let's break it down:
Components of the Line:
-
List.Buffer(Source[Gain]):
- This converts the
Gain
column from your
Sourcetable into a buffered list.
-
List.Buffer
is used to create a stable list in memory, which helps improve performance, especially when the list will be accessed multiple times, as in this case.
- This converts the
-
List.Buffer(Source[signal]):
- Similar to the above, this converts the
signal
column from your
Sourcetable into a buffered list.
- Similar to the above, this converts the
-
fxGroupCompReturn(List.Buffer(Source[Gain]), List.Buffer(Source[signal])):
- This is a function call to
fxGroupCompReturn
, which is a custom function you've defined to calculate the compounded returns.
- It takes two lists as inputs: the list of gains and the list of signals.
- These lists are used within the
fxGroupCompReturn
function to compute the compounded returns across rows, taking into account the grouping logic you defined.
- This is a function call to
-
BufferedReturns:
- This is the variable that stores the result of the
fxGroupCompReturn
function.
- After this line is executed,
BufferedReturns
will contain a list of compounded returns for each row in your data, based on the gains and signals.
- This is the variable that stores the result of the
Purpose in the Query:
-
Why this is Important: The compounded returns calculated by
fxGroupCompReturn
are needed later in the query to determine when to reset the position to 0 based on the -1% threshold. By computing and storing these returns in
BufferedReturns, you're able to reference them in the
fxRunningTotalfunction when deciding whether to reset the position.
-
Usage in the Query: After calculating the
BufferedReturns
, they are passed to the
fxRunningTotalfunction. This allows the
fxRunningTotalfunction to consider not just the signals but also the compounded returns when determining the current position.
In essence, this line calculates the compounded returns based on the input gains and signals, stores these returns, and then makes them available for further calculations within the query.
Thanks Catalin, its really great to have the code broken down like this.
I'm now confused by something else though.
When calling the function fxGroupCompReturn you have used a list of signals as an input.
Returns are calculated from Gain x position and I was thinking the input should be a list of positions.
I wanted to test this myself but as position is calculated from the fxRunningTotal function, I'm not sure how to write this as BufferedReturns is used within fxRunningTotal function.
I have added some extra data to the example file (see attached) and there is no reset on the 5/9/23 when there should be.
I've been giving this some more thought.
fxGroupCompReturn should calculate compounded returns by group.
Its inputs should be, return (Gain x position) and grouping (also derived from position).
I've tried writing: BufferedReturns = fxGroupCompReturn(List.Buffer(return[return]), List.Buffer(grouping[grouping]))
but I get a cyclic reference error, as I thought I might.
Do you have any fresh thoughts on this Catalin?
I'll keep trying to figure this out in the meantime.
