Reference or LookUp the Previous Close of a specific time(Bar)

Hi,

I am using the below code on hourly data. I would like to reference or lookup the previous day's 11AM Closing value. I have tried entering a specific date/time and that does not produce the correct results. ideally, the code could be generic enough to always know to pull the previous day's 11am close price without hardcoding the specific date.

Thanks,

Mike

//Below filter for debugging AFL
AddColumn( Close, " Close", 1.6 );
//AddTextColumn( Now(), "Current time");
AddColumn( Ref( C, -1 ), " Close 1-Hour ago", 1.6) ;
AddColumn( Ref( C, -2 ), " Close 2-Hours ago", 1.6) ;
AddColumn( Ref( C, -3 ), " Close 3-Hours ago", 1.6) ;
InputDate = "01/14/2022 11:00:00";
AddColumn(Lookup( Close, _DT( InputDate ) ) , "11:00 AM Close PrevDay", 1.6) ;

Below is the current output:
image

Usw ISO Format and try again. Also check analysis interval.

InputDate = "2022-01-14 11:00:00";

(Also check whether 11th hour bar actually exists)

1 Like

fxshrat,

As usual, you were correct on the ISO format and intuitive on the analysis interval, which I had set to Daily.

The code will reference and look up the correct values for January 13th & 14th as hardcoded. Instead of specifically referencing the dates and changing the dates each subsequent day i run the Exploration, how would it be coded to always look back 1 day and 2 days?

Thanks,

Mike

//Below filter for debugging AFL
AddColumn( Close, " Close", 1.6 );
//AddTextColumn( Now(), "Current time");
AddColumn( Ref( C, -1 ), " Close 1-Hour ago", 1.6) ;
AddColumn( Ref( C, -2 ), " Close 2-Hours ago", 1.6) ;
AddColumn( Ref( C, -3 ), " Close 3-Hours ago", 1.6) ;
//InputDate = "11:00:00";
InputDate_1 = "2022-01-14 11:00:00";
InputDate_2 = "2022-01-13 11:00:00";
//InputDate = "01/14/2022 11:00:00";
AddColumn( Lookup( Close, (_DT( InputDate_1) )) , "11:00 AM Close 1 Day Ago", 1.6) ;
AddColumn( Lookup( Close, (_DT( InputDate_2) )) , "11:00 AM Close 2 Days Ago", 1.6) ;

You may use Valuewhen() function together with TimeNum() check.
Note: Valuewhen() function has three arguments!

Thanks fxshrat...I will give the Valuewhen() function a try....Mike

This topic was automatically closed 100 days after the last reply. New replies are no longer allowed.