how can i transfer data from row to column in filewrite command

 

hi

i try to write a mql code to write macd of symbol for three timeframes in excel with filewrite command.

but macd numbers in result excel file is only one-column and all macd data stored in rows. [ size of cells are 1000 x 1 for three time frames]

but i want to stored each time frame in a column 

for example 

H1 in column A

H4 in column C

D1 in column F


how can i do it without change numbers to string and save matrix as string and write to excel as a string ? 


thank you 

 
SEYED Karim:

hi

i try to write a mql code to write macd of symbol for three timeframes in excel with filewrite command.

but macd numbers in result excel file is only one-column and all macd data stored in rows. [ size of cells are 1000 x 1 for three time frames]

but i want to stored each time frame in a column 

for example 

H1 in column A

H4 in column C

D1 in column F


how can i do it without change numbers to string and save matrix as string and write to excel as a string ? 


thank you 

Loop through the array

Convert each value to a string with the DoubleToString() or IntegerToString() functions

Combine into a string, adding commas - put spaces in between the commas to align with the Excel columns you want (A, C, F) 

Use FileWriteString() to write a csv line to the file.

Once the entire file is written, import into Excel either as csv or as text using , as the delimiter

There are also options to write as a csv file (search the documentation for FILE_CSV) but either way you need to do some work to assemble the data as needed

 

thank you dear

Is there no way to do this without converting numbers to text?>

Does the FileWrite Command put all the numbers under each row and can't select a column?


I think that converting to text for Large data Arrays overload cpu and It takes a lot of time to store a very large text string and transfer it to Excel.

( macd text string for whole watchlist symbols make a large string  and it is possible that metatrader freeze for some seconds or minutes at running time of this code ) .




R4tna C #:

Loop through the array

Convert each value to a string with the DoubleToString() or IntegerToString() functions

Combine into a string, adding commas - put spaces in between the commas to align with the Excel columns you want (A, C, F) 

Use FileWriteString() to write a csv line to the file.

Once the entire file is written, import into Excel either as csv or as text using , as the delimiter

There are also options to write as a csv file (search the documentation for FILE_CSV) but either way you need to do some work to assemble the data as needed

 
SEYED Karim #:

thank you dear

Is there no way to do this without converting numbers to text?>

Does the FileWrite Command put all the numbers under each row and can't select a column?


You could us the StringFormat() command - you can pass numbers to that which will eventually get converted and assembled into the output string if you use that approach.

Read the documentation https://www.mql5.com/en/docs/files/filewrite

It should explain all - maybe by creating an array with empty columns to match the desired Excel format you could use the FileWrite() approach

Documentation on MQL5: File Functions / FileWrite
Documentation on MQL5: File Functions / FileWrite
  • www.mql5.com
FileWrite - File Functions - MQL5 Reference - Reference on algorithmic/automated trading language for MetaTrader 5
 
R4tna C #:

You could us the StringFormat() command - you can pass numbers to that which will eventually get converted and assembled into the output string if you use that approach.

Read the documentation https://www.mql5.com/en/docs/files/filewrite

It should explain all - maybe by creating an array with empty columns to match the desired Excel format you could use the FileWrite() approach

after running this code save data to an excel file .
macd for H1 timeframe in first rows
then
macd for H4 in second rows
.
.

I want to filewrite transfer each timeframe to a column .


//+------------------------------------------------------------------+
//|                                               Demo_FileWrite.mq5 |
//|                        Copyright 2013, MetaQuotes Software Corp. |
//|                                              https://www.mql5.com |
//+------------------------------------------------------------------+
#property copyright "Copyright 2013, MetaQuotes Software Corp."
#property link      "https://www.mql5.com"
#property version   "1.00"
//--- show the window of input parameters when launching the script
#property script_show_inputs
//--- parameters for receiving data from the terminal
input int                InpFastEMAPeriod = 12;            // fast EMA period
input int                InpSlowEMAPeriod = 26;            // slow EMA period
input int                InpSignalPeriod = 9;              // difference averaging period
input ENUM_APPLIED_PRICE InpAppliedPrice = PRICE_CLOSE;    // price type
input datetime           InpDateStart = D'2001.01.01 00:00'; // data copying start date
//--- parameters for writing data to file
input string             InpFileName = "MACD01.csv"; // file name
input string             InpDirectoryName = "Data"; // directory name
//+------------------------------------------------------------------+
//| Script program start function                                    |
//+------------------------------------------------------------------+
void OnStart()
  {
//--- end time is the current time
   MqlDateTime tm;
   TimeToStruct(TimeCurrent(), tm);
   string nam = _Symbol + "M" + string(tm.mon) + " D" + string(tm.day) + " H" + string(tm.hour) + " M" + string(tm.min)+ " S" + string(tm.sec) + ".csv";
   int file_handle = FileOpen(InpDirectoryName + "//" + nam, FILE_READ | FILE_WRITE | FILE_CSV|FILE_ANSI,',');
//--- receive MACD indicator handle
   ResetLastError();
   ENUM_TIMEFRAMES vect[] = {PERIOD_H1, PERIOD_H4, PERIOD_D1, PERIOD_W1};
   for(int ivect = 0; ivect < ArraySize(vect); ivect++)
     {
      datetime date_finish; // data copying end date
      bool     sign_buff[]; // signal array (true - buy, false - sell)
      datetime time_buff[]; // array of signals' arrival time
      int      sign_size = 0; // signal array size
      double    asksym[], macdval[];
      double   macd_buff[]; // array of indicator values
      double   close_buff[]; // array of indicator values
      datetime date_buff[]; // array of indicator dates
      int      macd_size = 0; // size of indicator arraysint
      int macd_handle = iMACD(_Symbol, vect[ivect], InpFastEMAPeriod, InpSlowEMAPeriod, InpSignalPeriod, InpAppliedPrice);
      date_finish = TimeCurrent();
      if(macd_handle == INVALID_HANDLE)
        {
         //--- failed to receive indicator handle
         PrintFormat("Error when receiving indicator handle. Error code = %d", GetLastError());
         return;
        }
      //--- being in the loop until the indicator calculates all its values
      while(BarsCalculated(macd_handle) == -1)
         Sleep(10); // pause to allow the indicator to calculate all its values
      //--- copy the indicator values for a certain period of time
      ResetLastError();
      if(CopyBuffer(macd_handle, 0, InpDateStart, date_finish, macd_buff) == -1)
        {
         PrintFormat("Failed to copy indicator values. Error code = %d", GetLastError());
         return;
        }
      //--- copy the appropriate time for the indicator values
      if(CopyTime(_Symbol, vect[ivect], InpDateStart, date_finish, date_buff) == -1)
        {
         PrintFormat("Failed to copy time values. Error code = %d", GetLastError());
         return;
        }
      if(CopyClose(_Symbol, vect[ivect], InpDateStart, date_finish, close_buff) == -1)
        {
         PrintFormat("Failed to copy time values. Error code = %d", GetLastError());
         return;
        }
      //--- free the memory occupied by the indicator
      IndicatorRelease(macd_handle);
      //--- receive the buffer size
      macd_size = ArraySize(macd_buff);
      //--- analyze the data and save the indicator signals to the arrays
      ArrayResize(sign_buff, macd_size - 1);
      ArrayResize(time_buff, macd_size - 1);
      ArrayResize(asksym, macd_size - 1);
      ArrayResize(macdval, macd_size - 1);
      for(int i = 1; i < macd_size; i++)
        {
         //--- buy signal
         if(macd_buff[i - 1] < 0 && macd_buff[i] >= 0)
           {
            sign_buff[sign_size] = true;
            time_buff[sign_size] = date_buff[i];
            asksym[sign_size] = close_buff[i];
            macdval[sign_size] = macd_buff[i];
            sign_size++;
           }
         //--- sell signal
         if(macd_buff[i - 1] > 0 && macd_buff[i] <= 0)
           {
            sign_buff[sign_size] = false;
            time_buff[sign_size] = date_buff[i];
            asksym[sign_size] = close_buff[i];
            macdval[sign_size] = macd_buff[i];
            sign_size++;
           }
        }
      //--- open the file for writing the indicator values (if the file is absent, it will be created automatically)
      ResetLastError();
      if(file_handle != INVALID_HANDLE)
        {
         PrintFormat("%s file is available for writing %s", InpFileName,(vect[ivect]));
         PrintFormat("File path: %s\\Files\\", TerminalInfoString(TERMINAL_DATA_PATH));
         //--- first, write the number of signals
         FileWrite(file_handle, sign_size, _Symbol, (vect[ivect]));
         //--- write the time and values of signals to the file
         for(int i = 0; i < sign_size; i++)
            FileWrite(file_handle, time_buff[i], sign_buff[i], asksym[i], macdval[i]);
         //--- close the file
         PrintFormat("Data is written, %s file is closed", InpFileName);
        }
      else
         PrintFormat("Failed to open %s file, Error code = %d", InpFileName, GetLastError());
     }
   FileClose(file_handle);
  }
//+------------------------------------------------------------------+


 

The easiest way is for me to write a VBA code in Excel to move some rows to the next column.

 
SEYED Karim #:

I ran it - first of all I recommend changing the start date to something more recent - I used

input datetime           InpDateStart = D'2023.11.01 00:00'; // data copying start date

After that I got a csv file which can be read in Excel - 


14 GBPUSD 16385
2023.11.02 01:00:00 TRUE 1.2166 0.000117486
2023.11.07 03:00:00 FALSE 1.23382 -9.35994E-05
2023.11.09 11:00:00 TRUE 1.23005 0.000122766
2023.11.09 13:00:00 FALSE 1.22615 -0.000181613
2023.11.13 08:00:00 TRUE 1.22383 1.39178E-05
2023.11.15 22:00:00 FALSE 1.24096 -6.1457E-05
2023.11.16 18:00:00 TRUE 1.24205 3.6626E-05
2023.11.17 01:00:00 FALSE 1.24139 -1.30852E-05
2023.11.17 02:00:00 TRUE 1.24179 7.27837E-06
2023.11.17 03:00:00 FALSE 1.24129 -1.67362E-05
2023.11.17 14:00:00 TRUE 1.24283 0.000100669
2023.11.22 16:00:00 FALSE 1.24759 -0.00040652
2023.11.23 11:00:00 TRUE 1.25584 0.000396666
2023.11.30 11:00:00 FALSE 1.26489 -0.000338913
4 GBPUSD 16388
2023.11.01 12:00:00 FALSE 1.21219 -7.66163E-05
2023.11.02 00:00:00 TRUE 1.21935 0.000350649
2023.11.09 20:00:00 FALSE 1.22222 -0.00011873
2023.11.14 08:00:00 TRUE 1.23019 0.000277599
1 GBPUSD 16408
2023.11.07 00:00:00 TRUE 1.22992 7.96685E-05
0 GBPUSD 32769


If you insert spaces, you will find that you can get empty columns in-between

         FileWrite(file_handle, " ", sign_size, " ", _Symbol, " ", (vect[ivect]));
         //--- write the time and values of signals to the file
         for(int i = 0; i < sign_size; i++)
            FileWrite(file_handle, " ", time_buff[i], " ", sign_buff[i], " ", asksym[i], " ", macdval[i]);


  14   GBPUSD   16385
  2023.11.02 01:00:00   TRUE   1.2166   0.000117
  2023.11.07 03:00:00   FALSE   1.23382   -9.4E-05
  2023.11.09 11:00:00   TRUE   1.23005   0.000123
  2023.11.09 13:00:00   FALSE   1.22615   -0.00018
  2023.11.13 08:00:00   TRUE   1.22383   1.39E-05
  2023.11.15 22:00:00   FALSE   1.24096   -6.1E-05
  2023.11.16 18:00:00   TRUE   1.24205   3.66E-05
  2023.11.17 01:00:00   FALSE   1.24139   -1.3E-05
  2023.11.17 02:00:00   TRUE   1.24179   7.28E-06
  2023.11.17 03:00:00   FALSE   1.24129   -1.7E-05
  2023.11.17 14:00:00   TRUE   1.24283   0.000101
  2023.11.22 16:00:00   FALSE   1.24759   -0.00041
  2023.11.23 11:00:00   TRUE   1.25584   0.000397
  2023.11.30 11:00:00   FALSE   1.26489   -0.00034
  4   GBPUSD   16388
  2023.11.01 12:00:00   FALSE   1.21219   -7.7E-05
  2023.11.02 00:00:00   TRUE   1.21935   0.000351
  2023.11.09 20:00:00   FALSE   1.22222   -0.00012
  2023.11.14 08:00:00   TRUE   1.23019   0.000278
  1   GBPUSD   16408
  2023.11.07 00:00:00   TRUE   1.22992   7.97E-05
  0   GBPUSD   32769
 
SEYED Karim #:

The easiest way is for me to write a VBA code in Excel to move some rows to the next column.

That might be better - you can control what happens in Excel

 
R4tna C #:

That might be better - you can control what happens in Excel

R4tna C #:

I ran it - first of all I recommend changing the start date to something more recent - I used

After that I got a csv file which can be read in Excel - 


14 GBPUSD 16385
2023.11.02 01:00:00 TRUE 1.2166 0.000117486
2023.11.07 03:00:00 FALSE 1.23382 -9.35994E-05
2023.11.09 11:00:00 TRUE 1.23005 0.000122766
2023.11.09 13:00:00 FALSE 1.22615 -0.000181613
2023.11.13 08:00:00 TRUE 1.22383 1.39178E-05
2023.11.15 22:00:00 FALSE 1.24096 -6.1457E-05
2023.11.16 18:00:00 TRUE 1.24205 3.6626E-05
2023.11.17 01:00:00 FALSE 1.24139 -1.30852E-05
2023.11.17 02:00:00 TRUE 1.24179 7.27837E-06
2023.11.17 03:00:00 FALSE 1.24129 -1.67362E-05
2023.11.17 14:00:00 TRUE 1.24283 0.000100669
2023.11.22 16:00:00 FALSE 1.24759 -0.00040652
2023.11.23 11:00:00 TRUE 1.25584 0.000396666
2023.11.30 11:00:00 FALSE 1.26489 -0.000338913
4 GBPUSD 16388
2023.11.01 12:00:00 FALSE 1.21219 -7.66163E-05
2023.11.02 00:00:00 TRUE 1.21935 0.000350649
2023.11.09 20:00:00 FALSE 1.22222 -0.00011873
2023.11.14 08:00:00 TRUE 1.23019 0.000277599
1 GBPUSD 16408
2023.11.07 00:00:00 TRUE 1.22992 7.96685E-05
0 GBPUSD 32769


If you insert spaces, you will find that you can get empty columns in-between


  14   GBPUSD   16385
  2023.11.02 01:00:00   TRUE   1.2166   0.000117
  2023.11.07 03:00:00   FALSE   1.23382   -9.4E-05
  2023.11.09 11:00:00   TRUE   1.23005   0.000123
  2023.11.09 13:00:00   FALSE   1.22615   -0.00018
  2023.11.13 08:00:00   TRUE   1.22383   1.39E-05
  2023.11.15 22:00:00   FALSE   1.24096   -6.1E-05
  2023.11.16 18:00:00   TRUE   1.24205   3.66E-05
  2023.11.17 01:00:00   FALSE   1.24139   -1.3E-05
  2023.11.17 02:00:00   TRUE   1.24179   7.28E-06
  2023.11.17 03:00:00   FALSE   1.24129   -1.7E-05
  2023.11.17 14:00:00   TRUE   1.24283   0.000101
  2023.11.22 16:00:00   FALSE   1.24759   -0.00041
  2023.11.23 11:00:00   TRUE   1.25584   0.000397
  2023.11.30 11:00:00   FALSE   1.26489   -0.00034
  4   GBPUSD   16388
  2023.11.01 12:00:00   FALSE   1.21219   -7.7E-05
  2023.11.02 00:00:00   TRUE   1.21935   0.000351
  2023.11.09 20:00:00   FALSE   1.22222   -0.00012
  2023.11.14 08:00:00   TRUE   1.23019   0.000278
  1   GBPUSD   16408
  2023.11.07 00:00:00   TRUE   1.22992   7.97E-05
  0   GBPUSD   32769




this code has 4 column and 

H4 timeframe data put in rows under H1 data set.

i want to filewrite command

put H1 data to columns 1 to 4 

put H4 data to columns 5 to 8 

put D1 data to columns 9 to 12

WITOUT converting numbers to text.

Is It possible ?

 

WITOUT converting numbers to text.

and 

WITOUT Changing structure of this code .

Calling multidimensional arrays in the filewrite command is very difficult and challenging with considering For Loop in this Code.

 
SEYED Karim #:

WITOUT converting numbers to text.

and 

WITOUT Changing structure of this code .

Calling multidimensional arrays in the filewrite command is very difficult and challenging with considering For Loop in this Code.

And this is why I prefer doing it with strings, like I suggested in the beginning. But with some effort I am sure you can do it with FileWrite() too