Supply and demand daily and weekly levels spreadsheet

SUPPLY AND DEMAND SPREADSHEET

The Supply and Demand spreadsheet that can be downloaded from this thread, was created with the goal of having a quick snapshot of where the currency pairs I'm trading every day are located in the Daily and Weekly Demand and Supply Curve.

I believe that knowing where you are located in the supply and demand curve is key to have success in the markets. After all what we all want to do is "buy low" and "sell high" in order to increase our odds and obtain as much benefits as we can from the supply and demand imbalances in the markets.

This spreadsheet could be much better if I had the programming skills, but so far it's an amateur trial to obtain BID/ASK prices from Metatrader by using DDE Server, that's all. The other calculations, changing cells' background colors, calculating how low or high price is in the curve, etc, are all done based on BID/ASK prices and manually entered proximal and distal lines for D1 and WK supply and demand levels.

There is also a png attachment that will give you a quick visual look of what the spreadsheet for supply/demand looks like.

WHAT IS A DISTAL LINE? WHAT IS A PROXIMAL LINE?

An area of supply or demand is not just a single price/line (like classic support/resistance), but a zone/area composed of a number of pips. The highest part of that (rectangle) is the distal line, that is, the highest price in the zone. The lowest part of that rectangle is the proximal line, which is the lowest and the closest to current price.

- The closer you are to the a supply area, the better for selling or exiting your long positions
- The closer you are to the a demand area, the better for buying or exiting your short positions

The sole purpose of this spreadsheet is to allow you to know how high or low you are in the D1 and WK supply and demand curve if you manually add the distal and proximal lines for both D1 and WK SD levels on your charts, or whatever timeframe you want to use as the curve (demand and supply in control of current price)

All proximal and distal lines prices are manually entered, you can change them and the spreadsheet will do all the calculations for you. Levels might differ depending on brokers and of course depending on what you consider to be the demand and supply areas in control of price at any given moment.

Drawing supply and demand levels can be considered an art. You might see levels everywhere if you are new to supply and demand, with time you will have to create very simple and strict rules to filter the bad ones out as much as possible.

Features of this spreadsheet:

• There are 2 different areas to display D1 and WK Supply/Demand D levels. They are displayed between current price zones
• You can use other timeframes like H1, H4 as the curve. You just have to add the prices for the H1/H4 levels that you found and the spreadsheet will do the calculations for you. In other words, you can use any timeframe, spreadsheet has been created to have room for 2 timeframes, so you can use D1/WK, H1/H4, any 2 timeframes combination
• Each supply and demand area is composed of these fields: current price, % location in the curve, distal price and proximal price
• PIPS left for proximal line to be hit. These are the 2 pips fields located to both sides of current price
• It will calculate how low or high we are in the D1 and WK curve in the form of a %. The higher the better for selling, opposite for buying

CONDITIONS AND CELL HIGHLIGHTING
There are a few conditions that will change the look of any given cell in order to visually help you distinguish what is going on with all the currency pairs.

• Percentages, curve % or altitude (how high or low in the curve) are calculated by using the proximal lines, a simple subtraction
• SD level about to be hit. If current price is between 0 to 40 pips from hitting the proximal line of a SD level, the pip cell will be highlighted to yellow
• SD level broken. If current price is higher than the distal line, the cell will be highlighted in Purple. This will mean that the SD area has been broken
• SD level has been hit, price is within the level. If current price has hit the proximal line, the proximal line cell will be highlighted in Red. This will mean that current price is within that area
• SD level has been penetrated more than 50%. If distal line cell is Yellow, it will mean that price has penetrated your SD zone at least 50%
• Price is low in the curve . If we are low in the curve 0-40%, the % cell will be highlighted in blue
• Price is in the middle of the curve. If we are 40-60 % in the curve, the % cell will be highlighted in green
• Price is high in the curve. If we are high in the curve 40-60 %, the % cell will be highlighted in green
• Spread is too high. If spread is bigger than the average on your broker (this is a personal setting), then the spread cell will be highlighted in yellow

Why all these conditions?

This is the first thing I do in the mornings or at any time of the day that I start trading, I will know which pairs I should look at first, because if the levels are broken or penetrated, if price is too high or low in the curve or, I will be interested in setting a limit order at the first valid previous SD level with a good imbalance, to keep on trading in the direction of the breakout or the rejection.

How to add more currency pairs or how to modify existing ones if your broker uses suffixes for symbols?

• Many brokers use suffixes like . pro, m, etc, that are added at the end of the symbol. If that is your case, you need to edit the current price cell formula =DDE("MT4";"BID";"EURUSD";1) and change it to whatever your symbol is, like for example: =DDE("MT4";"BID";"EURUSD.pro";1). You have to change that for both current price column and spread column, because the spread column uses both bid and ask prices
• There are some calculations that use columns on the far right on the spreadsheet and some variables lower. If you move the cells or columns, some of these calculations will not work, like the % of penetration of a level, because it uses a fix variable within the conditional formatting, like Sheet1.$AC$9<W49. If you move that variable from cell W49 to another cell, then a few conditional formatting will stop working
• If you see a cell with N\A or #VALUE, it will mean that either there is no connection to MT's DDE server, that that pair is not supported by your broker, that pair name is different in your broker (maybe some suffix/preffix need to be added to root name), or that you haven't changed permissions on Windoews' UAC Module as described on the "How to install" below


WHAT DO YOU NEED IN ORDER TO INSTALL THIS SPREADSHEET?
• Tick the DDE server checkbox to true under your Metatrader Tools/Options's server tab
• Download and install Open Office Calc. Excel was giving me a lot of problems with prices, comma separators and it blocked sometimes
• Set the permissions for UAC (User Account Control) to low, or DDE will not work. Follow this tutorial and set it to the lowest low http://www.howtogeek.com/howto/3340/...-in-windows-7/

1. Once you have MT running and ticking (not before), load the spreadsheet on your Open Calc
2. When it prompts a popup, say YES to update content
3. THAT'S ALL

NEW ADDITIONS
I've many other ideas in mind, but I don't have the programming skills to code them. It would require MQL coding and on how to exchange information between MT4 and Excel/Open Office Calc. There are ways to automatically calculate brand new 1H/4H/D1 levels, or any timeframe's levels. This is great because at any moment in time, the spreadsheet would be able to detect real time all new SD levels and filter them by age, pair and how fresh they area.

If anybody out there has the knowledge to do this, please contact me, it would be an incredible and powerful tool for those who trade supply and demand level, which is what moves the markets after all.

Any feedback is welcome! This is just the start of what I believe could be a bigger project that could make trading for those SD traders easier and funnier.

P.S. Excel version. I've not created this spreadsheet under Excel because it gave me a lot of problems. If anybody has the knowledge and can do it under Excel, it would be great.

Hello Panoramia!

I found your link on Ken's forum. I am new to the whole forex universe and I found the supply and demand that Ken (and Sam S) put forth to identify when the BIG boys are acting very interesting and some what easy to follow. I too took a position with USDCAD this morning and waiting to see how it pans out.

I'm very interested in your spreadsheet and will take a look at later tonight. If you don't mind, I'll play around with it and see if I can get it to work in excel. I'll let you know.

If I have any questions, I'll PM you.

Have a good one!

Jim

Thanks a lot for your reply Jim, I appreciate it. I love supply and demand like I think most in this thread do
Take a look at it, it's the first version I've created, it's all based on what each of us believes to be D1 and WK supply and demand levels. The spreadsheet will do all the calculations according to the prices entered for proximal and distal lines.

Hope that you enjoy it!
Alfonso

I can help you with the coding, but I'm going on vacation from tomorrow till 13th of May, PM me around this date and we may try to make it all MT4 and no excel needed ... I will have to dig to the code of the ods also ...

Good luck Alfonso with this thread

What is your problem with Excel? One of the best forum for Excel help is MrExcel.com.

My platform is Multicharts and I'm using Elexcel.dll to send data from Multicharts to Excel. I'm also using Global variable to create a communication link between Multicharts and Excel.

Martin

Thanks a lot for your quick reply, I really appreciate it. I will contact you when you are back from your holidays.

I'm not a coder so I can't build complex things. I would need MQL programming knowledge in order to code what I need. I wanted to create an Excel sheet or MT4 indicator that will automatically detect all SD levels on selected timeframes. I do have the indicator (which I had modified a bit) that draws SD levels automatically. If we add that the % in the curve, if level is fresh or not, etc, we'll be able to have a system that will tell you the odds of the levels detected. Mostly when we are near a D1/WK level which is too wide and there are no lower timeframe levels to lean on to place a trade. It's also a good thing to do it automatically because the indicator would detect when new H1/H4 levels have been created and show them on a table together with how high/low in the D1/WK curve is located.

So by looking at a single screen you will know age of brand new levels created on certain timeframes, if they are high or low in the curve, whether they are fresh or not and how long ago it was created (1 hour, 5 hours, etc). So you start trading and you will visually see which are the pairs that are moving and new levels created to trade pullbacks into it if they rules match your trading plan, direction, etc.

That's the idea amongst other details I've documented !

Have a great holidays!
Alfonso

Thanks for your wishes Martin. My problems with Excel had to do with excel freezing most of the times, then receiving prices in raw format. I had to apply style to it and then came the problem of period separators. Some prices were received with . and others with , so it was a mess and I just stopped doing it with Excel. I tried with Open Office Calc and it gave no problems.

I'm not a coder so I can't build complex things. I would need MQL programming knowledge in order to code what I need. Someone over here (sypherBG) offered to help. I want to create an Excel sheet or MT4 indicator that will automatically detect all SD levels on selected timeframes. Part of this nformation is in the previous message. I am using MT4 so Multicharts is not for me for now!

Thanks a lot again!
Alfonso

Panoramia, the tool is great! I was thinking to build one for myself don't have the skills to do it so, thank you very much!

By the way, already asking for things, is it possible to add hourly supply/demand levels?
Because I am very short term trader, I use the 1hr supply/demand areas to trade off of.

Do you think it is possible?

Thanks in advance!

Hi Joao!
I am glad that you like the spreadsheet, I really appreciate it.

As far as showing the H1 or any timeframe supply or demand, you just edit the distal and proximal price lines and that's all. You rename the WK SUPPLY adn WK DEMAND headers to H1 DEMAND and H1 SUPPLY and you manually add the prices, the rest will be done automatically. Once prices are added, the formulas will calculate the how high or low you are in the H1 curve or whichever curve you decide to use. Since you will be adding the prices for SD levels manually for any timeframe, the spreadsheet will work with any timeframe for which add prices.

I hope that I was clear about it!
Thanks again!

Alfonso

Thanks for your prompt answer!
I got it!

But, what about if I want to add a new time frame, while keeping the others? For example, I want to keep the daily and weekly and add the hourly?
Is it just a matter of copying the cells and duplicate it elsewhere?
Sorry if the question is not clear.

Thank you!

By copying and pasting you will achieve it part of it, but there are some conditional formattings and extra columns to calculate each SD level width, pips left to be reached, etc. It's not just copying and pasting, you need to duplicate other columns in the far right of the spreadsheet, initially not seen at first sight.

It's also very important that one decides which will be your supply/demand curve for your entry timeframe. The more information that you have the more confusing it will become, that's my opinion. I use the D1 as my curve but I like to know if the D1 is near or at a WK SD zone. Having the H1 as cuve is not part of my trading plan.

So far, the spreadsheet will support for now 2 timeframes, just decide which ones you want to use. I could add 3, 4, etc, but that would just be too much. In fact, you not be able to see so much information in a normal monitor or laptop.

I suggest that you reuse one of the 2 timeframes, if your entry timeframe is M5/M15, H4 and D1 would be perfect as the curve. If H4 is near or at D1 SD level it'll probably be a higher odds trade.

Hope that's clearer.

Yeap, thanks a lot!
I do agree with you. I will reuse the curves there so.
H4/H1 or even D1/H1 would work. Will test a couple of combinations and find the best one for me.

Thanks for the hints!

And again, thanks a lot for the tool.
Cheers!

You are welcome, any feedback is welcome, just trying to stick with very simples rules, the simpler the better. Reusing those 2 SD areas is the best, nice that you like the tool, it's helping me a lot in terms of having a quick real time snapshot of what's going on with the curve in the pairs I trade.

Enjoy it!
Alfonso

Alfonso, thank you very much for this tool, it is very useful!. Finally it is possible to see in just one blink of eye what is going on many instruments.
Until now I've entered the SDZ for 30 instruments still few to complete ...., however having them all open it freeze Mt4. Is it possible to solve this somehow ? I have quite powerful PC but can't go thru . ..

once again thanks a lot!

You are welome! It's good to know that you find the spreadsheet useful, it's part of my trading plan to have a look at it and watch it.

I don't quite understand the freezing problem that you have with your MT4. I track 25 pairs with a slow PC and MT4 works fine with me, sometimes it takes time to load but it loads.

What is the exact problem that you have and with what? What do you have open at the same time? I don't understand
Alfonso

I like it very much, once again thanks.

Until 25 pairs was no problem everything was perfect. But in the spreadsheet there is all together 32 instruments, by adding this few more it caused probably slowing down my Mt4. By freezing (slowing down) I mean that: after I klick on some window or tool need to wait for respond 10-20s ... but basically you are right I don't need to have all of them so will delete some and problem solved ... no worries ..
thx a lot.

Yes, just delete content (not row) all the pairs you don't want to track, that's all, it shouldn't be a big issue after all. Just have there those you are going to trade

Enjoy it!
Alfonso

Hi, I’m a dabbler not a coder …. With that said I have this spreadsheet working on excel with the mt4 platform.

On metatrader 4 you have to go to Tools…Options….Server tab and enable/check the …Enable DDE server box and click OK. Then on Microsoft excel in the security warning area you have to click on options and enable this content and click OK … this should make this spreadsheet work.

It is following the same structure provided with the OpenOffice spreadsheet…. I don’t yet know how to color the cells red, green and blue but will work on that aspect.

Also I’m guessing that there is a way to export the distal and proximal lines from MT4 so they don’t have to be imputed manually….not sure so I will try and research that also but I think that is way beyond my basic skills. This is what I have so far let me know if I am on the right path.

Thanks

Joe

There is a way to export rectangle's distal and proximal lines from MT4 to Excel or any other software, but that requires serious programming, using DLLs and so on I amsure. If anyone out there has the knowledge to do it, I have other great ideas to make a better spreadsheet, but so far it's the way I'm doing it. It does not take much time to change it manually.

I tried your Excel spreadsheet, selected the security options you mentioned and what my Excel freezed, I had to use task manager. That is what almost always happened to me when using Excel. I used Open Office Calc because I didn't have such problems, that's why. I am using Excel 2007 version by the way.

The color codes area explained in the first post, we have yellow, purple, red and blue, each of them means a different thing. I used Calc conditional formatting in order to do that, which is also available on any Excel version. Would love to use Excel but it's not working well on my side. I read a few forums about MT4 and Excel and many many people are experiencing same problems, Excel will freeze and won't work.

Thanks a lot in advance!
Alfonso

The Open Office does a great job and thanks so much for sharing it. I really appreciate your work so I just wanted to make an attempt to contribute. The excel works fine on my end on 2 different mt4 platforms so I’m confused but moving on. I am grateful and plan on using the spreadsheet you have provided.

Thanks again,

Joe