Exporting MT4 account history to excel

New comment
 

Hello, 

 

I'm attempting to export mt4 account history into excel in order to calculate monthly returns. I have no problem getting all of the data into excel. But my problem is trying to perform any functions for numbers greater than 999 or less than -999. So basically any number that should have a comma in it. Has anyone ever seen this problem before? And do you know how I can quickly convert these numbers to a usable format? I really do not want to have to go through and manually key in these numbers. 

 Thanks 

 
Aj Hosenfeld: I'm attempting to export mt4 account history into excel in order to calculate monthly returns. I have no problem getting all of the data into excel. But my problem is trying to perform any functions for numbers greater than 999 or less than -999. So basically any number that should have a comma in it. Has anyone ever seen this problem before? And do you know how I can quickly convert these numbers to a usable format? I really do not want to have to go through and manually key in these numbers.

I cannot see why you would have problems with that unless you are using a different regional format for your Excel sheet. In other words, MetaTrader is producing something like "1234.456" and your Excel is expecting something like "1.234,456" (I am assuming that you are exporting data to Excel via CSV files and not via Binary data).

So, to solve the problem, either set your regional settings to be the same as used by MetaTrader, or change your code so that it outputs the numerical data in the correct format expected by Excel.

Either way, in order to help you further, you will have to show your code. We will need to see how you are outputting the data so as to offer help on how to correct it.

The following might help to serve as an example of a function to output in a different format (code is untested):

// CSV File Parameters
input string
   strCSVDelimiter   = ";", // Delimiter to be used in CSV File
   strCSVDecimal     = ","; // Decimal Character to be used in CSV File

...

// Function to output string a Value with different decimal character
string dblToStr( double dblValue, int intDigits = 8 )
{
   string strTmp = DoubleToString( dblValue, intDigits );
   StringReplace( strTmp, ".", strCSVDecimal );
   return( strTmp );
}
 

I think you are a little too far over my head here. I don't have much experience in programming. I am just downloading the html files that are pulled directly out of Metatrader so that I analyze the transaction data.

I download the report out of Metatrader as an html file, and then copy and pasted the html file into excel. This is all perfectly fine. I can format the file to where I can do whatever analysis I need too. But any number in the thousands shows up as 49 992.65 for example. So when I go to add/subtract/multiply this number to another number I get the #Value error. You would think that if I just changed the cell to a Number category it would fix the problem but it doesn't. I have tried every trick I can think of and nothing is working. 

 I appreciate your response 

 
Aj Hosenfeld: I think you are a little too far over my head here. I don't have much experience in programming. I am just downloading the html files that are pulled directly out of Metatrader so that I analyze the transaction data.

I download the report out of Metatrader as an html file, and then copy and pasted the html file into excel. This is all perfectly fine. I can format the file to where I can do whatever analysis I need too. But any number in the thousands shows up as 49 992.65 for example. So when I go to add/subtract/multiply this number to another number I get the #Value error. You would think that if I just changed the cell to a Number category it would fix the problem but it doesn't. I have tried every trick I can think of and nothing is working.

Converting HTML files into Excel sheets is not exactly straight-forward because of the different numerical format output, nor is it the usual way to output historical data for processing. The HTML files were never meant to be used in that way.

If you don't know how to code this, then I suggest one of the following:

  • Either, using a HTML or standard text editor, do some editing of the file beforehand, in order to make it more acceptable for Excel (not the best solution, but I had to mention it)
  • or, import the data columns as "text" and then create an extra column with some Excel coding that will strip the space character and convert it a proper numeric value (not straight-forward, but an acceptable solution)
  • or, select the columns in question and just do a standard "Find & Replace" so as to replace the space character with nothing (probably the easiest and quickest for you at the moment)
  • or, hire someone, to code for you a utility to export the data, properly formatted for importing into Excel (probably what you want to avoid if possible).
 
Aj Hosenfeld:

I think you are a little too far over my head here. I don't have much experience in programming. I am just downloading the html files that are pulled directly out of Metatrader so that I analyze the transaction data.

I download the report out of Metatrader as an html file, and then copy and pasted the html file into excel. This is all perfectly fine. I can format the file to where I can do whatever analysis I need too. But any number in the thousands shows up as 49 992.65 for example. So when I go to add/subtract/multiply this number to another number I get the #Value error. You would think that if I just changed the cell to a Number category it would fix the problem but it doesn't. I have tried every trick I can think of and nothing is working. 

 I appreciate your response 

What Fernando said is right.

But if you only want to copy and paste into Excel, you can go to Settings \ Advanced \ Edition Options (inside Excel) and then uncheck "Use system separators" and select the separators that you want, as I show in picture. My Excel is in Spanish, sorry.




Note:  depending on your Excel version, options can appear with other names or in other places, but anyway you would have to look for Edition Options or similar.


Regards.

 
Jose Francisco Casado Fernandez: What Fernando said is right.

But if you only want to copy and paste into Excel, you can go to Settings \ Advanced \ Edition Options (inside Excel) and then uncheck "Use system separators" and select the separators that you want, as I show in picture. My Excel is in Spanish, sorry.

Note:  depending on your Excel version, options can appear with other names or in other places, but anyway you would have to look for Edition Options or similar.

Regards.

Although that is a valid possible solution, I do not recommend it because that will change the overall functionality of Excel; impacting other sheets and could end up causing more harm than good if the OP is not careful.

Since this is not a generalised problem with Excel, but a specific requirement when importing the MetaTrader HTML data, the option of just doing a standard "Find & Replace" for the space character (in those columns), is easier and faster, and for repetitive use, can even be set up as a Macro.

 
Fernando Carreiro:

Although that is a valid possible solution, I do not recommend it because that will change the overall functionality of Excel; impacting other sheets and could end up causing more harm than good if the OP is not careful.

Since this is not a generalised problem with Excel, but a specific requirement when importing the MetaTrader HTML data, the option of just doing a standard "Find & Replace" for the space character (in those columns), is easier and faster, and for repetitive use, can even be set up as a Macro.

It's not a possible solution, but a tested solution.

I did it 6 years ago without any problems. I'm using my Excel for these 6 years always this way. Maybe a problem only if OP would want to import a csv file whose separator symbols are comma's. To avoid it, he would have only to leave blank "Thousands separator" field (or select anyother symbol).

I think it's easier and faster than "Find & Replace", because he has to do it only once in his life (and not each time he paste something, like when using "Find & Replace") and his Excel will work this way forever.

Regards.
 
Jose Francisco Casado Fernandez: It's not a possible solution, but a tested solution.

I did it 6 years ago without any problems. I'm using my Excel for these 6 years always this way. Maybe a problem only if OP would want to import a csv file whose separator symbols are comma's. To avoid it, he would have only to leave blank "Thousands separator" field (or select anyother symbol).

I think it's easier and faster than "Find & Replace", because he has to do it only once in his life (and not each time he paste something, like when using "Find & Replace") and his Excel will work this way forever.

When I stated the word "possible", I was not saying "untested". I know that solution very well and do not contest it. I said "possible" because there are several other solutions, some of which I already mentioned, but it will be up to the OP to choose the one that best fits his needs.

Only once he chooses one of the "possible" solutions does it become the "actual" solution for the OP!

 
Fernando Carreiro:

When I stated the word "possible", I was not saying "untested". I know that solution very well and do not contest it. I said "possible" because there are several other solutions, some of which I already mentioned, but it will be up to the OP to choose the one that best fits his needs.

Only once he chooses one of the "possible" solutions does it become the "actual" solution for the OP!

Ok, I misinterpreted that word, sorry. It is always better to have several possible alternatives to choose, just as you say.

Regards.
 

Awesome thanks for the help! The find and replace and the use system separators options worked for me. You guys just saved me hours of manually keying data in. 

Thanks again  

 
Comments that do not relate to this topic, have been moved to "Off Topic Posts".
New comment
Reason: