This script is designed to automatically pull data from the MetaTrader 4 (MT4) trading platform and write it into an Excel spreadsheet.
Here's a breakdown of what it does, step by step:
-
Connect to Excel: The script starts by opening an Excel application. If it can't, it will display an error message. This connection is made possible by an external library (mt4excel.dll) that enables communication between MT4 and Excel.
-
Write Initial Data: Once connected to Excel, the script writes some initial information into specific cells:
- In the first row, it labels the columns as "Instrument," "EURUSD," "GBPUSD," and "USDCHF." (These are currency pairs)
- In the second row, it writes the opening price of each currency pair for the current day (at 0:00 GMT).
- In the third row, it writes the current "bid" price (the price at which you can sell) for each currency pair.
-
Calculate and display the daily change: In the fourth row, it calculates the difference between the opening price and the latest bid price for each currency pair, showing the price change during the current day.
-
Format the spreadsheet: The script adjusts the column widths for better readability, sets the background color of the header row (the one with "Instrument," etc.) to black, and the font color to white to make it stand out.
-
Create a Chart: It then creates a chart showing the daily price change for each instrument. The chart takes the labels for the currencies from column A and the change for the currencies from column D. It puts the chart in a specific location in the spreadsheet.
-
Real-time update: The script then enters a loop that continuously updates the current "bid" prices in the Excel sheet. It monitors price changes every second. If the "bid" price of a currency pair changes, it updates the corresponding cell in the third row.
-
Save and Close Excel: When the script stops running (deinitialization), it saves the Excel file as "C:\proba.xls" and closes the Excel application.
//+------------------------------------------------------------------+
//| shablon.mq4 |
//| Viatcheslav Suvorov |
//+------------------------------------------------------------------+
#property copyright "Viatcheslav Suvorov"
#property show_inputs
#include <WinUser32.mqh>
#import "mt4excel.dll"
bool ExcelOpen();//Îòêðûâàåò Excel è ñîçäàåò ÷èñòóþ ñòðàíèöó
bool ExcelClose();//Çàêðûâàåò Excel
bool ExcelOpenPattern(int NumPatt);//Îòêðûâàåò Excel ïî øàáëîíó
bool ExcelOpenFile(string FileName);//Îòêðûâàåò ôàéë Excel
bool ExcelSave();//Ñîõðàíÿåì ôàéë
bool ExcelSaveAs(string FileName);//Ñîõðàíÿåì ôàéë â FileName
bool ExcelAddSheet(string Name);//Äîáàèòü ëèñò è ñäåëàòü åãî àêòèâíûì
bool ExcelSetFormulaCell(int X,int Y,string Value);//Çàïèñàòü ôîðìóëó â ÿ÷åéêó
double ExcelGetValueCell(int X,int Y);//Ñ÷èòàòü ÷èñëî èç ÿ÷åéêè
string ExcelGetTextCell(int X,int Y);//Ñ÷èòàòü òåêñò èç ÿ÷åéêè
string ExcelGetFormulaCell(int X,int Y);//Ñ÷èòàòü ôîðìóëó èç ÿ÷åéêè
/*TypeD - òèï äèàãðàììû:
xl3DArea -4098 xl3DAreaStacked 78 xl3DAreaStacked100 79 xl3DBarClustered 60 xl3DBarStacked 61 xl3DBarStacked100 62
xl3DColumn -4100 xl3DColumnClustered 54 xl3DColumnStacked 55 xl3DColumnStacked100 56 xl3DLine -4101 xl3DPie -4102
xl3DPieExploded 70 xlArea 1 xlAreaStacked 76 xlAreaStacked100 77 xlBarClustered 57 xlBarOfPie 71
xlBarStacked 58 xlBarStacked100 59 xlBubble 15 xlBubble3DEffect 87 xlColumnClustered 51 xlColumnStacked 52
xlColumnStacked100 53 xlConeBarClustered 102 xlConeBarStacked 103 xlConeBarStacked100 104 xlConeCol 105 xlConeColClustered 99
xlConeColStacked 100 xlConeColStacked100 101 xlCylinderBarClustered 95 xlCylinderBarStacked 96 xlCylinderBarStacked100 97 xlCylinderCol 98
xlCylinderColClustered 92 xlCylinderColStacked 93 xlCylinderColStacked100 94 xlDoughnut -4120 xlDoughnutExploded 80 xlLine 4 xlLineMarkers 65
xlLineMarkersStacked 66 xlLineMarkersStacked100 67 xlLineStacked 63 xlLineStacked100 64 xlPie 5 xlPieExploded 69 xlPieOfPie 68 xlPyramidBarClustered 109
xlPyramidBarStacked 110 xlPyramidBarStacked100 111 xlPyramidCol 112 xlPyramidColClustered 106 xlPyramidColStacked 107 xlPyramidColStacked100 108
xlRadar -4151 xlRadarFilled 82 xlRadarMarkers 81 xlStockHLC 88 xlStockOHLC 89 xlStockVHLC 90 xlStockVOHLC 91 xlSurface 83
xlSurfaceTopView 85 xlSurfaceTopViewWireframe 86 xlSurfaceWireframe 84 xlXYScatter -4169 xlXYScatterLines 74 xlXYScatterLinesNoMarkers 75
xlXYScatterSmooth 72 xlXYScatterSmoothNoMarkers 73 */
bool ExcelSetDiagramma(int TypeD,string Title,string XRange,string YRange,int Left,int Top,int Riht,int Bottom);//Äîáàâëÿåò äèàãðàìó
bool ExcelDiagrammaAddRange();//Äîáàâèòü äàííûõ â äèàãðàììó. Ïåðâàÿ ñòðîêà - çàãîëîâîê
bool ExcelSetValueCell(int X,int Y,double Value);//Çàïèñàòü ÷èñëî â ÿ÷åéêó
bool ExcelSetTextCell(int X,int Y,string Value);//Çàïèñàòü òåêñò â ÿ÷åéêó
bool ExcelSetRange(string Range);//Çàïîìíèòü äèàïàçîí
string ExcelGetFormat();//Âîçâðàùàåò ôîðìàò äèàïàçîíà
bool ExcelSetFormat(string Format);//Çàäàòü ôîðìàò äëÿ äèàïîçàíà
bool ExcelSetFormula(string Formula);//Çàïèñàòü ôîðìóëó â äèàïàçîí
bool ExcelIsFormula();//true åñëè â äèàïàçîíå ôîðìóëà
int ExcelRangeCount();//Êîë-âî ÿ÷ååê â äèàïàçîíå
string ExcelRangeAdress();//Àäðåññ äèàïàçîíà
bool ExcelRangeColumnWidth(int Width);//Øèðèíà äèàïàçîíà
bool ExcelRangeRowHeight(int Height);//Âûñîòà äèàïàçîíà
bool ExcelRangeInteriorColor(int Color);//Öâåò ôîíà äèàïàçîíà
bool ExcelRangeFontColor(int Color);//Öâåò øðèôòà äèàïàçîíà
int ExcelGetLastErrorCode();//Êîä ïîñëåäíåé îøèáêè Excel
string ExcelGetLastErrorText();//Òåêñò ïîñëåäíåé îøèáêè Excel
#import
int start()
{
if (ExcelOpen()) Print("Ócïåøíî îòêðûëè Excel"); else Print("Íå îòêðûâàåòñÿ Excel:",ExcelGetLastErrorText());//Îòêðûâàåì Excel
ExcelSetTextCell(1,1,"Èíñòðóìåíò");
ExcelSetTextCell(2,1,"EURUSD");
ExcelSetTextCell(3,1,"GBPUSD");
ExcelSetTextCell(4,1,"USDCHF");
ExcelSetTextCell(1,2,"Îòêðûòèå(0:0 GMT)");
ExcelSetValueCell(2,2,iOpen("EURUSD",PERIOD_D1,0));
ExcelSetValueCell(3,2,iOpen("GBPUSD",PERIOD_D1,0));
ExcelSetValueCell(4,2,iOpen("USDCHF",PERIOD_D1,0));
ExcelSetTextCell(1,3,"Ïîñëåäíèé Bid");
ExcelSetValueCell(2,3,MarketInfo("EURUSD",MODE_BID));
ExcelSetValueCell(3,3,MarketInfo("GBPUSD",MODE_BID));
ExcelSetValueCell(4,3,MarketInfo("USDCHF",MODE_BID));
ExcelSetTextCell(1,4,"Èçìåíåíèå çà äåíü");
ExcelSetRange("D2:D4");
ExcelSetFormula("=C2-B2");
ExcelSetRange("A1");
ExcelRangeColumnWidth(15);
ExcelSetRange("B1");
ExcelRangeColumnWidth(20);
ExcelSetRange("C1");
ExcelRangeColumnWidth(20);
ExcelSetRange("D1");
ExcelRangeColumnWidth(20);
ExcelSetRange("A1:D1");
ExcelRangeInteriorColor(0x000000);
ExcelRangeFontColor(0xFFFFFF);
ExcelSetDiagramma(103,"Èçìåíåíèå çà äåíü","A2:A4","D1:D4",1,50,400,200);
bool NeedLoop=true;
double lastEURUSD,lastGBPUSD,lastUSDCHF;
double curEURUSD,curGBPUSD,curUSDCHF;
while(NeedLoop){
curEURUSD=MarketInfo("EURUSD",MODE_BID);
curGBPUSD=MarketInfo("GBPUSD",MODE_BID);
curUSDCHF=MarketInfo("USDCHF",MODE_BID);
if (lastEURUSD!=curEURUSD){
lastEURUSD=curEURUSD;
ExcelSetValueCell(2,3,MarketInfo("EURUSD",MODE_BID));
}
if (lastGBPUSD!=curGBPUSD){
lastGBPUSD=curGBPUSD;
ExcelSetValueCell(3,3,MarketInfo("GBPUSD",MODE_BID));
}
if (lastUSDCHF!=curUSDCHF){
lastUSDCHF=curUSDCHF;
ExcelSetValueCell(4,3,MarketInfo("USDCHF",MODE_BID));
}
Sleep(1000);
}//while
return(0);
}
void deinit()
{
ExcelSaveAs("C:\proba.xls");
ExcelClose();
}
Comments