Supply and demand daily and weekly levels spreadsheet

Hi Graham,

The video that explains padding and wiggle room is on Alfonso’s other thread at Forex Factory

“Supply and demand Swing Trading Journal by Alfonso Moreno”

page 1 video #6 or on post 1973 page 99 here: http://www.forexfactory.com/showthre...35#post6953535

I hope this helps.

Joe

Hi, I have made some changes to the workspace. It only includes the information provided by Alfonso. I have worked on the position size calculator and I have added a partial profit column incase that is part of your trade plan. Next (and hopefully last) version will account for up to 3 sets of trade input (unless you think more is required). I am attaching 2 workspaces one is for mini and the other is for standard accounts. Let me know if you are having any problems or if you have any ideas to add to this workspace.

Thanks,

Joe

EDIT:

I have been going over the spread sheet and found a miscalculation in the position size calculator and miscalculations on sheet 5. So I am going to fix that and post the current fixed version soon. It has some nice changes. As I mentioned before I am not a programmer just a self taught dabbler so please bear with me. I am so sorry for all the different versions and any inconvience.
Thanks,
Joe

Hi, I believe that everything should be working right. This version is more stream line. It requires some input on sheet 1, it then gives you a couple of steps to follow. Enter your trade data on sheet 3 and your actual prices are displayed on sheet 4 along with the size of your position and where you are in the curve. I hope I have all the kinks worked out, I’ve yet to test this live so who knows. Again sorry for any inconvenience that associated with the earlier versions. I’m going to see how this works over the next few weeks, fingers crossed, if everything is working properly I will do a version where you can have multiple entries for a single contract. Also I am in the U.S. so I can’t trade metals or equities, that is why I don’t have them included in the workspace.

Joe

Hi,

I might not be able to put all of this in one post so please bear as I explain what is in this worksheet in detail over the next couple of posts and how I plan to use it.

It is comprised of 8 sheets.

Sheet 1 asks a couple of questions.

Sheet 2 is the spreadsheet from post 1 of this thread

Sheet 3 is where you enter your trade data and timeframe

Sheet 4 is where all of your final trade data is shown

Sheet 5 is an area where you can write your trade description and notes

Sheet 6 and 7 are where all the calculations are made

Sheet 10 is the correlation table provided by Alfonso

I have rebuilt the spreadsheet/tool to also incorporate the daily wiggle room and padding data. Because there were more pairs discussed on the other thread I have added the ones I have access to. You now can place data for multiple trade setups per pair using different time frames (incorporating various long and short scenarios). There were some flaws in the previous versions of the workspaces. Alfonso mentions in post 1 page 1 that the cells are conditionally formatted so if you move them (which I did to another sheet) then you would have to reset the conditional formatting. In previous versions I haven’t done that -----this version I have (so on this version Sheet 2 works properly the way Alfonso intended it to work so again sorry for any inconvenience!!!). Also in testing when putting on a trade where I had 1% risk I had lost 1.4% so I re- researched the formulas for the position size calculator and found that some of the formulas were wrong ---- so I fixed those formulas and tested the new ones (tested and retesting the formulas and verified the results with the web sites provided on sheet 7). So all the mentioned bugs appear to be fixed so this is the current version I have for us to use.

That being said-----

This is a tool where everything is in one place. I have tried to incorporate as much as I can so you can use the features that are specific to your trading needs and tailor the sheets to your style. On sheet 1 you enter your basic data in the light blue data boxes. If you don’t understand anything please ask and I will help and explain my rationale if you have any questions. Please note after a position is closed you have to reenter your new account value.

I found this feature that I will use throughout the workspace on the different sheets. It is a hide and show function. This way, if you like, you can set up the sheets to your own preferences and hide the features that you aren’t using. I will give snapshot examples from sheet 1 to show you how to use it just in case you never have done so before.

**Note be careful not to click on delete by mistake (which I have just done arrg) because you might not get the data back and then you would have to reinstall the workspace or close it and don’t save your changes**

So to use the hide function, In the Snapshots below --- on sheet 1 left click your mouse on the # 3 on line 3. It will then highlight row 3 in blue

Hold the left mouse down on that # 3 and drag it to # 11 and it will highlight all those rows in blue

Release the left button of your mouse and then right click, a grey box will appear and the 7th line of data will say “HIDE”. Left click on that and rows 3 through 11 will now be hidden and now on the left row you will see # 1, 2 then 12. To reopen them go through the same steps highlight 2 and 12 and now left click on “SHOW” and it brings back the rows.

Let me know if you have any questions or if you have any ideas to improve upon this workspace in any way.

Thanks,

Joe

On sheet 2 where you enter the distal and proximal data for the monthly, weekly and daily data I have added Columns M, V and AE, I use a 1 or 2 to mark the pair that is updated. 2 indicates and is highlight in Bordeaux if the S/D area has changed. I just want to make a note and to show that the market dynamics have changed. I have also added a Last analyzed section so I see when I did the work last.

I will use the hide and show function on this sheet to hide the daily and weekly section when working on the monthly and do the same when working on the weekly and daily. For me this is easier to locate the cells and pairs that I am currently working on.

This work is essential for S/D trading so you know where you are in the bigger picture timeframe curve ---- so now all of these calculations are carried forward and are essential for the next spreadsheets.

On Sheet 3 you enter your timeframe (1 for 1hour, 4 for 4 hour and 2 for daily) and trade data. You can have up to 5 different trade data sets for each pair depending on how advanced your trading is. So you can have buys and sell combinations for the 3 different timeframes we use. It also colors the cells blue for buy/longs and red for sells/shorts. I have added the curve here as well so it shows you where you are in the curve.

All the calculations are made on sheet 6 and 7 and presented on sheet 4. I have added the correlation spreadsheet provided by Alfonso In case you have multiple trade setups and you think your risk is too high. You would use this to lessen your risk and pick out the better trade from the highly correlated pair.

I would use the Hide function here by hiding the pairs that are not high or low in the curve or if you prefer to just show 1 row per pair you can do so also.

Fortunateson,

You did a fantastic work here. Thank you.

It would be a great idea to integrate all this (files + explanation) in the first post.

Sheet 4 is where all the calculated data is shown. It shows your timeframe in column B. C, D and E are your actual prices that include padding, wiggle room and the spread. Column F is your position size and it takes into account multiple trades (thus lessening your size). Column G is a take partial profit. I use actual risk here – so it adds padding etc. so if your stop is 20 pips it would actually be 30 pips. VS. for Column H when to move stop to break even I use implied risk where I don’t include padding etc. So if your stop is 20 it stays 20 so if your break even ratio is 4, 80 pips would be the BE value. Columns I,J and K are the curve. Columns M,N, O, and P are the actual tic differences for stops, partial profit, break even and target.

A couple of ways to use this I have displayed the prices up so you can set any needed alerts.

I have displayed the actual pip amount In case you have a trade manager/EA that uses the data that way. I don’t want to recommend any trade managers/EA here but I have one that I use and you can find it on my subscription list at my profile.

On this sheet I have the trade area compressed to only show 1 trade per pair vs. sheet 3 where the whole field is open. I probably would only show the actual trades I am working or in. Also I would hide the columns that you don’t plan to use.

Sheet 5 is a compressed area where you can open up to write down your trade notes and rationale. I have left this field blank for now because of the subjectivity in trade explanations.

I hope this all makes sense and can be a useful tool for you to use. I wanted to put everything in one place and organize it in a fashion where it takes out all of the guess work and makes trading more robotic. Sorry If I have hijacked this thread that wasn’t my intention I just wanted to contribute.

Thanks,

Joe

Thanks FerruFX, My only concern is that the work in not verified by someone else and that there still might be some cells that are flawed because my previous versions had several errors. Or someone might have a better way of showing/compling this.
Joe

Let's see how all this goes ... and when "final" version will be opproved, an update of the post #1 would be great.

Many thanks to Joe for his Herculean work. I don't use all of his spreadsheet yet, but it looks very useful. However I don't like to manually update it... I make many mistakes with transliteration etc.

So, I have written an MT4 indicator (!MC OBJ_GLOB) that reads the objects generated by Rectangle Reader, appends the symbol name, and writes global variables that specify the proximal and distal lines of the demand and supply zones nearest the bid. I have also written a script (!MC Export Globals) that reads the globals and writes the result to a file. Finally I converted one of the Supply_Demand spreadsheets to Excel and wrote a macro that reads the file and writes the results to the appropriate cells. All of this allows me to update the MN1, W1, and D1 zones automatically instead of manually. It all works well in Win XP but I have a problem in WIN 8 because of the screwed up permissions in the latter.

I also wrote a macro that allows one to add pairs without requiring him to change the cell functions that read the price from MT4 DDE server.

It is all a work in progress, so I will not post the indicator, script or Excel sheet here, unless there is some interest. I am quite sure that someone who speaks the ODS macro language could convert the macros to ODS so that the same approach could be used to automate the ODS spreadsheet. I do not intend to learn ODS. If you are interested, PM me.

MadCow

Of course there's some interest. This is exactly what we were all looking for. Once finished, it would be a wonderfull tool.

Thank you and keep up the great work.

Thanks MadCow,

I also make many mistakes and am definately interested in a tool that would help streamline and ease the process.

Joe

I think that kind of tool is exactly what we are all waiting for. Will it work in excel win 7 ?

You need to talk about this on the other thread in trading journals.

Joe! Thank you so much for your work. I am loving it.

April

Hi Joe,

Congrats on the work you have done. I'm sure it will be tremendous help to lots of people.

I've had a quick look at Sheet 2 and will look more when I have time but one thing I would suggest to change is the following to cater for different symbols with different brokers. You use symbols with suffix "m" on your broker and other brokers may not use that or may have a different one.

So, on Sheet 2 I would suggest you Label column N as "DDE SYMBOL" and in that column you have the MT4 symbol used in the DDE transfer.
Then change all cells that reference MT4 eg change from =DDE("MT4";"BID";"EURUSDm";1) to =DDE("MT4";"BID";N5;1) etc etc

Then in cell N5 you enter the MT4 data Symbol eg EURUSD or EURUSDm or whatever is specific to your broker.

That way users only need to change in once place their MT4 symbol and spreadsheet will update.

Peter

i just tried to use this sheet, but i am unable to understand sheet 2 , i dont know how to use open office. can you please tell me how to add price and where to add those values?

Hi Peter,

Thanks!! Thats an excellent idea, I will work on that and post the updated workspace soon.

Hi Solomon1a,

This is explained in detail by Alfonso on page 1 post 1 on this thread at http://www.forexfactory.com/showthread.php?t=424894 Your spreadsheet values might not be updating correctly because of what Peter mentioned in the above post. On previous versions I had included a Standard as well as a Mini version of the workspace depending on how your broker quotes the pair. So I apologize the I did not add the standard version. I am going to make the change that Peter reccomended and post that with the standard values(this way both are available). Let me know if that clears things up, if not I would be happy to help in any way.

Thanks,

Joe

I have made the changes as Peter has suggested. So on Sheet2 column A it is called DDE Symbol and the standard symbol names are there (for example EURUSD). If your broker designates a mini (for example EURUSDm) then you can change the symbols on this workspace or use the one provided above on post 85. I would call your broker and ask them what their DDE symbol is if they use something different that these 2.

Also the video “How to mechanically pre-plan a supply and demand trade” is now # 3 on page 1 post 1 on Alfonsos’ main thread at http://www.forexfactory.com/showthread.php?t=428204

Thanks and have a great weekend,

Joe