Jump to content

Welcome to the new Traders Laboratory! Please bear with us as we finish the migration over the next few days. If you find any issues, want to leave feedback, get in touch with us, or offer suggestions please post to the Support forum here.

  • Welcome Guests

    Welcome. You are currently viewing the forum as a guest which does not give you access to all the great features at Traders Laboratory such as interacting with members, access to all forums, downloading attachments, and eligibility to win free giveaways. Registration is fast, simple and absolutely free. Create a FREE Traders Laboratory account here.

Frank

Linking OEC DOM to Excel in Real-Time

Recommended Posts

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...

 

attachment.php?attachmentid=10363&stc=1&d=1240974435

5aa70ec98458f_ExcelDOMCapture.thumb.png.5f823eee2ed944cda480d826f05e5fae.png

Edited by Frank

Share this post


Link to post
Share on other sites

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...).

 

attachment.php?attachmentid=10435&stc=1&d=1241379169

5aa70ecb9ff3b_SnapshotofLiveFeedDashboard.thumb.png.872e56916341afcde5dd4b13fe01046a.png

Share this post


Link to post
Share on other sites

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...

 

attachment.php?attachmentid=10456&stc=1&d=1241472251

5aa70eccad073_200905045MinVolumevsAvg.thumb.png.c53049b83a0593ad0dfef46d774f0f45.png

Share this post


Link to post
Share on other sites

Bretts blog post today is on this intraday volume idea:

 

TraderFeed: Using Relative Volume to Identify When Markets Slow Down

 

attachment.php?attachmentid=10484&stc=1&d=1241612726

 

 

"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??? :)

5aa70ecd47b5e_SteembargerVolume.png.fbcbf1a22b77bed48e9533e9475ecb39.png

Share this post


Link to post
Share on other sites

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)

 

attachment.php?attachmentid=10497&stc=1&d=1241636718

5aa70ecd99dae_VolumeComparison.thumb.png.d4b9c766cd632f1b01c620e45ae3a61c.png

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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).

Share this post


Link to post
Share on other sites

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]

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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....

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.