get time and sales from esignal to excel

[overbet]

Hi all. Does anyone know of a way to get time and sales for a group of stocks at a particular time of day? For example, say I want to retrieve the prices of where 100 different stocks were trading at 1 hour before the close. I was hoping to write something in excel because VBA is the only thing i know. Thanks

[overbet]

Anybody? 11 views 0 replies, is it my deodorant?

[eSignal_AveryH]

Overbet,

Let me look into some different options and I'll post back once we have a solution or two.

Thank in advance for your patience.

[eSignal_AveryH]

overbet,

For example, say I want to retrieve the prices of where 100 different stocks were trading at 1 hour before the close.

Are you looking for what the price was or would be at that hour (a Price Bar) or the price action during the last hour of trade (Time and Sales)?

Getting a historical price bar would be much easier then actually trying to pull an hour’s worth of T & S data for 100 symbols.
With bar data you can limit the amount of data by passing a time variables for the data. All Time and Sales request are done in days. You'll receive the tick data from the moment of the request back to the start of the day. Once you would all the data you need you could then discard the remaining data.

QLINK— eSignal QLink is a Dynamic Data Exchange (DDE) add-on service that makes it quick and simple to download real-time, streaming data into your Excel worksheets. You can then perform further analysis suited to your market and your strategies for trading that market.
Here is KB Article 4185 on QLINK and the functions available for retrieving historical T & S data as well as Historical Bar data.
Active X API-- eSignal Desktop API--- this would allow you to program in VBA requesting historical bar data. With the history request as method above would allow you to specify the time span of the data (see below)
eSignal.RequestHistory(“sSymbol”, 1, “btBars”, 60, 780, 870). Here is the breakdown of the request.

• sSymbol-- The symbol for the data request
• 1--- Interval of the request ( 1minute bars)
• btBar—Bar Type
• 60--- number of bars to return
• 720 Time of the first bar (12 pm PST)
• 750 Time of the last bar (1:30 pm PST)

The start and end times are computed as:
(Number of hours * 60) + Minutes
For example, if start_time = 570 and end_time = 960, the request scans for data between 9AM and 4PM each day that applies to the request.

Hopefully this isn't too much information and is clear. Please let me know if you have any questions.

Another note is the number of symbols your requesting would be taxing on any solutions provided. To prevent overloading of the eSignal history servers we try and limit the number of history request (symbols) per History connection. It might be easier to break up the request is to smaller subsets and processing each subset separately.

[overbet]

Thank for the help. To clarify what I would like to do: It does not need to be real time I want to back test strategies. For example say I want to test buying a dozen or so oil stocks on the open. I then would like to know what they were all trading at say 15 minutes after the open then again 30 minutes after the open and so on. So I think I do not need full day tick by tick time and sales. Is your suggestion still the best or is there something else you recommend now that I have clarified? i think the qlink looks like it will work i will try it today. Thanks Jason

[eSignal_AveryH]

Thanks for the clarification. For the scenario you describe below QLINK would be your best option. Doing a 15 minute Bar Request at the end of the day would give you the information you need so that you could work your strategies.

[overbet]

Thank you again for the help. I plan on starting on it this weekend and I appreciate the point in the right direction. Happy Memorial Day.

[ScottJ]

BTW, you get the first month free when you add QLink to your account so you can basically test all this out for free.

http://www.esignal.com/products/specials.aspx#qlink

Thanks.