Counting bars to certain drop in price

[Hans]

Hi,

I want to analyse some stock prices. I want to know the highest high and lowest low from each date in the past for the upcoming 100 days. I also want to know when there is a drop of 10% in price since start date. I was able to code this.

But now I want to know also the date when the drop in price occured and the number of days since start date.

This is the code I have:

lookback = 100;
ddThreshold = 10;

startClose = Close;
startDate = DateTime();

endDate = Ref(DateTime(), lookback);
endClose = Ref(Close, lookback);

highestPrice = HHV(High, lookback);
lowestPrice = LLV(Low, lookback);

highestPriceDate = ValueWhen(High == highestPrice, DateTime(), lookback);
lowestPriceDate = ValueWhen(Low == lowestPrice, DateTime(), lookback);

highestClose = HHV(Close, lookback);
lowestClose = LLV(Close, lookback);

highestCloseDate = ValueWhen(Close == highestClose, DateTime(), lookback);
lowestCloseDate = ValueWhen(Close == lowestClose, DateTime(), lookback);

maxDrawdownLowest = 100 * (highestPrice - lowestPrice) / highestPrice;
maxDrawdownClose = 100 * (highestClose - lowestClose) / highestClose;
drawdownAlert = IIf(maxDrawdownClose > ddThreshold, 1, 0);

Filter = 1;
AddColumn(startDate, "Start Date", formatDateTime);
AddColumn(startClose, "Start Close", 1.2);
AddColumn(endDate, "End Date", formatDateTime);
AddColumn(endClose, "End Close", 1.2);
AddColumn(highestPrice, "Highest Price (High)", 1.2);
AddColumn(highestPriceDate, "Highest Price Date", formatDateTime);
AddColumn(highestClose, "Highest Close", 1.2);
AddColumn(highestCloseDate, "Highest Close Date", formatDateTime);
AddColumn(lowestPrice, "Lowest Price (Low)", 1.2);
AddColumn(lowestPriceDate, "Lowest Price Date", formatDateTime);
AddColumn(lowestClose, "Lowest Close", 1.2);
AddColumn(lowestCloseDate, "Lowest Close Date", formatDateTime);
AddColumn(maxDrawdownLowest, "Max Drawdown (Lowest)", 1.2);
AddColumn(maxDrawdownClose, "Max Drawdown (Close)", 1.2);
AddColumn(drawdownAlert, "DD Close > Threshold", 1.0);

Can someone help me?

[Hans]

Bottom line:

For every day in the past I want to know the number of bars when at least a 4% drop occurs from that day.

[nsm51]

So I reversed the arrays and checked from the initial bars.
I do get visual confirmation on the chart for the Highest and Lowest values which correspond to the future Datetime.

See if you can build from here as I am out of time. Maybe I try over the weekend.

lookback = 100;
ddThreshold = 10;

bi		= BarIndex();
r_DT	= Reverse( DateTime() );
r_close = Reverse( C );
r_high  = Reverse( H );
r_low   = Reverse( H );

HHV_look = HHV( r_high, lookback );
LLV_look = LLV( r_low , lookback );

highestPriceDate = ValueWhen( r_high == HHV_look, r_DT );
lowestPriceDate = ValueWhen( r_low == LLV_look, r_DT );


Filter = 1;
SetOption("NoDefaultColumns", True);

AddTextColumn(Name(), "Sym", 1);
AddColumn( bi, "BI", 1);
AddColumn( r_DT, "DT", formatDateTime, colorDefault,colorDefault, 140 );
AddColumn( HHV_look, "HHV", 1.2);
AddColumn( highestPriceDate, "HDT", formatDateTime, colorDefault,colorDefault, 140 );
AddColumn( LLV_look, "LLV", 1.2);
AddColumn( lowestPriceDate, "LDT", formatDateTime, colorDefault,colorDefault, 140 );

This is a running check, so if you start from the first bar in the Ticker and lookahead n bars, you will print the H/L from that bar.

[Hans]

Hi @nsm51,

This code seems not to wortk. See screenshot.

Any idea how to get it working?

[nsm51]

sorry, i had a typo here

r_low   = Reverse( H );

change it to

r_low   = Reverse( L );

And use a smaller date and range of data or use another ticker for testing.
If you are using a year which is in 1952, I dont know the outcome. Keep date above 1964 for now.

You can enforce smaller data set with AFL Function Reference - SETBARSREQUIRED

[Hans]

Hi @nsm51,

Starting date was 2000. That's was one of the errors. Correction didn't fix the problem. The period is visible in the screenshot.

Also there is no column with the number of day when the 10% drop occurs.

[nsm51]

As i said, its required to use a ticker that "actually" has a few hundred bars in it.

Given the nature of Formula, SetBarsRequired() is not limiting bars for me. And Since we are Reversing arrays, From-Date and To-Date will show very old data.

In this case, you need to set from-to dates of the early bars.

Another way to filter is add this instead

filt_dt = bi < 300;
Filter = filt_dt;

But first use a symbol that has less data so "you" can visualize it in chart.

And, the 10% code is not yet added.

For example, as of 18-jul-2024, in DAILY timeframe,
the index made a High of 26277.35 on 27-sep-2024
and Low of 23350 on 18-nov-2024. Both are within 100 days looking forward

Please read above posts and code properly. Change Filter dates to "All quotes" and use the filter variable filt_dt

[Hans]

I don't get any output. Can you post your complete and final code for me?

[nsm51]

I think we're in far apart in timezones.

You are not getting output, because from-To Dates will filter the last part of the range and we have put filter on BarIndex() for the first few hundred bars.
Both cancel each out.

So far the arrays are reversed, so you have to think like that. Latest bars are now at the start of the array.
Let me post after some refactoring.

[nsm51]

Analysis UI settings:
Periodicity: DAILY
Symbol: Current
Range: From=01-01-2022, To=31-12-2024

AFL:

lookback    = 100;
ddThreshold = 10;

bi		= BarIndex();
r_DT	= Reverse( DateTime() );
r_close = Reverse( C );
r_high  = Reverse( H );
r_low   = Reverse( L );

HHV_look = HHV( r_high, lookback );
LLV_look = LLV( r_low , lookback );

highestPriceDate = ValueWhen( r_high == HHV_look, r_DT );
lowestPriceDate  = ValueWhen( r_low == LLV_look, r_DT );

// All the Calculation you want needs to be done here
// make sure to use reverse arrays like "r_close" and not "Close" etc
// Then later, reverse those Arrays again

// Begin restore: Reverse of Reverse
r_r_DT 				= Reverse( r_DT );
r_HHV_look 			= Reverse( HHV_look );
r_highestPriceDate 	= Reverse( highestPriceDate );
r_LLV_look 			= Reverse( LLV_look );
r_lowestPriceDate 	= Reverse( lowestPriceDate );
/* Reverse other metric arrays also */

filt_dt = bi > (BarCount - 300);
// Filter =	filt_dt; this is AFL filter opposed to Settings Filters in UI 
Filter = 1;

SetOption("NoDefaultColumns", True);

AddTextColumn(Name(), "Sym", 1);
AddColumn( bi, "BI", 1);
AddColumn( r_r_DT, "DT", formatDateTime, colorDefault,colorDefault, 100 );
AddColumn( r_HHV_look, "HHV", 1.2);
AddColumn( r_highestPriceDate, "HDT", formatDateTime, colorDefault,colorDefault, 100 );
AddColumn( r_LLV_look, "LLV", 1.2);
AddColumn( r_lowestPriceDate, "LDT", formatDateTime, colorDefault,colorDefault, 100 );

/*
AddColumn( bi, "BI", 1);
AddColumn( r_DT, "DT", formatDateTime, colorDefault,colorDefault, 140 );
AddColumn( HHV_look, "HHV", 1.2);
AddColumn( highestPriceDate, "HDT", formatDateTime, colorDefault,colorDefault, 140 );
AddColumn( LLV_look, "LLV", 1.2);
AddColumn( lowestPriceDate, "LDT", formatDateTime, colorDefault,colorDefault, 140 );
*/

[Hans]

Hi @nsm51,

I'm getting similar output. But, unfortunately it's not correct (see screenshot). The lowest low price is not matching with the date.

If I change the the lookback value (for example to 5), I'm also getting some errors.

BI stands for Bar Index. Can you tell me what kind of info this column is displaying?

Kind regards,
Hans

[nsm51]

I told you not to use SPX for now and use a symbol with shorter data. It's probably 3rd time i wrote for testing, always be light weight. Then incrementally improve iterations in code.

@Tomasz Given the nature of AFL written above, can dates less than year 1964 affect the ValueWhen() in some way as it is checking datetime condition?
Data range in his SPX ticker is from 1932 to 2024, therefore we might have 2 DT32 dates with same bits representing 2 different dates.
I suspect something in his output which I am not able to reproduce with shorter data from 2000 to 2024 or have I got something wrong in the AFL. (it is quite simple though)
Thanks

[Hans]

Hi @nsm51,

Ok. Sorry for that.

I have same issue with EEM.

[Hans]

And this is the other one. @nsm51

[nsm51]

Add this symbol to your DB using AmiQuote, maybe then we can use a common symbol. I cant see your data
ACC Limited (ACC.NS) Stock Price - Yahoo Finance

Backfill from 2020 in DAILY, that is 1210 bars.
(actually look ahead) Lookback = 50 in AFL, from-to Dates is 01-jan-2022 to 31-12-2024

[Tomasz]

@nsm51

@Tomasz Given the nature of AFL written above, can dates less than year 1964 affect the ValueWhen() in some way as it is checking datetime condition?

No, AFL datetimes cover dates back to 1900 no problem.

[Hans]

Hi @nsm51,

It's not possible (anymore) to download historical data from Yahoo Finance.

Kind regards,
Hans

[Tomasz]

Downloading from Yahoo works perfectly fine just use AmiQuote 4.17

AmiQuote 4.17 released

A new public version of AmiQuote 4.17 is released now. INSTALLATION: If you are using AmiBroker 6.93 you can install AmiQuote update using Help->Check for and Install updates menu To install AmiQuote 4.17 in all other cases, please download and run AmiInstaller from: https://www.amibroker.com/bin/AmiInstaller.exe and follow on-screen directions NOTE: 64-bit version of AmiQuote now requires Microsoft Visual C++ 2022 runtime. The setup program should detect missing runtime and download it fo…

[Hans]

Hi @nsm51,

This is my output:

I loaded all data starting from 1-7-2002:

[nsm51]

so 3rd column "DT" is the bar in reference.
If you see the chart, for the bar=03/jan/2022, look ahead upto 50 bars, HH was 18/jan and LL was 07/mar

then upto 18/01, HH remains and from 19/jan new HH is 01/feb. similarly, LL remains same till 07/mar

this is what you are looking for right?