It never gets easier, you just get better
Supply and demand daily and weekly levels spreadsheet
{quote} Hi MadCow, I need some help with you spreadsheet and indicators. I followed the instructions to install and run the indicator/script. When I try to update the zones I receive an error \\"Path not found\\" I don't think I am imputing my Mt trader path correctly. Please see screenshots attached. Please help when you get the chance. Thanks April{image} {image}
Hi April, Sorry that you are having problems. From your screen shot it looks like you may have entered the path in the comments box instead of the cell. Be sure to include the final backslash, and write the path into the cell. Do not include the experts\files\, just the path to your terminal.
If that's not the problem, then you may be using Win8 or Win 7. I have not solved the problem between Excel and Win 8 yet, but will report when (if) I do. This works with Win XP.
You can check that the indicator and script did their job by looking in the /experts/files folder for the 'SD levels.csv' file. If that is not there, then something is wrong in either the indicator or the script. Be sure to initialize the indicator after making your final zone rectangles, and then run the script.
MadCow
{quote} Hi April, Sorry that you are having problems. From your screen shot it looks like you may have entered the path in the comments box instead of the cell. Be sure to include the final backslash, and write the path into the cell. Do not include the experts\files\, just the path to your terminal. If that's not the problem, then you may be using Win8 or Win 7. I have not solved the problem between Excel and Win 8 yet, but will report when (if) I do. This works with Win XP. You can check that the indicator and script did their job by looking in...
Thanks for the help. I do have windows 7. Plus, I did enter the path in the comment box instead of the cell itself, a little embarrassed I am at that but I don't know excel too well.
Even when entering the path correctly in the cell I still receive the same message. I took off /experts/files as well. SD Levels.csv is in the files folder. What is happening in windows 7 that is preventing excel from finding the path? Security issue? Here is the debug report I get when I press the debug button on the error message: Hope it helps somewhat.Option Explicit
Private Sub CommandButton1_Click()
UpdateZones
End Sub
Public Sub UpdateZones()
Dim r As Long, c As Long
Dim filename As String, folder As String
Dim Pair As String, TF As String
Dim P1 As Double, P2 As Double, P3 As Double, P4 As Double
Close
folder = Cells(1, 3)
filename = folder + "experts\files\SD levels.csv"
Open filename For Input As #1
Input #1, Pair, TF, P1, P2, P3, P4
While Not EOF(1)
'If P1 > 0 And P2 > 0 And P3 > 0 And P4 > 0 Then
r = FindRow(Pair)
If r > 0 Then
If TF = "D1" Then
c = 6
ElseIf TF = "W1" Then
c = 15
Else:
c = 23
End If
Cells(r, c) = P1
Cells(r, c + 1) = P2
Cells(r, c + 5) = P3
Cells(r, c + 6) = P4
' End If
End If
Input #1, Pair, TF, P1, P2, P3, P4
Wend
Close #1
End Sub
Public Function FindRow(s As String) As Long
Dim r As Long
For r = 5 To 50
If Cells(r, 1) = s Then
FindRow = r
Exit Function
End If
Next
FindRow = 0
End Function
Private Sub CommandButton2_Click()
UpdateSS
End Sub
Public Sub UpdateSS()
'Scan Column 1 for symbol that ! match column 9 =MT4|BID!EURUSD
Dim r As Long, aStr As String, Symbol As String, k As Integer
For r = 5 To 100
Symbol = Cells(r, 1)
If Symbol = "DDE" Then
Exit Sub
End If
If Symbol <> "" Then
aStr = Cells(r, 5).Formula
k = InStr(aStr, Symbol)
'If k = 0 Then
aStr = "=(MT4|ASK!"
k = InStr(Symbol, "JPY")
If k = 0 Then
aStr = aStr + Symbol + "-RC[4])*10000"
Else
aStr = aStr + Symbol + "-RC[4])*100"
End If
Cells(r, 5) = aStr
aStr = "=MT4|BID!" + Symbol
Cells(r, 9) = aStr
' End If
End If
Next
End Sub
Hi April That is just the macro. It stopped at the yellow line because it cannot access the file. That will happen for several reasons, primarily if either the path or file name is wrong. You can tell if this is the case by hovering your cursor over the 'filename' in the yellow line. That should bring up the path and file name that the macro is using. From what you tell me that is probably OK, but you should check it out. If that's OK, then the problem lies with the interface between Win 7 and Excel. I think this is the same as Win 8. I am pretty...
Thanks, do you encounter the same error message that I do when you try on windows 7 or windows 8? If not, what error message do you get or issues you have? Also, the ods spreadsheet I downloaded from fortunateson works for me on windows 7. It automatically updates spread, price, pips, It has always worked for me.
I was able to get rid of the path not found error message. I am now able to update the proximal and distal lines whenever I press "update zones". However, your spreadsheet doesn't automatically update the price, pips and spread like fortunateson's does. Is this on purpose? It will update everything when I press update sheet but it would be nice if it was live, especially now that I can update my proximal and distal lines.
I was able to fix my error message by removing myself from Users group and only allowing myself to be in one group- administrators group with full control. I did this by Right Clicking on "My Computer" select "Manage" expanding "Local Users and Groups" Select Groups, select Administrators and add my user account to that group. I then selected the Users group and removed myself there. In windows, the most restrictive permissions apply, no matter if you are already an administrator. Since I was in the Users group I didn't have write access to the program files folder and those permissions applied.
To confirm you have Write Access to the program files folder do this: Right-Click on Program Files ( or where ever mt trader and excel are installed on your system) and select Properties. Select the Security Tab=> Advanced button=>Effective Permissions, under the Group or user name title there is a box. next to it is a button named "Select" Hit that button at add your user account then press okay, this will tell you all the permissions you have to this folder, you need to have all the "Write" attributes in order for Excel to read the sdlevels.csv.
Let me know if this works for you.
Thanks
April
Thanks April.
Yes I get the same message, so your solution may work. I will try later.
You said
"However, your spreadsheet doesn't automatically update the price, pips and spread like fortunateson's does. Is this on purpose? It will update everything when I press update sheet but it would be nice if it was live, especially now that I can update my proximal and distal lines."
No that is not on purpose. With Win XP the prices (and all associated calculations) are updated whenever they change. The update sheet button merely updates the sheet in case you have changed the order of the symbols, or added a new symbol. If it updates prices also, that is good news, since it means that the DDE link is operating, and merely needs to be reinitialized to update prices. Please check to see that pressing Update Sheet causes a price update. If that is the case, I may be able to add a macro that will update the prices every few seconds, so your verification would be very helpful.
Do you have Excel Options /Formulas / Calculation Options set to "automatic"? If so, the link to metatrader should work whenever a new price is received, and all calcs should follow. If you leave the setting at "manual" , you will have to press F9 to get a price update.
If you have problems with the price updates, you may have to set Metatrader and Excel to "Run as Administrator". If you do that to Metatrader, be careful because it may set all your indicator input settings to their default value.
Joe, I am sorry for you. I once had Vista, and eventually reinstalled XP. Vista has a lot of problems. I think Win 8 is really nice, but I have not yet mastered it's intricacies. My boss (She Who Must Be Obeyed) runs XP
Yes I get the same message, so your solution may work. I will try later.
You said
"However, your spreadsheet doesn't automatically update the price, pips and spread like fortunateson's does. Is this on purpose? It will update everything when I press update sheet but it would be nice if it was live, especially now that I can update my proximal and distal lines."
No that is not on purpose. With Win XP the prices (and all associated calculations) are updated whenever they change. The update sheet button merely updates the sheet in case you have changed the order of the symbols, or added a new symbol. If it updates prices also, that is good news, since it means that the DDE link is operating, and merely needs to be reinitialized to update prices. Please check to see that pressing Update Sheet causes a price update. If that is the case, I may be able to add a macro that will update the prices every few seconds, so your verification would be very helpful.
Do you have Excel Options /Formulas / Calculation Options set to "automatic"? If so, the link to metatrader should work whenever a new price is received, and all calcs should follow. If you leave the setting at "manual" , you will have to press F9 to get a price update.
If you have problems with the price updates, you may have to set Metatrader and Excel to "Run as Administrator". If you do that to Metatrader, be careful because it may set all your indicator input settings to their default value.
Joe, I am sorry for you. I once had Vista, and eventually reinstalled XP. Vista has a lot of problems. I think Win 8 is really nice, but I have not yet mastered it's intricacies. My boss (She Who Must Be Obeyed) runs XP
Just so you all know.
The 'Update Zones' macro works as follows:
Gets the symbol in the first column
Reads the ...experts\files\SD levels.csv file to find the symbol that matches the symbol in the row
Gets the proximal line data from the file
Fills in the cells that use this data, e.g. cell(5,7) uses the daily proximal supply line.
It repeats that operation for every symbol in the first column.
That is all that it does. It should not cause the price columns to update.
This macro requires that you have enabled macro's for this spreadsheet.
It may require that you run as administrator or some such nonsense if you use Win 7 or 8.
The 'Update Sheet' macro works as follows:
Reads the symbol in column 1
Uses this to write the formula that gets the spread and prices in columns 5 and 9.
All the other price columns simply refer to column 9.
It repeats that operation for every symbol in column 1.
That is all that it does.
If you look at the formula in cell(5,9) you will find =MT4|BID!EURUSD
where the symbol in the formula matches the symbol in column 1.
This causes the spread sheet to use a communication link called DDE that gets the Bid price for the symbol directly from Metatrader.
All of the other price columns refer to this column; they do not use DDE.
The formula in cell(5,5) will be =(MT4|ASK!EURUSD-RC[4])*10000
This gets the Ask price, and subtracts the bid that is in column 9.
I wrote this macro so that if you move your symbols around, or add symbols you will not have to change the formula in columns 5 or 9. This allows you to use symbols that are non standard, such as EURUSDm, so one spreadsheet fits all.
Most problems that have been posted seem to involve the price update. To isolate the problem:
First look at the formula in columns 5 and 9 and spot check that they match the above, accounting for the symbol.
If they do not match, press Update Sheet, and check again. If they still do not match, PM me and I will help trouble shoot.
If they do match, then try pressing F9 several times. This causes recalculation, and will cause the DDE request to be sent.
If this solves the problem, then consider setting your calculation setting to automatic, and you won't have to hit F9. However, this will cause a lot of unecessary DDE transfers, and may slow stuff down.
If this does not solve the problem, you have a problem getting the DDE link to operate. In Win XP, it operates correctly, however DDE is an older method of communication, so Win 7 and 8 are reluctant to support it, and you must coax them. I do not know how to do this, but perhaps someone here does. (Carousel46 has found a method by changing some Excel settings).
If the ods spreadsheet updates prices correctly with your OS, then it is probably a problem with some Excel setting. You might try running Metatrader as well as Excel 'run as administrator'. Or you might try installing Metatrader outside of the program files folder, since that folder is heavily protected by WIN 7 and 8.
I notice that Carousel46 has solved the DDE problem for Win 7. Congratulations
Please post your solution.
MadCow
The 'Update Zones' macro works as follows:
Gets the symbol in the first column
Reads the ...experts\files\SD levels.csv file to find the symbol that matches the symbol in the row
Gets the proximal line data from the file
Fills in the cells that use this data, e.g. cell(5,7) uses the daily proximal supply line.
It repeats that operation for every symbol in the first column.
That is all that it does. It should not cause the price columns to update.
This macro requires that you have enabled macro's for this spreadsheet.
It may require that you run as administrator or some such nonsense if you use Win 7 or 8.
The 'Update Sheet' macro works as follows:
Reads the symbol in column 1
Uses this to write the formula that gets the spread and prices in columns 5 and 9.
All the other price columns simply refer to column 9.
It repeats that operation for every symbol in column 1.
That is all that it does.
If you look at the formula in cell(5,9) you will find =MT4|BID!EURUSD
where the symbol in the formula matches the symbol in column 1.
This causes the spread sheet to use a communication link called DDE that gets the Bid price for the symbol directly from Metatrader.
All of the other price columns refer to this column; they do not use DDE.
The formula in cell(5,5) will be =(MT4|ASK!EURUSD-RC[4])*10000
This gets the Ask price, and subtracts the bid that is in column 9.
I wrote this macro so that if you move your symbols around, or add symbols you will not have to change the formula in columns 5 or 9. This allows you to use symbols that are non standard, such as EURUSDm, so one spreadsheet fits all.
Most problems that have been posted seem to involve the price update. To isolate the problem:
First look at the formula in columns 5 and 9 and spot check that they match the above, accounting for the symbol.
If they do not match, press Update Sheet, and check again. If they still do not match, PM me and I will help trouble shoot.
If they do match, then try pressing F9 several times. This causes recalculation, and will cause the DDE request to be sent.
If this solves the problem, then consider setting your calculation setting to automatic, and you won't have to hit F9. However, this will cause a lot of unecessary DDE transfers, and may slow stuff down.
If this does not solve the problem, you have a problem getting the DDE link to operate. In Win XP, it operates correctly, however DDE is an older method of communication, so Win 7 and 8 are reluctant to support it, and you must coax them. I do not know how to do this, but perhaps someone here does. (Carousel46 has found a method by changing some Excel settings).
If the ods spreadsheet updates prices correctly with your OS, then it is probably a problem with some Excel setting. You might try running Metatrader as well as Excel 'run as administrator'. Or you might try installing Metatrader outside of the program files folder, since that folder is heavily protected by WIN 7 and 8.
I notice that Carousel46 has solved the DDE problem for Win 7. Congratulations
Please post your solution.MadCow
Just so you all know. The 'Update Zones' macro works as follows: Gets the symbol in the first column Reads the ...expertsfilesSD levels.csv file to find the symbol that matches the symbol in the row Gets the proximal line data from the file Fills in the cells that use this data, e.g. cell(5,7) uses the daily proximal supply line. It repeats that operation for every symbol in the first column. That is all that it does. It should not cause the price columns to update. This macro requires that you have enabled macro's for this spreadsheet. It may require...
Thanks Madcow,
I had to set Excel calculation to automatic. Your spreadsheet works flawlessly now.
April
- #140
- Edited 11:32am Nov 19, 2013 4:12am | Edited 11:32am
Hi MadCow, first of all a big tanks for this great tool. 
I followed the instructions in the thread and on page 1 of the spreadsheet. Every step was without any warning or error.
Still I need some help, because the file looks like this, with the right levels, but look at the prices and the spread.
When I hit F9 or turn on automatic calculations, I get the second picture.
I'm using Win 7 and Excel 2010. The only thing I had to do in order to keep the macro's, was to save it as an xlsm-format
I really hope you can help me out here. (It looks like I cannot upload the two files at once on FF Error: Exceeds your quota by 1.5 MB)
I followed the instructions in the thread and on page 1 of the spreadsheet. Every step was without any warning or error.
Still I need some help, because the file looks like this, with the right levels, but look at the prices and the spread.
When I hit F9 or turn on automatic calculations, I get the second picture.
I'm using Win 7 and Excel 2010. The only thing I had to do in order to keep the macro's, was to save it as an xlsm-format
I really hope you can help me out here. (It looks like I cannot upload the two files at once on FF Error: Exceeds your quota by 1.5 MB)
