Macros designed to edit Macros?

[dilshod_k]

I have number of workbooks with almost identical Macros and quite often I have to make corrections to Macros in order to optimise performance. Is there any way to write code which would open all files one by one and made changes in Macros? Multiplying by copy and paste is not an option as there are small differences between the files contents and Macros. Thanks in advance for any suggestions.

 

[dilshod_k]

Don't want to waste your time because I probable will not be able to help you.
But in this script show what might you need to change.
You said you had to change Macros frequently.

In the script I published earlier nothing needs to be changed. I published it in order to explain how it works . But some of the formulas (highlighted in red color) in one of these macros need to be changed quite often, and doing it manually in each file is pretty tedious.:

Sub A_BUY_SELL_Signals_MasterSheet()
'
' BUY_SMA10_30_Crossover_SELL_Price_SMA10_Crossover Macro
'


'
Dim ws As Worksheet


For Each ws In Sheets

If ws.Name <> "Control" And ws.Name <> "Response" Then

ws.Activate
Range("I1").Select
Selection.NumberFormat = "?#,##0.00"
ActiveCell.FormulaR1C1 = "SMA10"
Range("I2").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-3]:R[9]C[-3])"
Range("I2").Select
Selection.AutoFill Destination:=Range("I2:I84")
Range("I2:I84").Select

Range("J1").Select
Selection.NumberFormat = "?#,##0.00"
ActiveCell.FormulaR1C1 = "SMA30"
Range("J2").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-4]:R[29]C[-4])"
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J84")
Range("J2:J84").Select

Range("K1").Select
Selection.NumberFormat = "?#,##0.00"
ActiveCell.FormulaR1C1 = "BUY/SELL SMA10"
Range("K2").Select
Columns("K:K").EntireColumn.AutoFit
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-5]>RC[-2],R[1]C[-5]<R[1]C[-2],RC[-2]>RC[-1]),""BUY"",IF(AND(RC[-5]<RC[-2],R[1]C[-5]>R[1]C[-2]),""SELL"",""""))"
Range("K2").Select
Selection.AutoFill Destination:=Range("K2:K84")
Range("K2:K84").Select


' Range("L1").Select
' Selection.NumberFormat = "?#,##0.00"
' ActiveCell.FormulaR1C1 = "BUY/SELL SMA30"
' Range("L2").Select
' Columns("L:L").EntireColumn.AutoFit
' ActiveCell.FormulaR1C1 = _
' "=IF(AND(RC[-6]>RC[-2],R[1]C[-6]<R[1]C[-2]),""BUY"",IF(AND(RC[-6]<RC[-2],R[1]C[-6]>R[1]C[-2]),""SELL"",""""))"
' Range("L2").Select
' Selection.AutoFill Destination:=Range("L2:L84")
' Range("L2:L84").Select
' ActiveWindow.ScrollColumn = 2
' ActiveWindow.SmallScroll ToRight:=7
'
' Range("M1").Select
' Selection.NumberFormat = "?#,##0.00"
' ActiveCell.FormulaR1C1 = "BUY/SELL SMA CROSSOVER"
' Range("M1").Select
' Columns("M:M").EntireColumn.AutoFit
' Range("M2").Select
'
' ActiveCell.FormulaR1C1 = _
' "=IF(AND(RC[-4]>RC[-3],R[1]C[-4]<R[1]C[-3]),""BUY"",IF(AND(RC[-7]<RC[-4],R[1]C[-7]>R[1]C[-4]),""SELL"",""""))"
' Range("M2").Select
'
' Selection.AutoFill Destination:=Range("M2:M84")
' Range("M2:M84").Select
Range("N2").Select

End If
Next ws

ActiveWorkbook.Save

 

[My Aswer Is This]

Just curious again:

Why do you have all these ' in front of all these lines of code?

When you put a ' in front of the line of code your telling the script to not do that line of code.

See here you have:

' Range("L1").Select
' Selection.NumberFormat = "?#,##0.00"
' ActiveCell.FormulaR1C1 = "BUY/SELL SMA30"
' Range("L2").Select​

 

[dilshod_k]

Just curious again:

Why do you have all these ' in front of all these lines of code?

When you put a ' in front of the line of code your telling the script to not do that line of code.

See here you have:

' Range("L1").Select
' Selection.NumberFormat = "?#,##0.00"
' ActiveCell.FormulaR1C1 = "BUY/SELL SMA30"
' Range("L2").Select​

When I do not need all the data for analysis I simply inactivate part of the code. I might need it sometime later so I do not erase it. This time I did not need data in columns L and M.

 

[My Aswer Is This]

Ok. And now what was your logical reason for wanting to not run theses lines of code.

See if there is some logical reasoning we can use a if statement.

Like for example If Today is Monday do not run these lines of code.

Is there some standard logic here?

Or if Range("A1") value="Mom" do not run these line of code.

 

[dilshod_k]

Ok. And now what was your logical reason for wanting to not run theses lines of code.

See if there is some logical reasoning we can use a if statement.

Like for example If Today is Monday do not run these lines of code.

Is there some standard logic here?

Or if Range("A1") value="Mom" do not run these line of code.

Some of these columns contain indexes (SMA10 - Simple Moving Average for 10 days; SMA30 - for 30 days (in technical analysis of the stock price graphs there are dozens of various indexes)), others designed for BUY/SELL signals (based on conditions like you mentioned: =IF(AND(F2>I2,F3<I3,I2>J2),"BUY",IF(AND(F2<I2,F3>I3),"SELL","")).

Security Code	Date	Open	High	Low	Close	Adj Close	Volume	SMA10	SMA30	BUY/SELL SMA10
ABB	01-Feb-2019	€19.31	€19.36	€19.22	€19.24	€19.24	2,671,100	€19.22	€19.11	BUY
ABB	31-Jan-2019	€19.09	€19.17	€18.96	€19.15	€19.15	9,149,300	€19.21	€19.12	SELL
ABB	30-Jan-2019	€19.12	€19.39	€19.06	€19.36	€19.36	4,633,000	€19.21	€19.12	BUY
ABB	29-Jan-2019	€19.17	€19.25	€19.02	€19.02	€19.02	4,735,600	€19.20	€19.12
ABB	28-Jan-2019	€19.15	€19.23	€19.02	€19.21	€19.21	2,334,600	€19.24	€19.12	SELL
ABB	25-Jan-2019	€19.42	€19.57	€19.41	€19.49	€19.49	1,979,000	€19.26	€19.12	BUY

<colgroup><col><col><col span="8"><col></colgroup><tbody>
</tbody>

But these are just different algorithms which I'm trying to figure out empirically. That is why they need frequent tweaking. The logic is based on position of current price in relation to various types of indexes, and interrelationship between indexes. Unfortunately maths and logic are not sufficient to make BUY/SELL decisions, many things depend on shape of the linear indexes on the graphs. Conditions like "when price crosses SMA10 above or below" are not enough because there will be too many false signals. That is why all signals after selection must be validated by checking graphs of these stocks. The aim of tweaking is to reduce number of false signals. What you suggesting I'm already using in algorithm formulas. Those "rigid" logical conditions would be more applicable during reverse backtesting, when you want to test profitability of your trading strategy/algorithm based on historical price data, but I'm just making first steps in programming and it would be too complicated for me.
Thank for your willingness to help anyway.