VBA code to calculate monthly variance

[Sumaira]

I have a daily data of stock returns of18 years in one sheet. For example my data starts with 7/1/2010 and ends at 6/30/2020. I have to calculate monthly variance of this data Means variance of june 2010 by using all observations of June 2010 then variance of august 2010 and so on till june 2020. can someone help in making a VBA code for this

 

[fadee2]

Salam and welcome to the board,

I would suggest uploading your sample data using the xl2bb addon, that way it will be much easier to understand and suggest solutions.

 

[Sumaira]

Thank you so much for responding. i would greatly appreciate your help.

book.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
3		7/1/2010	7/2/2010	7/5/2010	7/6/2010	7/7/2010	7/8/2010	7/9/2010	7/12/2010	7/13/2010	7/14/2010	7/15/2010	7/16/2010	7/19/2010	7/20/2010	7/21/2010	7/22/2010	7/23/2010	7/26/2010	7/27/2010	7/28/2010	7/29/2010	7/30/2010	8/2/2010	8/3/2010	8/4/2010	8/5/2010	8/6/2010	8/9/2010	8/10/2010	8/11/2010	8/12/2010
4	NESTLE PAKISTAN - TOT RETURN IND	0.01	0.00	-0.04	-0.04	0.05	0.04	0.01	-0.01	0.02	0.02	-0.01	-0.02	0.03	0.00	-0.01	-0.01	0.02	0.00	0.00	-0.01	0.00	0.00	-0.01	0.01	-0.01	0.00	-0.01	0.04	-0.04	0.01	-0.03
5	OIL & GAS DEVELOPMENT COMPANY - TOT RETURN IND	0.00	0.00	-0.01	-0.02	0.02	0.00	0.02	0.01	0.03	-0.01	-0.01	0.01	0.00	0.00	0.00	0.00	0.02	0.01	0.00	0.00	0.00	0.00	-0.02	0.00	0.00	0.01	0.00	-0.03	-0.03	-0.01	-0.02
6	PAKISTAN TOBACCO COMPANY - TOT RETURN IND	0.00	-0.02	0.01	0.05	0.00	-0.03	0.00	0.00	0.00	0.03	-0.03	0.03	0.01	-0.03	0.00	0.03	-0.02	0.01	0.00	0.00	0.01	0.02	-0.04	0.00	0.00	0.03	-0.02	0.02	0.02	-0.04	0.00
7	ABBOTT LABS.(PAK.) - TOT RETURN IND	-0.01	0.00	-0.04	0.00	0.03	0.00	0.01	0.00	0.01	0.03	0.00	0.00	0.01	-0.01	0.02	-0.03	0.01	-0.01	0.01	0.01	0.00	0.01	-0.01	0.00	0.01	0.01	0.00	-0.03	0.00	0.00	0.00
8	ABDULLAH SHAH GAZI SUGAR MILLS - TOT RETURN IND	0.00	-0.22	-0.14	0.00	-0.27	0.00	0.00	1.24	0.00	-0.15	0.00	-0.29	-0.39	-0.15	0.89	0.21	-0.37	0.00	0.38	0.44	-0.29	0.43	0.30	0.23	0.13	-0.02	-0.17	0.10	-0.13	0.05	-0.05
9	ABSON INDUSTRIES SUSP - SUSP.28/01/16 - TOT RETURN IND	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
10	ADAM SUGAR MILLS - TOT RETURN IND	0.00	0.07	0.00	0.00	0.00	0.00	0.07	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.03	-0.02	0.00	0.00	0.00	0.00	0.00	0.00	-0.07	0.00	0.08	0.00	0.00	0.00	-0.05	0.00
11	ADOS PAKISTAN - TOT RETURN IND	0.00	0.04	0.01	0.03	0.00	-0.05	0.00	0.03	0.01	0.03	0.00	0.03	-0.05	0.04	-0.04	0.04	0.00	-0.05	0.00	0.04	0.01	0.01	-0.04	0.01	-0.02	0.00	0.00	-0.02	0.00	0.00	0.03
12	#ERROR	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
13	AGRIAUTO INDUSTRIES - TOT RETURN IND	0.00	0.00	0.00	-0.05	0.01	0.00	0.01	0.05	0.02	0.01	-0.03	0.04	0.02	-0.02	0.01	0.01	0.00	0.00	-0.01	-0.03	0.03	0.02	-0.03	0.02	-0.02	0.00	-0.01	-0.03	0.00	0.00	-0.01
14	#ERROR	-0.05	0.05	-0.04	0.04	0.03	0.01	-0.01	0.03	0.00	-0.01	0.01	0.01	0.00	0.01	-0.01	0.00	-0.02	0.01	-0.03	0.02	-0.03	0.00	0.00	-0.01	0.01	-0.03	0.00	-0.04	-0.02	0.02	-0.02
Sheet1

Cell Formulas
Range	Formula
D4:AH14	D4	=IFERROR([PAKISTAN.xlsx]Sheet2!CBL4-D$2,"NaN")

 

[fadee2]

a question, what do you mean by ??

variance of june 2010 by using all observations of June 2010 then variance of august 2010 and so on till june

does this mean you need MEAN for each month? if so, where do you need this variance, i mean in this same worksheet or another worksheet?

 

[Sumaira]

I need to compute variance in another worksheet.

 

[Sumaira]

Basically I want to ask if there is a way to put a loop command. Like I have data of 120 months how does a single command be executed to all these months one by one.

 

[fadee2]

hi,
Create a results sheet (sheet 3) with similar structure to your data sheet (sheet 2), similar to below sample:

Book1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		2010
2		January	February	March	April	May	June	July	August	September	October	November	December
3	NESTLE PAKISTAN - TOT RETURN IND
4	OIL & GAS DEVELOPMENT COMPANY - TOT RETURN IND
5	PAKISTAN TOBACCO COMPANY - TOT RETURN IND
6	ABBOTT LABS.(PAK.) - TOT RETURN IND
7	ABDULLAH SHAH GAZI SUGAR MILLS - TOT RETURN IND
8	ABSON INDUSTRIES SUSP - SUSP.28/01/16 - TOT RETURN IND
9	ADAM SUGAR MILLS - TOT RETURN IND
10	ADOS PAKISTAN - TOT RETURN IND
11	#ERROR
12	AGRIAUTO INDUSTRIES - TOT RETURN IND
13	#ERROR
Sheet3

the year you are looking for goes in Cell B2 (2010,2011,2012,..... etc).

Copy and paste the code below in a module and bind it to a button on your results sheet.

Private Sub sumss()
Dim srng As Range
Dim crng As Range
Dim cmonth As String

lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells(2, Columns.Count).End(xlToLeft).Column

With Sheets("sheet3")

    For j = 3 To lr
        For i = 2 To lc
            Set srng = Sheets("sheet2").Range("b" & j, "af" & j)
            Set crng = Sheets("sheet2").Range("b2:af2")
            cmonth = DateValue("01/" & Cells(2, i).Value & "/" & Cells(1, 2).Value)
On Error Resume Next
            Cells(j, i) = Application.WorksheetFunction.AverageIfs(srng, crng, ">=" & cmonth, _
                                crng, "<=" & Application.WorksheetFunction.EoMonth(cmonth, 0))
            Cells(j, i).NumberFormat = "#,###0.00000"
        Next i
    Next j

End With
End Sub

Basically the structure of both your sheets will remain the same for the code to work properly.

hope this helps.

P.S. you can choose to change sheet names and ranges to suit your needs.