calculate the longest loss period

do you have an idea how to calculate the longest loss period in one line(without help-lines D & E..)?

the longest loss period means, if somebody did buy a share on the at this time highest price, how long has he had to wait, until he's not in a loss anymore (more details see the excel file in affix).

at least i found a way to calculate ;-) but i'm looking for a more easy way without help-lines, do you have an idea?

thank you


---
data to calculate the longest loss period:

date Price
2013-01-03 64
2013-01-10 61
2013-01-17 63
2013-01-24 61
2013-01-31 62
2013-02-07 64
2013-02-14 63
2013-02-21 64.5
2013-02-28 65
2013-03-07 66
2013-03-14 67
2013-03-21 68.5
2013-03-28 66.5
2013-04-04 67
2013-04-11 68
2013-04-18 69
2013-04-25 70
2013-05-02 69
2013-05-09 69.68
2013-05-16 72
2013-05-23 74
2013-05-30 71
2013-06-06 70
2013-06-13 69.5
2013-06-20 71.3
2013-06-27 70
2013-07-04 70.96
2013-07-11 70.28
2013-07-18 71.71
2013-07-25 72
2013-08-01 72.5
2013-08-08 73
2013-08-15 71.79
2013-08-22 72.2
2013-08-29 69.66
2013-09-05 69.25
2013-09-12 72.16
2013-09-19 71.72
2013-09-26 71.76
2013-10-03 71.16
2013-10-10 67
2013-10-17 66.68
2013-10-24 66.57
2013-10-31 68.48
2013-11-07 70.57
2013-11-14 73.98
2013-11-21 78.3
2013-11-28 77.68
2013-12-05 74.37

see attachment

you are a real excel guru, thank you very much!

the formula works perfect, but with some tousands of data, even my new computer goes to the limit with all of this index matrix calculations in every single row...

does anyone know how to translate this formula into a .xla, that i only have to select the data & excel calculates the longest loss period? VBA could be a solution, or do you have another idea how to get the result without calculate in every row or less calculation power?

thank you

i found an error in the first excel sheet, have a look to C50, there's an error too.

i fixed it with an if formula, have a look at the new formula in C50

=IF(AND(B49<B50;B50>B51);(IF(B50>=MAX(B51:B$52);"";INDEX(A51:$A$52;MATCH(B50;B51:$B$52;1)+1)-A50));"")

and in affix the calculation without the error.

you may have to add the if formula in the VBA code to fix the error?

look, i found a very fast way to calculate the longest loss period (see the Attachement) ;-)


do you now a way, how to put this code into an add-in (.xla file)?

i have so many spreadsheets, i'm not able to start the macro every single time... so i want to convert it into an add-in, that it's just necessary to put for example "=maxVZ(data)" and it will calculate the longest loss period in days.?

to calculate the date is not necessary, just the number of day's counts.

??

thank you

i have serious doubts about that macro, about the speed and the results.
so, in attachment, you find both macros. ;-)
I made a kind of sinusoide with noise for your valuta with almost 2.800 rows.
My macro makes a top-900 list (you can limit him to a Top-10 list) of all the longest loss periods.

is the layout of all your spreadsheets very similar, for example the table with data starts each time at A1 and you want your top-10 always in I2:M11 ?
Then it's easy to do a loop with that macro.

thank you, okay your calculation is a bit more fast now ;-)

well, at the end i have more than 100 spreadsheets with around 2'000 data each spreadsheet or more. some spreadsheets are very similar, others not & i need the calculation in almost every sheet :-( so a loop is no solution & that's why i need the function i think.. :-(

and i just need the longest loss period, second longest loss period doesn't matter..

i have a function for the maxDrawdown & it works perfect, you see in attachment.

i found a solution, have a look in the attachment ;-)

with a function it's the best and a very fast calculation. the only problem is, what to do if the longest loss period is now? with this function, if the longest loss period is now, it doesn't show's because it has not a higher price, so it shows the second longest loss period...

any idea?

here's the final function (see the attachment), it also works when the longest loss period is now. it calculates from the last Point > til now.

thank you for the Support.

if you're happy, then it's end of discussion, but when i like at the graph, that is certainly not a correct answer.
Your valuta increase all the time, so the longest loss-period = 0, because you always gain.
There is somewhere an error in that macro or i missunderstand your question.

hehe, yea you're right, there's still a little fault in the formula. that's because if the formula can not find a loss period, it takes the minimum of the available dates & in my example it's 7 because i took weekly dates...

i calculate these formula on shares, so they always go up and down over some time-period. so for my use the formula is good enough.

but yea, in my example the answer is not 100 % correct & should be 0, does anyone can solve this last little problem?

happy with this (see the attachment) ;-)

it's now the perfect formula, even more then i expected..

Originally Posted by bsalv

if you're happy, then it's end of discussion, but when i like at the graph, that is certainly not a correct answer.
Your valuta increase all the time, so the longest loss-period = 0, because you always gain.
There is somewhere an error in that macro or i missunderstand your question.

I spent a little time overlooking all the solutions and in attachment, you see the result.
I made a function for both point of views. : F_MaxVZ en F_LongestPeriod, so you need that xla no longer.
The results fo those functions are the orange and light blue cells in "blad1" (cells K7 and N7)
there you enter the formula

Please Login or Register  to view this content.

and you make an enter, then you select 6 cells K7:L9, you push F2 and then CTRL-SHIFT-ENTER (those 3 keys together) and you have your answer. Now you only have to change the formats to date and valuta.

The yellow and dark blue cells are the result of the macros (push the green buttons), but you don't need them no more, you can delete the buttons and the macros.

There is 1 difference between the 2 functions :
I modified the last cell B5000 to 800, so there is a feasible solution and both functions become the same result.
If you change that cell to 700, then for a certain date(2108-10-11 valuta 741.25€) you can't find a longest period, because that period isn't finished yet.
So my solution (orange cells) gives NA (No Answer of Not Available), the MaxVZ gives the best feasible solution.
So it's up to your exceptations, i can modify my function, so that the answer is the same.