Linking OEC DOM to Excel in Real-Time

[Frank 10]

for basic info:

 

http://www.openecry.com/software/softwarehighlights_exceldde.cfm

 

I am just kind of learning how to do this and see where it goes but one cool thing that has potential is to link the DOM data into excel in real-time. First real use is to get a precise calculation of vwap since you can weight each price for its exact volume. Another feature is to conditionally format the DOM to change colors per any rule you want to create (there are tons of options in Excel 2007).

 

the rules in this attachment are to highlight all volume bars that are in the top 30% and automatically attach a box to the highest volume of the day and make it bold.

 

Ultimately, you could make a nice 'dashboard' in Excel to aggregate whatever you want -- ie, diff't markets etc...

 

Edited April 29, 2009 by Frank

[Frank 10]

Thought I would just post what I have been working on -- this is still early in exploring this Excel link feature but I think it has potential. This is a screenshot of an excel 'Dashboard' that summarizes some market information for me. Again, this is real-time linked and can sit on an adjacent monitor to help interpret price action in that chaotic time near the open.

 

on left is a table and graph to show how volume is beginning vs the previous day.

 

in the middle is some basic market information --- and on the right are some 30-min charts.

 

this screenshot is from Friday -- you can see volume was low and selling dried up after violating the previous days low.

 

more to come but this is really cool feature in OEC (though this is not exactly easy to set-up -- you need some good understanding of Excel -- ie, 'look-up functions etc...).

 

[brownsfan019 17]

Frank - that's impressive!!!

 

:thumbs up:

[Frank 10]

example of this.

 

today price was pushing up bullishly, closing above 24-hr vwap on opening 15-min bar and kept creeping up -- there were no sellers but volume was tracking average to down -- then at 6:55am (9:55am EST) buyers came in aggressively as price pushed up to the April high at 885.75...

 

[Frank 10]

Bretts blog post today is on this intraday volume idea:

 

TraderFeed: Using Relative Volume to Identify When Markets Slow Down

 

 

 

"I mentioned yesterday how the trade was a difficult one. The above chart draws upon the relative volume concept to show, half-hour by half-hour, how participation in the market compared to 2009 norms. What we see is that, from 11 AM CT forward, the volume in the ES market (blue bars) fell well below their median values for that time period in 2009 (red bars). Recall that volume is closely correlated with volatility: that shift tells us that, unless relative volume picks up, the market is going to move much less as the day unfolds. This is a very helpful alert that can help traders avoid overtrading a narrow, slow, choppy market.

 

Interestingly, the period of waning volume also corresponded to the drying up of selling pressure that I posted. Notice how correlating what is happening in volume with what is happening in NYSE TICK can give us some clues as to *who* is gaining and losing participation: buyers, sellers, or both. This was useful information as the day unfolded, as we were unable to sustain afternoon lows."

 

his chart is clearly done in excel --- wonder if he has it linked in real-time???

[Eiger 10]

Very nice work, Frank.

[Frank 10]

this volume indicator is really a nice complement and I really think its one thing (along with an analysis of range) that can be so much better viewed in a custom-created Excel dashboard than in a chart window....

 

note that the chart below is using west coast time (3 hours earlier than EST)

 

[Frank 10]

just a screenshot

 

[Trendup_ 10]

I tried to use Excel 2002, but didn’t win, I am completely lost.

Could it be a too old Excel? Did someone find the doc helpful??

[Trendup_ 10]

Back to confirm that Excel02 is not suitable, 2003 is the oldest we can use.

Cheers

[Frank 10]

Hi Trendup,

 

I have done a fair bit of further work on this and at times it gets kind of tricky so let me know what you think you would like to see and I can help.

 

I do think that creating your own dashboard is significantly helpful in terms of organizing complex data into something more manageable.

[Frank 10]

As a starter, watch and bookmark this video on how Excel calculates time. This is a bit funky but it is not complex and trust me, it is important to understand how Microsoft has decided to convert dates into serial numbers and time into %'s of a day as keeping track of the timestamp of the OpenEcry feed will ensure that your data is correct.

 

[ame=http://www.youtube.com/watch?v=MtClZ6meTFg]YouTube - How Does Excel Calculate Time?[/ame]

 

(you may have to watch the video 2-3 times before you really get it --- but if you have trouble with this, then excel is probably not for you).

[Frank 10]

ok,

 

here is a starter video on one thing you can do when live-linked to Excel that I have found useful. this relates to nuances you pick-up in tape-reading and order flow by having the data organized and presented in a certain way. its very hard to explain the value in this -- you just have to watch it every day and you begin to pick up clues in the price action.

 

this particular video is about understanding that volume can be better interpreted in context of the time of day that the volume bar is building.

 

[ame=http://www.youtube.com/watch?v=S0EopSNCp1w]YouTube - OEC volume[/ame]

[Trendup_ 10]

I noticed that the total volume reported in Excel ‘Quotes’ table is different than the Total volume shown under the histogram in the OEC DOM, also when I add the lastVol in Excel ‘Quotes’ I can see that the result is different than the increment in TotalVol, It looks like I don’t get the full numbers of transactions in LastVol.

The lastVol must be a snpashot only?

[Frank 10]

hmm, I don't use the quotes table.

 

I use these types of codes:

 

For 5-min Volume:

 

=ACCTNAME|bars!ESU9?5?Volume?0

 

 

For the Time-Stamp of a 5-min bar

 

=ACCTNAME|bars!ESU9?5?Time?0

(note that my Ecry has screwy time stamp that is 7 hours ahead of my time -- but you can do a formula in the next cell referring to that cell such as

 

=A6-(TIME(7,0,0))

 

that adjusts it 7 hours to make it local to my time --

and then format the cell for date and time.)

 

 

For 1-Min Volume:

 

=ACCTNAME|bars!ESU9?1?Volume?0

 

etc....