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
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 ) .
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?
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
- www.mql5.com
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.
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 |
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 4put 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.
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
- Free trading apps
- Over 8,000 signals for copying
- Economic news for exploring financial markets
You agree to website policy and terms of use
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 Fhow can i do it without change numbers to string and save matrix as string and write to excel as a string ?
thank you