Excel calculator formatting problem

Hello. I've been trying to make an excel calculator mainly for margin calculation purposes. The problem is that I have the quotes from Metatrader in the format X.XXXX, and I cannot convert all of them in the form X,XXXX. I have tried Custom Formatting without success. Also I'm interested if the data for a pair can dynamically change in the tables after I choose another pair. If anyone would know how to solve this problems I would be grateful. Thank you!

You need a lookup table. I'm not sure why you need the decimal as a comma.

I've listed traded symbols as green. The yellow cells are calculated and used in exposure. There's conditional formatting on bid/ask column to show the correct number of digits.

I've included the formulas you need if you want to use DDE so that the bid/ask will automatically change with MT4.

There's a sample cell where you can change the symbol and margin will also change.

enjoy!

Thanks, the VLOOKUP function is the answer. But I still can't figure out how to change the dot into a comma, the value must remain a number.

File, Options, Advance
Uncheck Use system separators, then change the Decimal separator to a comma. You have to change the Thousands too so they are not ambiguous.

Still does not work. The data is imported with the dot format, and can't make any calculation with it as Excel gives #VALUE error...

I doubt it is about the dot, probably more the fact that it is no a number and is being treated as text ?

why do you not just create a panel and run it on a chart in MT rather than go through all the issues of data exchange.

there are a number of excel data exchange bits of code kicking around do a search and some of it may help you

Simply change the format to English (United States) in the Region and Language of the Control Panel.
This will resolve your problem.

Panurgo

I think you are right, Excel have issues with DDE. I'll try to do something like this in metatrader. Anyway thank you all for your help.

Hi bogdan, i think i have the solution to your problem its very simple and yet very useful for excel users to have.
First off all go to your dde excel document, type in pair with dot values, for example (=MT4|ASK!USDCHF) in my case it gives 0.93974
Your next step is to insert simple function into this cell that you just wrote: =REPLACE(MT4|ASK!USDCHF;2;1;",")
And now you have 0,93974 do the same steps for the rest of your values BID HIGH LOW ect.

This formula replaces character "." with "," in position two ("2" in formula), if you have something like 1432.10 you need to type "5" instead of "2".
Number "1" in formula refers to how many characters you want to replace and the last one "," is character, its what you want be in place.

I'm not a guru on excel, spent quite a lot of time solving this issue, so hope i will save time for someone else. Cheers