Pathetically Begging For Help With An Excel Spreadsheet!

Hi Guys,

A long time ago (in a galaxy far, far away) I acquired a free spreadsheet from a UK Forex broker which no longer seems to exist. The company's name was FXdialogue and while it still shows up on Google, clicking on any of the links will take you nowhere fast. None of this back story is necessary as my problem concerns the spreadsheet itself. Its called TickColectorFinal (sic) and I've attached it (hopefully!) along with this post.

What it basically does is ... umm ... well, it collects ticks! Not the kind your dog collects, but price ticks via the DDE feed of Metatrader. It does its job fairly well and much like immense parts of my anatomy, I make use of it a lot.

"So what's the problem?", I hear nobody cry. The problem is that while it has quite a lot of currency pairs listed, I would like to be able to add more and duplicate its functionality. I am hindered by the disturbing fact that I am a complete imbecile and do not have the foggiest idea how to go about such a thing. Whoever it was who created it has hidden the formulae and I cannot duplicate it. Seeing as Forex Factory is the number 1 place where all the really smart and cool kids hang out, I'm hoping one of the aforesaid geniuses can take a look at it (the spreadsheet, not the part of my anatomy that I mentioned earlier), do some Einsteinian wizardry upon it and then reupload it for my benefit, theeeen perhaps they could go wash my car. Oh, alright then, just fixing the spreadsheet should be sufficient.

Thanks in advance!

Regards,
Ian

You will need to enable DDE in Terminal settings, MT4 will need to be running and ticks coming in.

Got this from the 'Help' in MT4

Export of Quotes

Source data serving as a basis for the entire analytical work of the terminal user are those about security price changes. This information is provided by the brokerage company. Price data allow to draw symbol charts, research in financial markets, use various trading tactics, and make trade decisions. Quotes represent files with records in format of "SYMBOL, BID, ASK, DATE" (security symbol, bid price, ask price, date and time) and income in the terminal automatically as soon as connection to the server has been established.

The terminal allows to export the current quotes to other programs in the real-time mode through "DDE" (Dynamic Data Exchange) protocol. This is a protocol of operational systems of MS Windows used for dynamic data exchange among various applications. Quotes are given through DDE only at incoming of new ticks (ADVISE mode), but not immediately on request (REQUEST mode) where the latest price is shown. N/A is shown on the first REQUEST, and after the new price has been income, quotes will appear.

To activate the export of quotes from the terminal through DDE, one has to enable the "Enable DDE server" option in the terminal settings.

Attention: History Data cannot be exported through DDE protocol. The current quotes are exported only when the client terminal is online.


DDE request formats and their possible results by the example of "DDE-sample.xls" file:

BID request: = MT4|BID!USDCHF result: 1.5773
ASK request: = MT4|ASK!USDCHF result: 1.5778
HIGH request: = MT4|HIGH!USDCHF result: 1.5801
LOW request: = MT4|LOW!USDCHF result: 1.5741
TIME request: = MT4|TIME!USDCHF result: 21.05.02 9:52
QUOTE request: = MT4|QUOTE!USDCHF result: 21.05.02 9:52 1.5773 1.5778 1.5776

Attention: For data to be shown properly in MS Excel, one has to enable "Tools — Options... — Translation — Translation formula entry" menu option of MS Excel.



with the above wisdom, you should be able to push DDE to the max

I do NOT see that the formulas are hidden?

Do not know if it works but you appear to think that it does... that is in between the times you spend looking for fleas on your dog and other things on your anatomy!

To duplicate with new pairs, assuming that you want additional ones, you will have to play with XL basic if that is what floats your boat.

Enable macros
Press Alt + F11
Double click on 'this workbook'
and then good luck :-)

M.

i'm also kkeping record of ticks coming from metatrader , on your excel spreadsheet, if you check the Cell A4 you should read :=MT4|BID!EURUSD
this means on the cell A4 you are looking at the bid EURUSD , so if you want to add others currency go on a cell and type : =MT4|BID!XXXX (XXXX being the currency you want to get )
hope it helps ....

cheers

landsat

Thanks to all of you for responding and please do not think I am being impertinent when I say that nothing anyone has said has helped.

Sebastiaan76: I am able to USE the spreadsheet and can get the tick data coming in via the DDE server. I'm sorry you went to such trouble writing so much, but I did say "it collects ticks! ......... price ticks via the DDE feed of Metatrader. It does its job fairly well", indicating I am able to use its current functionality.

landsat: Thank you, but the mistake was mine for not expressing myself well enough. I am able to get dynamic bid and ask data for any available pairs in much the same way that it appears inside any trading platform and I did know the formula that was required to obtain it. What I am UNABLE to do is to capture and retain it tick by tick in the spreadsheet. That is what it does now. If you open it up and look at cells C4 & D4 for instance, during market hours as soon as the DDE server is enabled the first price tick data for EUR/USD will appear in these two aforementioned cells. The next ticks will appear in C5 & D5, the next in C6 & D6, ad infinitum, until I disable the feed. These ticks will appear in the appropriate cells AUTOMATICALLY on enabling the DDE server and it is this that I am wanting to emulate with pairs which are not listed. It is also this that I meant when I said the formulae is hidden, i.e. I do not know how these cells get populated with data.

After typing the last paragraph I decided to attach a 'populated' version of the spreadsheet to show you what it does and for you to see what I mean more clearly. Please download and check it out.

futurespec: You said "Do not know if it works but you appear to think that it does" - It works during the week when the market is open, but obviously not at the weekend! :-) Apart from this, I think you have given the most helpful advice, as it probably does have something to do with enabling macros and so forth. I followed your instructions until the "and then good luck" part of your post. Unfortunately it is precisely THIS part that I am needing help with! lol

Anyway guys, I appreciate your attempts to help me and can only hope there's someone else out there with the knowledge I require.

Take care,
Ian

ahhhh, I get what you are trying to do now.

But, I think you would need to find if there is a macro/script running within that spreadsheet that is doing what you need.

Unfortunately I have no idea how to find/edit such a script - so maybe someone with a bit more excel / programming experience could chime in

Ian,

Without teaching you Visual Basic there is no easy answer!

If you followed my original instructions then the answer is there.... you would have arrived at the code you need to change/add to.

In my opinion that code could be a lot tidier but it has been quite some time since I tackled VB and have forgotten the little that I learned. What you need is not difficult, just time consuming.

There is not a 1 line answer. sorry.

First you need to ask yourself what you need you see... do you really want bid & ask and time?? What are you trying to achieve?

Desperately trying to achieve the status of "aforesaid geniuses" :-)

I could do it but would take time and patience ... and I have little of both!
And my car needs washing... if you do that I will do your spreadsheet.

M.

Hi again guys,

Thanks once more for taking moments out of your precious time to reply to me, I really appreciate it.

Futurespec, yes, I really do need the bid, ask and time - it is essential to what I'm wanting to achieve. What I'm wanting to achieve is something I'm not willing to divulge unfortunately, but believe me when I say that although I have presented myself in a light hearted manner here on Forex Factory, I am a full time trader and take it all very seriously.

Thank you for your input and for steering me in the right direction. I think I'm going to employ someone to do what I want. If you lived nearby then I'd gladly wash your car for you if you did the work for me (), but unless you're up in Scotland then forget it!

Best wishes to you all at any rate.

Cheers!

I am going to move to Scotland just to get my car washed :-)
Will look at it another time when market is open.

What is max number of pairs that you want?

It is only a 10 min job for someone (not me) good with VB. I can do it but will take me longer 'cos when I get an error I have trouble trying to resolve it.

M.

There is second worksheet in your file. It holds a single cell, making an "MT4!" call.

Is it possible this cell steals a data point from the stream, or fouls up the flow layout?

This might then throw all following references for a loop, as seems to be the case.

That is; One full line runs normally, then the single cell makes an undue call, and the proverbial stuff hits the fan.

Apipshort, The single cell on sheet 2 is probably a leftover attempt by yours truly trying to fiddle about and recreate the logic in the first sheet. You may be right in saying it can skew the data, but I haven't seen anything to support the theory. Thanks for pointing it out though. I've now deleted it.

Futurespec, Thanks again for continuing to assist me on this.

In addition to the pairs that sheet already contains, I'd like a whole lot more: EURNZD, NZDJPY, GBPNZD, CADJPY, GBPAUD, GBPCAD, NZDCAD, AUDCHF, NZDCHF, USDNOK, EURNOK, GBPNOK, CADNOK, AUDNOK, NZDNOK, NOKJPY, NOKCHF, USDSEK, EURSEK, GBPSEK, CADSEK, AUDSEK , NZDSEK, SEKJPY, SEKCHF

Yes, I realise this a lot of data, but I'd only be using the spreadsheet for very small periods of time and I have the PC power in order to handle it.

I think I'll enquire on one of those freelance programmer sites to see how much they'd charge for the work.

Best wishes to all.

Ian

yes your spreadsheet contain 2 Visual Basic forms , in order to modify your file , you will need the help of VBA expert ,its not complicated but it's complex.

Cheers

Landsat

ps: i don't think its going to be expensive , under 100 pounds..... no more than 2 hours max to make the changes...

Ian,
Sorry it looks as though my car is not going to get washed as I failed miserably. Well just a little bit.

I have done what I think should work but am getting a "type mismatch" error which for the life of me I cannot work out why!

Perhaps some kind sole could shed some light on that ???

It is set up for up to 50 pairs but you will need to put the pairs and the links in along rows 3 and 4 every 3rd column for the new ones.(if that makes sense)

M.

Futurespec.... 'M'... or Mr Cat-With-A-Watermelon-Helmet,

Thank you so much for this! I am going to send you a private message now if you don't mind...

Best regards!

P.S.
Thanks to you too Landsat!

Ian,
Sorted it for you.
Sent you a PM.

M.

I have download an excel dashboard from website. But my excel is not responding while refresh the data. Please help. Anywhere my mt4 terminal has been enable DDE server. I have attached the excel here.

Another way i did was i opened new excel, type =MT4|BID!EURUSD into any blank cell, it show formula error.