I've got a local database that stores hourly data for a variety of instruments. I'm trying to get the last DateTime stamp from the hourly database to use in some data retrieval from my provider.
My idea is to setup a ParamTrigger in parameters (and later perhaps a GUI button) that when pressed will run some AFL to get the latest hourly data from my provider. All is working fine except that I cannot retrieve the datetime stamp of the last hourly bar in the database.
My current AFL is using LastValue( DateTime() ) in the ParamTrigger part of the code but if it's selected in a daily chart then it returns the datetime stamp of the last daily bar (which for me is 20181112 00:00:00). If I'm in a 12 hourly chart then it returns the datetime stamp of the last 12 hourly bar (which at the moment is 2018112 17:00:00) even though the last hourly bar might have a datetime stamp of say 20181112 14:00:00.
I've also tried using EndValue( DateTime() ) but it returns the same results. Experimented with TimeFrameSet/Restore but that hasn't helped either (but that could be because I don't know what I'm doing).
Is there a method or some AFL code I can run to always return the datetime stamp of the LAST bar in my hourly database no matter which type of chart it's called from. Ultimately I'd like it to return "20181112 14:00:00" based on my example above regardless of whether it's trigger from a daily, 12 hourly or 3 hourly chart.
If anyone's got any ideas I'd be very grateful if you could share.
"lastbarend" - returns DateTime of the end of last bar. For example 5 -minute bar at 9:00 will have end time of 9:04:59 (works for time-based bars only) (v5.60)
"lastbartimeleft" - returns number of seconds to the completion of current last bar. Works for time-based bars only. Note that for proper operation this requires database timeshift to be set properly (so dates displayed on chart match your local computer time zone). (v5.60)
"lastbartimeleftrt" - it works like "lastbartimeleft" but uses the most recent RT stream update time instead of Now(). Also added Status("lastrtupdate") - time of last RT stream update Depends on RT plugin to deliver correct DateUpdate / TimeUpdate data. If plugin or date source sends incorrect datetimestamps or does not send DateUpdate/TimeUpdate correctly this function will not operate properly. Note that most data sources send weird (not current) datetime stamps on weekends. Also IQFeed plugin sends DateUpdate/TimeUpdate only inside regular trading hours. (v5.60)
"lastrtupdate" - returns date time of last update sent by RT plugin (see remarks above) (v5.60)
I probably should have been a bit more clear in my original post. I'm not using a RT data source. I'm able to download data from my provider and I'm importing it into the local database.
I tried the Status("lastbarend") but it didn't return the datetime of the last hourly bar in the database (no doubt because I'm not using a RT data source).
Is there anything else I can whip up in AFL that will permit me to retrieve the datetime stamp of the last hourly bar I have in the local database? I'm a bit stumped with this one. Been racking my brains for days.
Did you try the option I suggested to atleast get the LAST Tick bcos it does it very well.
Especially for non-RT local DB.
I didn't mention, which Tomasz correctly pointed out, it won't do any harm but you should be carefully using it as it may break other formulas written that may be designed on START time of bars etc
so far, with All built-in AB functions, i've never had any problems.
This worked very well when I had to scrape data from some different sources.
Yeah I did try that - it gives me results like Tomasz documented above but if I was in a daily chart it gave the last time of the daily, similarly for 12 hourly, 3 hourly. I really just want to get the datetime stamp of the last hourly bar that's in the database regardless of which chart I'm in. I just can't figure out a way to do that.
There is something definitely different that you are doing which is not obvious.
Firstly, Time resolution is one way going up, that is, a 3H a chart or a 12H chart can't Look into hourly.
While writing this post, I'd sensed the problem and that is why thought of mentioning the Intraday LAST Tick change in settings. This has to work.
Now, can you take out some time and post the code that you are using to test, whether its in the chart or AA. What is the Interval().
Post the DB settings, and any other changes if you've made to the defaults.
Also, post snaps of the output you get, and debug output etc if its there.
Low and behold I'm now getting the datetime stamp of the last hourly bar. This is good.
I've never used "time of LAST tick inside bar" before. Usually I've just had the defaults of "time of FIRST tick inside bar". Is there any downside to making that change? Something I should be looking out for?
As I've mentioned in above post, it has worked very well with AB and all AFL that comes along with it.
For your own formula or 3rd party ones, just have to make sure they haven't hardcoded anything that works or checks a condition based on fixed time.
For example, in a 5min chart, at time 13:20:00
START Time: LastValue( DateTime() ) after formatting would return 13:20:00 for the whole period of the bar, whereas,
LAST Tick: would keep updating with last time,
so just make sure Time isn't hardcoded anywhere or certain counting using ValueWhen() etc
Edit: Also, just check that reverting back to Default START Time doesn't serve the purpose and all this wasn't because of some other issue.
Yeah I was just doing some checks and changing that setting to LAST tick has stuffed up some automatic trendlines that I'm drawing via AFL. I'll have to look into that and see why it's causing problems.
And from memory I'm using a lot of ValueWhen() in my code
For what it is worth I've sort of stumbled across a solution to my problem. Posting for the benefit of anyone else that might think like me
The reason I was trying to find out the last timestamp of the hourly data in my database was to use that date to retrieve hourly bars from my provider since that date. It would make my retrieval just that little bit more efficient. What I didn't notice was that the LastValue( DateTime() ) for higher timeframes which I tend to look at was OLDER than the latest hourly data as I've set my preferences as above to have Intraday data set to "START time of interval (recommended)". So if I'm looking at the 12 hourly chart and I press my button to retrieve data from my provider it uses the LastValue( DateTime() ) for that period which is older than the hourly data. What I hadn't contemplated is "who cares". Basically I'm submitting a request to my provider with more historical data than I require. When I import that extra historical data (which is already in the AmiBroker database) AmiBroker just deals with it. Doesn't create duplicates.
So I've got a workaround but I'm still on the hunt for getting that last timestamp for the hourly data. I'll put it on my list of "Things to Do"!
There are a few elegant ways of doing it still.
In your case, you didn't discuss your setup elaborately. It was more about the date/time itself.
By logical design of AB, Timeframes can be worked in correct multiple going up.
That is why ideally for example, people prefer the 1 min timeframe for intraday.
You can extrapolate any higher TF from it, like 2m, 12m, 59m and so on.
Another pointer in your case which comes to my mind is very simple and can be part of the automation so nothing manual required.
The object can be called from within AFL or your own application or other bit of code or JScript file running on a timer or a scheduled Task etc, but a pseudocode is like this:
// Create object
var oAB = new ActiveXObject("Broker.Application");
// Get Stocks
var oStocks = oAB.Stocks;
// Do a one time search and get the ordinal number of reference Symbol
i = 10; // example
oStock = oStocks( i ); // or just find it by name property etc
// Get Total Bar count, adjust to zero base.
j = oStock.Quotations.Count - 1;
// That's datetime of last quote
tmpDateNum = oStock.Quotations(j).Date ;
This is part of JScript code used to delete quotes but can used to do anything.
I never thought about using OLE to get the information. That should fit in nicely with an external node.js script I've written to retrieve the data from my provider.
Just a hint, you can use actual symbol name in the oStocks call:
oStock = oStocks("MSFT"); // instead of ordinal number
Also it can be done directly from AFL, but you need version 6.30 to read Date field since it is in MS OLEDATE format and only 6.30 is able to auto-convert it to text:
Version( 6.30 );
Ab = CreateObject("Broker.Application");
stk = ab.Stocks(Name());
qts = stk.Quotations;
qty = qts.Count;
qt = qts.Item( qty - 1 );
Title = "Last DB timestamp is " + qt.Date;
OK - upgraded to 6.30 and the above is working a treat. Just what the doctor ordered. Thanks for everyone's assistance. Learnt a few more things this time round.
