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.

gomes

MP on excel

Recommended Posts

ryker, bobajob,

 

KUDOS to both of you for doing such a great job... but more importantly being so generous in sharing it.

 

What I'd like to do is link your sheets to DDE link for esignal, so that I can have it dynamically updating during the trading day.

 

How would I go about doing this?

 

I'm proficient with excel but not as versed in intricacies of programming it. Can either you point me in the right direction or walk me through it?

 

Thanks in advace!

 

Again, thank you in your generosity.

 

Agora.

 

I'm currently working on a version that will get data from csv file, so maybe it will be easier for you to update it?

DDE is not always good cause Worksheet_Change event in VBA is not fired by DDE changes (you've got to use another event).

Not finished yet, but will be ready soon.

Share this post


Link to post
Share on other sites
ryker,

 

Great stuff. How would I go about hooking up your sheet to esignal through a DDE link?

 

Agora

 

How many symbols do you need to follow?

If it's a few something could be possible, you need to use Worksheet_Calculate Event, find which values has changed on the sheet and then update data sheet with values (you'll need to build 30 mins bars with that data).

It's not that difficult to do but you need to be a bit confident with Excel. I've tried to do something like this but stopped cause for a lot of symbols this is not efficient... I think I still have an example at work, will look tomorrow.

Share this post


Link to post
Share on other sites

ryker,

 

Thanks. I probably just need to follow 2-3. ES, SP-full, mini-dow. But even one of those would be helpful like the mini-SP.

 

How do you tell excel that something new came into a cell and it needs to do something with it? That's probably where I need to begin.

 

Thanks for your generosity in sharing your knowledge.

 

Agora

Share this post


Link to post
Share on other sites

ryker,

 

Also, I guess if you're doing a csv file, that might even be better since that'll hold the historical data. I would probably need to see several days of MP in order to determine the plan of attack and having excel recalculate the whole thing would be inefficient. It just need to do that once and then add new data as it comes in during the trading day.

 

Agora

Share this post


Link to post
Share on other sites

hey ryker,

I think something wrong from my end, with the same data, 4 time slices I don't have value in my MP. I will try to see what is problem, hope I can fix this.

Thanks for your help.

Regards

Humberto

5aa70dce50aca_samedata.thumb.PNG.948024cd6c8e4860a6a513f7d58b7da2.PNG

Share this post


Link to post
Share on other sites

Hey ryker,

I found the problem. I was putting the date data prefix so when I clear this I got it working properly finally. I am good to go now.

I have one more question if I may bother you.

What is the gray A in the first bracket, I am just curious about that.

Thanks for all of your efforts and time.

Regards

Humberto

5aa70dce56af5_grayA.thumb.PNG.0bc6cfa24bc5e1d10fe5c07791e1a3c3.PNG

Share this post


Link to post
Share on other sites
Hey ryker,

I found the problem. I was putting the date data prefix so when I clear this I got it working properly finally. I am good to go now.

I have one more question if I may bother you.

What is the gray A in the first bracket, I am just curious about that.

Thanks for all of your efforts and time.

Regards

Humberto

 

Good :)

 

The gray A is the middle of the distribution, was wondering also if this is really necessary?

 

Was also wondering if I should add:

-- TPO count on the right of the distribution (is this useful?)

-- MP values at the top of the chart (under the date maybe?)

 

Any suggestions?

 

Thanks

Share this post


Link to post
Share on other sites
As I said in my post you can get a real-time import of data into excel from a web page - this is how I do my real-time charts

 

bobajob,

 

How did you link it to a web site? Or is the website already configured to do this?

 

Agora

Share this post


Link to post
Share on other sites

Right - in quote.com you can export data to webpage. If you do this you get a webpage full of data. Turns out that the webpage is always there at the same address -it just updates. So you can go into any excel spreedsheet and go to Data - Import External data - new web query. Then type or copy the address of this webpage with the data. You can then select the data from the page that you wan to import.

It will take a few seconds and then appear in the sheet. Then you can refer to this data for your own purposes. You do have to update the sheet yourself - although I'm sure with VBA you can write a script to do this. I haven't myself yet but I will soon. I would give you mine but it is linked to my quote.com account for which I pay for, so I'm not giving that away.

Share this post


Link to post
Share on other sites
Right - in quote.com you can export data to webpage. If you do this you get a webpage full of data. Turns out that the webpage is always there at the same address -it just updates. So you can go into any excel spreedsheet and go to Data - Import External data - new web query. Then type or copy the address of this webpage with the data. You can then select the data from the page that you wan to import.

It will take a few seconds and then appear in the sheet. Then you can refer to this data for your own purposes. You do have to update the sheet yourself - although I'm sure with VBA you can write a script to do this. I haven't myself yet but I will soon. I would give you mine but it is linked to my quote.com account for which I pay for, so I'm not giving that away.

 

boba,

 

Thanks for a great explanation. I'll have to see if I can somehow link esignal with their DDE.

 

Agora

Share this post


Link to post
Share on other sites
Good :)

 

The gray A is the middle of the distribution, was wondering also if this is really necessary?

 

Was also wondering if I should add:

-- TPO count on the right of the distribution (is this useful?)

-- MP values at the top of the chart (under the date maybe?)

 

Any suggestions?

 

Thanks

 

Hey ryker,

Yes I like to see that gray price, so I can easily see if the action is on the upper half or bottom half of the profile, I think it is necessary for me.

TPO count and MP values would be super duper feature. I was going to ask you if you can add this feature but I didn't want to ask to much, you already share your hard work. I was kinda afraid to ask.:o If you can add this features will be very much appreciated.

Thanks in advance

Regards

Humberto

Share this post


Link to post
Share on other sites

Kudos to bobajob on a great MP spreadsheet. It was just brilliant of you

to think of using CONCATENATE to create the row of letters from the separate letter columns! I have made a small addition to your

spreadsheet "ES Profile Maker Next Gen."

 

I added a feature that will calculate volume per tick using tick data for

the KOSPI200. In bobajob's current implementation, the profile appears

perfectly, but there is no volume information for how many contracts

were traded at each price point. I hope everyone will find this minor

addition useful. This feature was added using the excel function SUMIF.

 

Cheers,

Jun

MP2-26-07 example.rar

Share this post


Link to post
Share on other sites

Am I right in thinking that the only way to use bobajob's spreadsheet is to input the TPOs manually? If so I'm going to produce my own Excel VBA code that produces the profile automatically from IB data without the need to be fumbling around inputing letters into a spreadsheet. I like ryker's design but unfortunately (and understandably) he's password protected the VBA code. I think I can do the whole thing from scratch though.

 

Edit: ah, I see how bobajob has done it, using the formula box. It seems you still have to enter the data for each column manually. I'm going to automate the whole process with IB data and VBA.

Share this post


Link to post
Share on other sites

Hi guys thanks for the feedback - I've actually got two different versions of my software now. The most refined version uses buttons to automate the process, but it is a learning tool. Therefore it does not use realtime data. The program essentially allows you to replay the day to see how the profile is developing. The idea is that you can teach yourself to recognise patterns in the market. It is really trying to give you years of market experience in a very short time. I have had some success with this but it seems my trades are very short term - usually involving trading at the extreme of a bell distribution and closing the trade at the centre of the distribution. This kind of trade happens low risk over and over again in a day.

 

I was hoping to sell that spreedsheet for a very small price - but I would be happy for a few people to try it out. Therefore if you PM me I will send a few copies out - provided i get some feedback.

 

The next version is my realtime one. As I have mentioned previously my version updates from Quote.com. The reason the one you guys have doesn't is that it will access my account and obviously I can't send that out to people.

 

I love that you guys are modding it to do other things - that's great. I have previously done implementations with volume but can't for the life of my get reliable volume signals for trading. If someone could explain some I would be grateful.

 

Regards

Share this post


Link to post
Share on other sites
Am I right in thinking that the only way to use bobajob's spreadsheet is to input the TPOs manually? If so I'm going to produce my own Excel VBA code that produces the profile automatically from IB data without the need to be fumbling around inputing letters into a spreadsheet. I like ryker's design but unfortunately (and understandably) he's password protected the VBA code. I think I can do the whole thing from scratch though.

 

Edit: ah, I see how bobajob has done it, using the formula box. It seems you still have to enter the data for each column manually. I'm going to automate the whole process with IB data and VBA.

 

Hi notouch,

I'm currently working on a realtime spreadsheet (working with bloomberg) but also planning to allow realtime update from another third party, I'm doing this via a file that need to be updated. I can't say when it will be ready but I think it won't take that long. I'll be happy to share it but concerning the code I'm not really sure yet... But this should allow you to use my spreadsheet with IB. I've done some nice improvements since last version. I will post here as soon as I've last version.

 

Here:

© CJoint.com, 2007 is a screenshot of an autoupdateable version with last day splitted (can choose to merge it to obtain a normal profile).

 

 

++

Share this post


Link to post
Share on other sites

Here is the last version.

 

Setup to use it:

- First things to do is to install necessary addins (Analysis tools and Analysis tools - VBA).

- You need to setup your own list of securities by following the examples (you can change the bloomberg ticker by the symbol code you're usually using).

- Import your own data (must be 1min data): copy your filed into the import folder, rename them (something like: symbol.csv), and then press the Update data button (could take a while, you could check excel is still processing by looking into the logs).

- Do not try to import more than one year in one time.

 

How to use it:

- You have to make sure Begin and End date correspond to some trading day (if not program could crash, still had to be improved there...).

- If you want to have an autoupdate (button EnableAuto), you need to have a program which update the file called symbol..csv) every minutes in the folder AggregatedDay.

- Split button shows the last day in a splitted view (only work with the last day).

- Merge shows a normal distribution profile.

- Do not try to plot too many days or you'll hit excel limitations.

- Enable auto works properly in a splitted view, and doesn't work really well in a merged view (I'm not using it in realtime).

 

This is a quick description will try to improve it a bit later. I've inserted some screenshots of the different views available.

 

Ryker

TL-MP.rar

merge.png.200c85c3818775661bd71b7672836986.png

split.thumb.png.ec0bf89f996effa8b332de5f25f2e0f9.png

Share this post


Link to post
Share on other sites

I don't seem to be able to edit my post again...

 

So a few more thing:

- Format in the file to import data:

DateTime,High,Low,Open,Close,Volume

01/03/2007 07:00:00,115.83,115.83,115.83,115.83,0

Don't include first line with header.

- Edit on the name of the file for autoupdate: yyyymmdd.symbol.csv

Same format as above.

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.


  • Topics

  • Posts

    • also ... and barely on topic... Winners (always*) overpay. Buying the dips is a subscription to the belief that winners win by underpaying - when in actuality winners (inevitably/always*) win by overpaying... it’s amazing the percentage of traders who think winners win by underpaying ... “Winners (always*) overpay.” ...  One way to implement this ‘belief’ is to only reenter when prices have emphatically resumed the 'trend' .   (Fwiw, While “Winners (always*) overpay.” holds true in most endeavors (relationships, business, sports, etc...) - “Winners (always*) overpay.”  is especially true for auctions... continuous auctions included.)
    • re:  "Does it make sense to always buy the dips?  “Buy the dip.”  You hear this all the time in crypto investing trading speculation gambling. [zdo taking some liberties] It refers, of course, to buying more bitcoin (or digital assets) when they go down in price: when the price “dips.” Some people brag about “buying the dip," showing they know better than the crowd. Others “buy the dip” as an investment strategy: they’re getting a bargain. The problem is, buying the dip is a fallacy. You can’t buy the dip, because you can't see the total dip until much later. First, I’ll explain this in a way that will make it simple and obvious to you; then I’ll show you a better way of investing. You Only Know the Dip in Hindsight When people talk about “buying the dip,” what they’re really saying is, “I bought when the price was going down.” " ... example of a dip ... 
    • Date: 19th April 2024. Weekly Commodity Market Update: Oil Prices Correct and Supply Concerns Persist.   The ongoing developments in the Middle East sparked a wave of risk aversion and fueled supply concerns and investors headed for safety. Hopes for imminent rate cuts from the Federal Reserve diminish while attention is now turning towards the demand outlook. The Gold price hit a high of $2417.89 per ounce overnight. Sentiment has already calmed down again and bullion is trading at $2376.50 per ounce as haven flows ease. Oil prices initially moved higher as concern over escalating tensions with the WTI contract hit a session high of $85.508 per barrel overnight, before correcting to currently $81.45 per barrel. Oil Prices Under Pressure Amid Middle East Tensions Last week, commodity indexes showed little movement, with Oil prices undergoing a slight correction. Meanwhile, Gold reached yet another record high, mirroring the upward trend in cocoa prices. Once again today, USOil prices experienced a correction and has remained under pressure, retesting the 50-day EMA at $81.00 as we moving into the weekend. Hence, despite the Israel’s retaliatory strike on Iran, sentiments stabilized following reports suggesting a measured response aimed at avoiding further escalation. Brent crude futures witnessed a more than 4% leap, driven by concerns over potential disruptions to oil supplies in the Middle East, only to subsequently erase all gains. Similarly with USOIL, UKOIL hovers just below $87 per barrel, marginally below Thursday’s closing figures. Nevertheless, volatility is expected to continue in the market as several potential risks loom:   Disruption to the Strait of Hormuz: The possibility of Iran disrupting navigation through the vital shipping lane, is still in play. The Strait of Hormuz serves as the Persian Gulf’s primary route to international waters, with approximately 21 million barrels of oil passing through daily. Recent events, including Iran’s seizure of an Israel-linked container ship, underscore the geopolitical sensitivity of the region. Tougher Sanctions on Iran: Analysts speculate that the US may impose stricter sanctions on Iranian oil exports or intensify enforcement of existing restrictions. With global oil consumption reaching 102 million barrels per day, Iran’s production of 3.3 million barrels remains significant. Recent actions targeting Venezuelan oil highlight the potential for increased pressure on Iranian exports. OPEC Output Increases: Despite the desire for higher prices, OPEC members such as Saudi Arabia and Russia have constrained output in recent years. However, sustained crude prices above $100 per barrel could prompt concerns about demand and incentivize increased production. The OPEC may opt to boost oil output should tensions escalate further and prices surge. Ukraine Conflict: Amidst the focus on the Middle East, markets overlooking Russia’s actions in Ukraine. Potential retaliatory strikes by Kyiv on Russian oil infrastructure could impact exports, adding further complexity to global oil markets.   Technical Analysis USOIL is marking one of the steepest weekly declines witnessed this year after a brief period of consolidation. The breach below the pivotal support level of 84.00, coupled with the descent below the mid of the 4-month upchannel, signals a possible shift in market sentiment towards a bearish trend reversal. Adding to the bearish outlook are indications such as the downward slope in the RSI. However, the asset still hold above the 50-day EMA which coincides also with the mid of last year’s downleg, with key support zone at $80.00-$81.00. If it breaks this support zone, the focus may shift towards the 200-day EMA and 38.2% Fib. level at $77.60-$79.00. Conversely, a rejection of the $81 level and an upside potential could see the price returning back to $84.00. A break of the latter could trigger the attention back to the December’s resistance, situated around $86.60. A breakthrough above this level could ignite a stronger rally towards the $89.20-$90.00 zone. Always trade with strict risk management. Your capital is the single most important aspect of your trading business. Please note that times displayed based on local time zone and are from time of writing this report. Click HERE to access the full HFM Economic calendar. Want to learn to trade and analyse the markets? Join our webinars and get analysis and trading ideas combined with better understanding on how markets work. Click HERE to register for FREE! Click HERE to READ more Market news. Michalis Efthymiou Market Analyst HMarkets Disclaimer: This material is provided as a general marketing communication for information purposes only and does not constitute an independent investment research. Nothing in this communication contains, or should be considered as containing, an investment advice or an investment recommendation or a solicitation for the purpose of buying or selling of any financial instrument. All information provided is gathered from reputable sources and any information containing an indication of past perfrmance is not a guarantee or reliable indicator of future performance. Users acknowledge that any investment in FX and CFDs products is characterized by a certain degree of uncertainty and that any investment of this nature involves a high level of risk for which the users are solely responsible and liable. We assume no liability for any loss arising from any investment made based on the information provided in this communication. This communication must not be reproduced or further distributed without our prior written permission.
    • Date: 18th April 2024. Market News – Stock markets benefit from Dollar correction. Economic Indicators & Central Banks:   Technical buying, bargain hunting, and risk aversion helped Treasuries rally and unwind recent losses. Yields dropped from the recent 2024 highs. Asian stock markets strengthened, as the US Dollar corrected in the wake of comments from Japan’s currency chief Masato Kanda, who said G7 countries continue to stress that excessive swings and disorderly moves in the foreign exchange market were harmful for economies. US Stockpiles expanded to 10-month high. The data overshadowed the impact of geopolitical tensions in the Middle East as traders await Israel’s response to Iran’s unprecedented recent attack. President Joe Biden called for higher tariffs on imports of Chinese steel and aluminum.   Financial Markets Performance:   The USDIndex stumbled, falling to 105.66 at the end of the day from the intraday high of 106.48. It lost ground against most of its G10 peers. There wasn’t much on the calendar to provide new direction. USDJPY lows retesting the 154 bottom! NOT an intervention yet. BoJ/MoF USDJPY intervention happens when there is more than 100+ pip move in seconds, not 50 pips. USOIL slumped by 3% near $82, as US crude inventories rose by 2.7 million barrels last week, hitting the highest level since last June, while gauges of fuel demand declined. Gold strengthened as the dollar weakened and bullion is trading at $2378.44 per ounce. Market Trends:   Wall Street closed in the red after opening with small corrective gains. The NASDAQ underperformed, slumping -1.15%, with the S&P500 -0.58% lower, while the Dow lost -0.12. The Nikkei closed 0.2% higher, the Hang Seng gained more than 1. European and US futures are finding buyers. A gauge of global chip stocks and AI bellwether Nvidia Corp. have both fallen into a technical correction. The TMSC reported its first profit rise in a year, after strong AI demand revived growth at the world’s biggest contract chipmaker. The main chipmaker to Apple Inc. and Nvidia Corp. recorded a 9% rise in net income, beating estimates. Always trade with strict risk management. Your capital is the single most important aspect of your trading business. Please note that times displayed based on local time zone and are from time of writing this report. Click HERE to access the full HFM Economic calendar. Want to learn to trade and analyse the markets? Join our webinars and get analysis and trading ideas combined with better understanding on how markets work. Click HERE to register for FREE! Click HERE to READ more Market news. Andria Pichidi Market Analyst HFMarkets Disclaimer: This material is provided as a general marketing communication for information purposes only and does not constitute an independent investment research. Nothing in this communication contains, or should be considered as containing, an investment advice or an investment recommendation or a solicitation for the purpose of buying or selling of any financial instrument. All information provided is gathered from reputable sources and any information containing an indication of past performance is not a guarantee or reliable indicator of future performance. Users acknowledge that any investment in FX and CFDs products is characterized by a certain degree of uncertainty and that any investment of this nature involves a high level of risk for which the users are solely responsible and liable. We assume no liability for any loss arising from any investment made based on the information provided in this communication. This communication must not be reproduced or further distributed without our prior written permission.
    • Date: 17th April 2024. Market News – Appetite for risk-taking remains weak. Economic Indicators & Central Banks:   Stocks, Treasury yields and US Dollar stay firmed. Fed Chair Powell added to the recent sell off. His slightly more hawkish tone further priced out chances for any imminent action and the timing of a cut was pushed out further. He suggested if higher inflation does persist, the Fed will hold rates steady “for as long as needed.” Implied Fed Fund: There remains no real chance for a move on May 1 and at their intraday highs the June implied funds rate future showed only 5 bps, while July reflected only 10 bps. And a full 25 bps was not priced in until November, with 38 bps in cuts seen for 2024. US & EU Economies Diverging: Lagarde says ECB is moving toward rate cuts – if there are no major shocks. UK March CPI inflation falls less than expected. Output price inflation has started to nudge higher, despite another decline in input prices. Together with yesterday’s higher than expected wage numbers, the data will add to the arguments of the hawks at the BoE, which remain very reluctant to contemplate rate cuts. Canada CPI rose 0.6% in March, double the 0.3% February increase BUT core eased. The doors are still open for a possible cut at the next BoC meeting on June 5. IMF revised up its global growth forecast for 2024 with inflation easing, in its new World Economic Outlook. This is consistent with a global soft landing, according to the report. Financial Markets Performance:   USDJPY also inched up to 154.67 on expectations the BoJ will remain accommodative and as the market challenges a perceived 155 red line for MoF intervention. USOIL prices slipped -0.15% to $84.20 per barrel. Gold rose 0.24% to $2389.11 per ounce, a new record closing high as geopolitical risks overshadowed the impacts of rising rates and the stronger dollar. Market Trends:   Wall Street waffled either side of unchanged on the day amid dimming rate cut potential, rising yields, and earnings. The major indexes closed mixed with the Dow up 0.17%, while the S&P500 and NASDAQ lost -0.21% and -0.12%, respectively. Asian stock markets mostly corrected again, with Japanese bourses underperforming and the Nikkei down -1.3%. Mainland China bourses were a notable exception and the CSI 300 rallied 1.4%, but the MSCI Asia Pacific index came close to erasing the gains for this year. Always trade with strict risk management. Your capital is the single most important aspect of your trading business. Please note that times displayed based on local time zone and are from time of writing this report. Click HERE to access the full HFM Economic calendar. Want to learn to trade and analyse the markets? Join our webinars and get analysis and trading ideas combined with better understanding on how markets work. Click HERE to register for FREE! Click HERE to READ more Market news. Andria Pichidi Market Analyst HFMarkets Disclaimer: This material is provided as a general marketing communication for information purposes only and does not constitute an independent investment research. Nothing in this communication contains, or should be considered as containing, an investment advice or an investment recommendation or a solicitation for the purpose of buying or selling of any financial instrument. All information provided is gathered from reputable sources and any information containing an indication of past performance is not a guarantee or reliable indicator of future performance. Users acknowledge that any investment in FX and CFDs products is characterized by a certain degree of uncertainty and that any investment of this nature involves a high level of risk for which the users are solely responsible and liable. We assume no liability for any loss arising from any investment made based on the information provided in this communication. This communication must not be reproduced or further distributed without our prior written permission.vvvvvvv
×
×
  • Create New...

Important Information

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