Retrieve time-frame based quotes using OLE Objects

The database that I maintain contains mixed Tick and EOD quotes. Below is an AHK script snippet wherein to access a particular quote I need to loop through all quotes until the _ForDate is reached.

_ForDate := 20180820

oAB := ComObjCreate( "Broker.Application" )
oStocks := oAB.Stocks
Ticker := oAB.ActiveDocument.Name

oStock := %oStocks%(Ticker)
QuotCnt := oStock.Quotations.Count
i := QuotCnt
while( i > 0 )
{
	 oQuote := oStock.Quotations( i )
	 QuoteDt := DateParse( oQuote.Date )
	 if( QuoteDt == _ForDate )
	 {
		 OptC := oQuote.Close
		 break
	 }
	 i--
}

I am interested to pick daily close for selected tickers using OLE. Now it is becoming very cumbersome and time-taking to loop as there are several thousands of quotes between two dates. And it becomes very difficult to externally retrieve other timeframe quotes like weekly, hourly, etc.

Was wondering whether we have an OLE object for specifying timeframe by virtue of which I could trace quotes on the basis of it?

Another alternative which is somewhat working for me is to dynamically manipulate a saved APX and run it as Exploration to export a CSV, then read that CSV to access quotes.

Your two cents please, thank you very much!

1 Like

Why not using LookUp AFL function?

Or something like...

Filter = DateTime() == _DT( "2018-08-20" );

Then you can still export the result list to external file (e.g. Batch -> "Export to File").

Much faster than using OLE!

4 Likes

@Lennon

a totally untested tip: did you verify if quotes dates accessible via OLE are ordered?
In such a case you could try to use a binary search to speed up your process.

3 Likes

@Lennon, I was browsing the forum and saw this thread.

There is an interesting piece of code by @Tomasz. Note this call:

// retrieve quotation that matches split date
	qt = qts.Item( lastsplitstr );

See the OLE doc for the ADQuotations properties:

  • Item (ByVal Date As Variant) As Object [r/o] [default]

This is probably what you need!

4 Likes

Thank you very much @beppe for looking that up!
Shall try it out!

1 Like

@beppe is right. Quotations.Item() method accepts both date and ordinal index. It accepts variant which means that many times can be used. If you pass a number it will use it as ordinal index to quote array, but if you pass string to Item() it will do conversion from string to OLE date and the lookup by date for you.

2 Likes

Thank you @Tomasz @beppe @fxshrat :blossom::blossom::blossom::sunflower::sunflower::sunflower::cherry_blossom::cherry_blossom::cherry_blossom:

Yipee! The loop is gone! :rofl:

_ForDate := 20180621
AB := ComObjCreate( "Broker.Application" )
stks := AB.Stocks
Ticker := AB.ActiveDocument.Name
st := Stks.Item( Ticker )
qts := st.Quotations
qt := qts.Item( _ForDate )
OptC := qt.Close

What a relief !

2 Likes