Excel and VBA analysis | Page 2 | Forex Factory

Options

Go to
Permalinks
Print Thread

Similar Threads

MT4 to Excel through Excel RTD (Finally!) 612 replies

Excel analysis as a back testing tool - need help 5 replies

Performance History Analysis Excel Spreadsheet 15 replies

fun with an excel/vba price sim 1 reply

Price Distribution Analysis in Excel 26 replies

Attachments: Excel and VBA analysis
Exit Attachments

Excel and VBA analysis

  • #21
  • Mar 1, 2015 5:58pm Mar 1, 2015 5:58pm
Quoting Delta1One
{quote} Hey Tom, I have a solution for this. What I do is activate DDE (tools/options/server/activate DDE server). This activate live rate feeds to your xls rfom where you can use formula to c, like with a bberg station.
I will give it a shot, thanks. Now just how to export those opened trades.. and am ready to roll at faster speed. You cant imagine how much time I loose with typing numbers. And typing nonscence on FF
"I know one thing, that I know nothing." Sokrates
  • #22
  • Mar 2, 2015 5:04am Mar 2, 2015 5:04am
Quoting Tom_D_Trader
{quote} Hi Tigre, why not simply write indicators in *.xls? Just export price and Excel wil do the rest and insert indicators. T
Thanks Tom, well sometimes the indicator is very complex to calculate it in Excel...

Quoting JR97
Yeah. I've never used it but there's this:

https://www.mql5.com/en/code/12015
Thanks JR97, will try it.
  • #23
  • Mar 2, 2015 7:42am Mar 2, 2015 7:42am
Quoting tigretoncio
{quote} Thanks Tom, well sometimes the indicator is very complex to calculate it in Excel.
Type which you want, takes a minute. I hope . If cell commands wont work, can write macro VBA no problem.
"I know one thing, that I know nothing." Sokrates
  • #24
  • Mar 2, 2015 10:19am Mar 2, 2015 10:19am
Quoting Delta1One
[But for live indi in xls, that's another stuff...you'll need to store live data as they come and also use past data, depending how far you're tracking prices to make your stuff work. So if you need only live data, DDE is nice, but if you need price database to compile analysis, that's another stuff.
One of the indicators I posted a link to has the ability to export out historical data every tick. So it's essentially live data plus historical. I've built some pretty large scale trading platforms in Excel with this method. No need to try and capture data via DDE. But yes, DDE is good for capturing live quotes without doing anything fancy.
  • #25
  • Mar 2, 2015 10:23am Mar 2, 2015 10:23am
Quoting Tom_D_Trader
{quote} Dear JR, while offer still on the table, I need to ask you two more things . First, do you please also have mql script to export opened trades + rates, so I dont have to type em directly in portfolio table like in Middle ages? And second, How did you insert that nice window with code? My code posts are real mess and I wanna post some more. Thx, T
Yes. I can whip up and mql script to export opened trades plus rates. I might even have one already done and just need to modify.

To paste your code in a nice little window, on the far right, there is a "</>" button that should also highlight as "code". Use that. It's similar to the quotes window but doesn't truncate after x amount of lines.
  • #26
  • Mar 2, 2015 10:34am Mar 2, 2015 10:34am
Quoting JR97
{quote} ....No need to try and capture data via DDE.
I agree with you on that point JR'. I have had too many issues with DDE to bother with it any more. I find it more efficient and reliable to do what I need in MT4, write it to file and load that into Excel as external text data.
  • #27
  • Mar 3, 2015 6:49pm Mar 3, 2015 6:49pm
Quoting JR97
{quote} One of the indicators I posted a link to has the ability to export out historical data every tick. So it's essentially live data plus historical. I've built some pretty large scale trading platforms in Excel with this method. No need to try and capture data via DDE. But yes, DDE is good for capturing live quotes without doing anything fancy.
Hi Jr

I'll give a try. So just to be sure I got the "modus operandi":

  1. the script (the indicator you posted) catch data as they arise and write them in a file (right ?)
    -> if yes then it's a batch process (no?) -> then need to manage a procedure to say when to end (size limits/constraints/update frequency?) and switch to new file ?(named with something like yyyymmddhhmmss) -> then is it best/possible to have that saved under CSV comma delimited ? Or else ? (what do you recommend by experience ?) ps: I don't need a refresh every nano sec, I'm not in HFT :-)
  2. Then from xlsm I just draw a nice little button and attach VBE script to get above file stored in my rows. Right ? (or VBA to store in MS Access)
    I guess best way is to have 2 folders: "newdata" (receiving new files created by MT4 script and VBE checks there with a timer to see if anything there new to upload) and "treated" (to move files there after upload) -> this require some MS DOS commands to move file after use no ? Like call scriptname.bat (no?)

In case you have a MT4 script ready to extract positions (opened/pending/history) and send them to a file I can use xlsm to load, I'll be more than happy to play with ! This would speed-up my current process using manual copy-paste of html statements to xlsm.

Nice you came across this discussion Jr. And thanks to Tom for initiating !

  • #28
  • Mar 4, 2015 11:23am Mar 4, 2015 11:23am
Quoting Delta1One
{quote} Hi Jr I'll give a try. So just to be sure I got the "modus operandi": the script (the indicator you posted) catch data as they arise and write them in a file (right ?) -> if yes then it's a batch process (no?) -> then need to manage a procedure to say when to end (size limits/constraints/update frequency?) and switch to new file ?(named with something like yyyymmddhhmmss) -> then is it best/possible to have that saved under CSV comma delimited ? Or else ? (what do you recommend by experience ?) ps: I don't need a refresh every nano sec,...
The period converter optimized indicator I linked to can post out the Date/Time/O/H/L/C/V for all of the bars on the MT4 chart it's attached to a csv file. You can specify how often in the indicator parameters. The output file is named the instrument and time frame. eg [email protected] for a daily. [email protected] for a 15 min. bars. My original use was to do odd time frames like 2 and 4 min. charts. The indicator overwrites the file every time.

For one of the custom Excel trading apps with charting I had created, I had a timer to refresh the data import every 15 min. and then had the live DDE for displaying the current bar data. No need for doing any file handling. Just point Excel to the MT4 files folder.

I've also used a chart library that would read the .csv files directly and then updated the OHLC with live data which came from the DDE. Doing it that way was pretty much automatic with no need for a timer since I had MT4 putting out new historical every 5 min. for my 15 min. chart. Sometimes my excel dde and current candle calculations would get a little out of sync, but nothing drastic. (google chart director if you're interested in that library)

You can use the VBA I posted previously to access the data on the file to import it into the spreadsheet you work out of. I've got a pretty robust method I use to import for multiple pairs and is a framework for doing custom indicators and what not in VBA versus having the calculations done in the cells. Doing it this way really saves on the processing and heaviness of the sheet. It's some pretty heaving lifting using VBA class modules, class methods, and what not and pretty much a huge lesson in Excel programming and not just little snippets of VBA to do little macro copy/paste/formatting things.

Hope that helps. I'm putting together a series of "lessons" on how to do all of this but it's going to take a little time.
  • #29
  • Mar 4, 2015 11:30am Mar 4, 2015 11:30am
One more I thing I wanted to mention. Using Excel to create chart templates with things like custom pivot levels or whatever else you want on the chart is pretty simple. I've got a spreadsheet for doing a lot of that I can post up if I can find it. If not, I'll post up some snippets on how to do it. Just use Excel as the calculator per-se and then export out to the template file.

A while back I also created an MT4 script for importing data from a csv file. I used it draw horizontal and vertical lines generated in Excel. I'll post that up if I can find it and the Excel spreadsheet used to generate the files the script would import from.
  • #30
  • Mar 4, 2015 11:31am Mar 4, 2015 11:31am
Quoting One day!
{quote} I agree with you on that point JR'. I have had too many issues with DDE to bother with it any more. I find it more efficient and reliable to do what I need in MT4, write it to file and load that into Excel as external text data.

Hey dude! Long time. Just fyi, I've been away from trading for a while and not sure when I'll be back into actually trading again. But I've had a lot of ideas and been doing a lot of programming and scripting. Keep an eye on my journal.
  • #31
  • Mar 7, 2015 5:10am Mar 7, 2015 5:10am
hello sir, Thank ..
previously I still do not understand very well about the code to export to csv .....
I want to export to csv into a csv, but the script or indicator, which I used separately for each pair ....
I attach a file, hoping someone will help to be in a csv.
This is a script to export OHLC weekly in 5 bars ....
whether can be modif to 3 time frames (daily, weekly, and monthly), and so one csv?

Attached File(s)
File Type: mq4 Dsn_5barsONLY_QuoteToExcel.mq4   5 KB | 821 downloads
Regard, Ding Dong
  • #32
  • May 11, 2015 10:04am May 11, 2015 10:04am
Hello. I'm sorry to be a pain but hopefully there is an easy answer to this question. Its my first post on FF and I'm not sure where I should post it.......

I want to export the previous weekly data for about 6 pairs into excel. It would look something like this....

w/c
Open Close High Low
GBPAUD
EURUSD
etc
etc
etc

At the moment I only want the data for the last week but I want it on one excel sheet for further analysis....its to save time and avoid human error in transferring data pair by pair.

I hope someone can help or point me in the right direction.

Thanks.
  • #33
  • May 11, 2015 11:05am May 11, 2015 11:05am
Quoting ozcabman
Hello. I'm sorry to be a pain but hopefully there is an easy answer to this question. Its my first post on FF and I'm not sure where I should post it....... I want to export the previous weekly data for about 6 pairs into excel. It would look something like this.... w/c Open Close High Low GBPAUD EURUSD etc etc etc At the moment I only want the data for the last week but I want it on one excel sheet for further analysis....its to save time and avoid human error in transferring data pair by pair. I hope someone can help or point me in the right direction....
Hi Oz,

not sure what exactly are you looking for,..but assume its like D, h4, h1 candles of last week? Is very simple if you have metatrader, simply press ctrl+s while on chart and it will save timeserie in *.csv format.

We ve been talking here about live export into excel sheet here:
Quoting JR97
{quote} One of the indicators I posted a link to has the ability to export out historical data every tick. So it's essentially live data plus historical. I've built some pretty large scale trading platforms in Excel with this method. No need to try and capture data via DDE. But yes, DDE is good for capturing live quotes without doing anything fancy.
- it is the first page of this little thread. I havent tested it yet, as I was busy loosing money.

This is my mumbo jumbo formula to convert *.csv into readable OHLC format..but is very crappy code,not recommended to use .
Quoting Tom_D_Trader
Ok, I have made it. It was all in this little thingie. If you wish to convert text format into date, default .xls format is yyyy-mm-dd. Meaning if primitive like me, you just somehow force it inside and done! So this one working, has been tested on 0anda MT4 export Monthly,Weekly and Daily. FF wont let me upload .xlsm here, so I describe how it works. You make an excel sheet with four lists, called Menu Data_crushed Data_final and Data. Once in MT4 chart, press ctrl+s and save *.csv. Copy data from *.csv to list Data. Run macro. It will crush dates...
This is from JR on the same topic, looks very promising, but as usuall I havent found time to test it:
Quoting JR97
sorry I didn't get back to you sooner. I was out of town. Basically, there are two different styles to VBA. One is the macro code. The other is the object model. The object model is more akin to Visual Basic 6. The object model is a lot cleaner, easier to manipulate, and less prone to the errors you get using the macro recorder which is dependent on things such as active sheet, active cell, etc. The easy way to remember the basic model structure is that it's hierarchical. Workbook.Worksheet.Range(column_cell:column_cell).properties Below is some...
GL,
T
"I know one thing, that I know nothing." Sokrates
  • #34
  • May 11, 2015 12:27pm May 11, 2015 12:27pm
Thank you Tom.

What I want is the weekly data from a few different pairs onto one excel worksheet. Sorry my 1st message wasn't too clear. But the sheet would just look like this....

GBPAUD HIGH LOW OPEN CLOSE
EURUSD HIGH LOW OPEN CLOSE
ETC HIGH LOW OPEN CLOSE

I'm looking to place trades on a Monday across multiple pairs based on the weekly data only......I think its probably a macro I may need to write??

Thanks again.
  • #35
  • May 11, 2015 3:42pm May 11, 2015 3:42pm
Quoting ozcabman
Thank you Tom. What I want is the weekly data from a few different pairs onto one excel worksheet. Sorry my 1st message wasn't too clear. But the sheet would just look like this.... GBPAUD HIGH LOW OPEN CLOSE EURUSD HIGH LOW OPEN CLOSE ETC HIGH LOW OPEN CLOSE I'm looking to place trades on a Monday across multiple pairs based on the weekly data only......I think its probably a macro I may need to write?? Thanks again.
I think yes, prolly the best would be to test that indi of JR. Hold thumbs to my EU longs to 1,121, if hit, am gonna have some time for testing .
Like indi exports data, macro manages them in one sheet. Should be simple.
T
"I know one thing, that I know nothing." Sokrates
  • #36
  • May 11, 2015 5:03pm May 11, 2015 5:03pm
Thanks.....yep I need to get my head around that stuff in this thread.

Good luck with your trading.
  • #37
  • May 12, 2015 2:01am May 12, 2015 2:01am
If you are only interested in a few pairs, you could just copy and past the data that you need from the data window in your MT4 terminal....

Attached Image (click to enlarge)
Click to Enlarge Name: excel mt4.png Size: 64 KB


Which when pasted into Excel, looks like this....

Attached Image


No coding, but for a few pairs it might do the job....

Regards,

OD
  • #38
  • May 12, 2015 4:54pm May 12, 2015 4:54pm
Quoting JR97
One more I thing I wanted to mention. Using Excel to create chart templates with things like custom pivot levels or whatever else you want on the chart is pretty simple. I've got a spreadsheet for doing a lot of that I can post up if I can find it. If not, I'll post up some snippets on how to do it. Just use Excel as the calculator per-se and then export out to the template file. A while back I also created an mt4http://cdncache-a.akamaihd.net/items...rrow-10x10.png script for importing...
Waiting eagerly Sir....
~hanuraj
  • #39
  • May 14, 2015 10:04am May 14, 2015 10:04am
Thanks One Day!

I think that is the way I will do it.....still working my way around MT4 so thanks for the help.

Be lucky.
  • #40
  • Jun 24, 2015 5:06pm Jun 24, 2015 5:06pm
Quoting JR97
sorry I didn't get back to you sooner. I was out of town. Basically, there are two different styles to VBA. One is the macro code. The other is the object model. The object model is more akin to Visual Basic 6. The object model is a lot cleaner, easier to manipulate, and less prone to the errors you get using the macro recorder which is dependent on things such as active sheet, active cell, etc. The easy way to remember the basic model structure is that it's hierarchical. Workbook.Worksheet.Range(column_cell:column_cell).properties Below is some...
Hi JR,
havent wrote to you for some time, but now I gotta. As GU+EU corrects (and I got killed) finally found myself time to test it. a) it works wonders b) I have learned very much from this little macro of yours. You have my many thanks. It is gonna save me lotsa time with loading those csv into excel. I owe you one. Btw, please could you tell me how have you inserted that window with code into post? I wanna post some codes, but my posts are much more messy without that nice window.
Thanks again,
Tom
"I know one thing, that I know nothing." Sokrates
0 traders viewing now