Hv-Example
Sub HV() ’ ’ HV Macro ’ Macro recorded 5/29/2007 by Thomas Ott ’
’ Range(“G1”).Select ActiveCell.FormulaR1C1 = “LN” Columns(“G:G”).Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Range(“G3”).Select ActiveCell.FormulaR1C1 = “=LN(RC[-2]/R[-1]C[-2])” Range(“G3”).Select Selection.Copy ActiveWindow.ScrollRow = 8 ActiveWindow.ScrollRow = 23 ActiveWindow.ScrollRow = 55 ActiveWindow.ScrollRow = 99 ActiveWindow.ScrollRow = 151 ActiveWindow.ScrollRow = 205 ActiveWindow.ScrollRow = 264 ActiveWindow.ScrollRow = 318 ActiveWindow.ScrollRow = 373 ActiveWindow.ScrollRow = 417 ActiveWindow.ScrollRow = 458 ActiveWindow.ScrollRow = 494 ActiveWindow.ScrollRow = 524 ActiveWindow.ScrollRow = 542 ActiveWindow.ScrollRow = 553 ActiveWindow.ScrollRow = 561 ActiveWindow.ScrollRow = 565 ActiveWindow.ScrollRow = 567 ActiveWindow.ScrollRow = 570 ActiveWindow.ScrollRow = 573 ActiveWindow.ScrollRow = 575 ActiveWindow.ScrollRow = 578 ActiveWindow.ScrollRow = 581 ActiveWindow.ScrollRow = 584 ActiveWindow.ScrollRow = 587 ActiveWindow.ScrollRow = 588 ActiveWindow.ScrollRow = 589 ActiveWindow.ScrollRow = 588 ActiveWindow.ScrollRow = 587 ActiveWindow.ScrollRow = 581 ActiveWindow.ScrollRow = 571 ActiveWindow.ScrollRow = 557 ActiveWindow.ScrollRow = 545 ActiveWindow.ScrollRow = 531 ActiveWindow.ScrollRow = 522 ActiveWindow.ScrollRow = 515 ActiveWindow.ScrollRow = 510 ActiveWindow.ScrollRow = 506 ActiveWindow.ScrollRow = 503 ActiveWindow.ScrollRow = 500 ActiveWindow.ScrollRow = 497 ActiveWindow.ScrollRow = 493 ActiveWindow.ScrollRow = 487 ActiveWindow.ScrollRow = 483 ActiveWindow.ScrollRow = 478 ActiveWindow.ScrollRow = 473 ActiveWindow.ScrollRow = 468 ActiveWindow.ScrollRow = 463 ActiveWindow.ScrollRow = 458 ActiveWindow.ScrollRow = 454 ActiveWindow.ScrollRow = 451 ActiveWindow.ScrollRow = 449 ActiveWindow.ScrollRow = 445 ActiveWindow.ScrollRow = 442 ActiveWindow.ScrollRow = 439 ActiveWindow.ScrollRow = 436 ActiveWindow.ScrollRow = 434 ActiveWindow.ScrollRow = 431 ActiveWindow.ScrollRow = 428 ActiveWindow.ScrollRow = 425 ActiveWindow.ScrollRow = 422 ActiveWindow.ScrollRow = 419 ActiveWindow.ScrollRow = 416 ActiveWindow.ScrollRow = 412 ActiveWindow.ScrollRow = 407 ActiveWindow.ScrollRow = 403 ActiveWindow.ScrollRow = 399 ActiveWindow.ScrollRow = 394 ActiveWindow.ScrollRow = 390 ActiveWindow.ScrollRow = 387 ActiveWindow.ScrollRow = 384 ActiveWindow.ScrollRow = 381 ActiveWindow.ScrollRow = 379 ActiveWindow.ScrollRow = 376 ActiveWindow.ScrollRow = 373 ActiveWindow.ScrollRow = 371 ActiveWindow.ScrollRow = 368 ActiveWindow.ScrollRow = 365 ActiveWindow.ScrollRow = 363 ActiveWindow.ScrollRow = 361 ActiveWindow.ScrollRow = 358 ActiveWindow.ScrollRow = 355 ActiveWindow.ScrollRow = 354 ActiveWindow.ScrollRow = 352 ActiveWindow.ScrollRow = 350 ActiveWindow.ScrollRow = 348 ActiveWindow.ScrollRow = 346 ActiveWindow.ScrollRow = 343 ActiveWindow.ScrollRow = 340 ActiveWindow.ScrollRow = 336 ActiveWindow.ScrollRow = 333 ActiveWindow.ScrollRow = 330 ActiveWindow.ScrollRow = 327 ActiveWindow.ScrollRow = 324 ActiveWindow.ScrollRow = 322 ActiveWindow.ScrollRow = 320 ActiveWindow.ScrollRow = 319 ActiveWindow.ScrollRow = 316 ActiveWindow.ScrollRow = 313 ActiveWindow.ScrollRow = 311 ActiveWindow.ScrollRow = 309 ActiveWindow.ScrollRow = 305 ActiveWindow.ScrollRow = 302 ActiveWindow.ScrollRow = 297 ActiveWindow.ScrollRow = 293 ActiveWindow.ScrollRow = 287 ActiveWindow.ScrollRow = 283 ActiveWindow.ScrollRow = 278 ActiveWindow.ScrollRow = 274 ActiveWindow.ScrollRow = 271 ActiveWindow.ScrollRow = 267 ActiveWindow.ScrollRow = 264 ActiveWindow.ScrollRow = 260 ActiveWindow.ScrollRow = 258 ActiveWindow.ScrollRow = 254 ActiveWindow.ScrollRow = 250 ActiveWindow.ScrollRow = 246 ActiveWindow.ScrollRow = 242 ActiveWindow.ScrollRow = 240 ActiveWindow.ScrollRow = 237 ActiveWindow.ScrollRow = 236 ActiveWindow.ScrollRow = 237 ActiveWindow.ScrollRow = 239 ActiveWindow.ScrollRow = 241 ActiveWindow.ScrollRow = 242 ActiveWindow.ScrollRow = 243 Range(“G3:G276”).Select ActiveSheet.Paste Range(“H1”).Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = “HV 10” Range(“I1”).Select ActiveCell.FormulaR1C1 = “HV 20” Range(“J1”).Select ActiveCell.FormulaR1C1 = “HV 30” Columns(“H:J”).Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Range(“H1”).Select ActiveCell.FormulaR1C1 = “HV Daily” Range(“I1”).Select ActiveCell.FormulaR1C1 = “HV Weekly” Range(“J1”).Select ActiveCell.FormulaR1C1 = “HV Monthly” Columns(“H:J”).Select Columns(“H:J”).EntireColumn.AutoFit Windows(“gspc_vol_pred.xls”).Activate Windows(“HV example.xls”).Activate ActiveWindow.SmallScroll Down:=-6 Range(“H1”).Select ActiveCell.FormulaR1C1 = “HV 10” Range(“I1”).Select ActiveCell.FormulaR1C1 = “HV 20” Range(“J1”).Select ActiveCell.FormulaR1C1 = “HV 30” Range(“H12”).Select ActiveCell.FormulaR1C1 = “=STDEV(R[-9]C[-1]:RC[-1])SQRT(252)” Selection.Copy ActiveWindow.ScrollRow = 2 ActiveWindow.ScrollRow = 3 ActiveWindow.ScrollRow = 4 ActiveWindow.ScrollRow = 6 ActiveWindow.ScrollRow = 9 ActiveWindow.ScrollRow = 13 ActiveWindow.ScrollRow = 17 ActiveWindow.ScrollRow = 23 ActiveWindow.ScrollRow = 30 ActiveWindow.ScrollRow = 37 ActiveWindow.ScrollRow = 43 ActiveWindow.ScrollRow = 51 ActiveWindow.ScrollRow = 60 ActiveWindow.ScrollRow = 67 ActiveWindow.ScrollRow = 75 ActiveWindow.ScrollRow = 80 ActiveWindow.ScrollRow = 86 ActiveWindow.ScrollRow = 91 ActiveWindow.ScrollRow = 96 ActiveWindow.ScrollRow = 101 ActiveWindow.ScrollRow = 106 ActiveWindow.ScrollRow = 110 ActiveWindow.ScrollRow = 114 ActiveWindow.ScrollRow = 119 ActiveWindow.ScrollRow = 123 ActiveWindow.ScrollRow = 127 ActiveWindow.ScrollRow = 131 ActiveWindow.ScrollRow = 135 ActiveWindow.ScrollRow = 138 ActiveWindow.ScrollRow = 142 ActiveWindow.ScrollRow = 145 ActiveWindow.ScrollRow = 148 ActiveWindow.ScrollRow = 150 ActiveWindow.ScrollRow = 153 ActiveWindow.ScrollRow = 155 ActiveWindow.ScrollRow = 158 ActiveWindow.ScrollRow = 161 ActiveWindow.ScrollRow = 162 ActiveWindow.ScrollRow = 165 ActiveWindow.ScrollRow = 167 ActiveWindow.ScrollRow = 170 ActiveWindow.ScrollRow = 172 ActiveWindow.ScrollRow = 173 ActiveWindow.ScrollRow = 175 ActiveWindow.ScrollRow = 176 ActiveWindow.ScrollRow = 178 ActiveWindow.ScrollRow = 181 ActiveWindow.ScrollRow = 183 ActiveWindow.ScrollRow = 186 ActiveWindow.ScrollRow = 188 ActiveWindow.ScrollRow = 192 ActiveWindow.ScrollRow = 195 ActiveWindow.ScrollRow = 199 ActiveWindow.ScrollRow = 201 ActiveWindow.ScrollRow = 204 ActiveWindow.ScrollRow = 207 ActiveWindow.ScrollRow = 210 ActiveWindow.ScrollRow = 212 ActiveWindow.ScrollRow = 214 ActiveWindow.ScrollRow = 217 ActiveWindow.ScrollRow = 218 ActiveWindow.ScrollRow = 221 ActiveWindow.ScrollRow = 223 ActiveWindow.ScrollRow = 225 ActiveWindow.ScrollRow = 226 ActiveWindow.ScrollRow = 228 ActiveWindow.ScrollRow = 229 ActiveWindow.ScrollRow = 230 Range(”H12:H276”).Select ActiveSheet.Paste Range(”I22”).Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = ”=STDEV(R[-19]C[-2]:RC[-2])SQRT(252)” Range(“I22”).Select Selection.Copy ActiveWindow.ScrollRow = 2 ActiveWindow.ScrollRow = 5 ActiveWindow.ScrollRow = 10 ActiveWindow.ScrollRow = 17 ActiveWindow.ScrollRow = 27 ActiveWindow.ScrollRow = 39 ActiveWindow.ScrollRow = 51 ActiveWindow.ScrollRow = 64 ActiveWindow.ScrollRow = 77 ActiveWindow.ScrollRow = 88 ActiveWindow.ScrollRow = 98 ActiveWindow.ScrollRow = 104 ActiveWindow.ScrollRow = 109 ActiveWindow.ScrollRow = 113 ActiveWindow.ScrollRow = 117 ActiveWindow.ScrollRow = 120 ActiveWindow.ScrollRow = 124 ActiveWindow.ScrollRow = 126 ActiveWindow.ScrollRow = 129 ActiveWindow.ScrollRow = 132 ActiveWindow.ScrollRow = 136 ActiveWindow.ScrollRow = 139 ActiveWindow.ScrollRow = 143 ActiveWindow.ScrollRow = 147 ActiveWindow.ScrollRow = 150 ActiveWindow.ScrollRow = 153 ActiveWindow.ScrollRow = 156 ActiveWindow.ScrollRow = 159 ActiveWindow.ScrollRow = 162 ActiveWindow.ScrollRow = 164 ActiveWindow.ScrollRow = 165 ActiveWindow.ScrollRow = 167 ActiveWindow.ScrollRow = 169 ActiveWindow.ScrollRow = 171 ActiveWindow.ScrollRow = 174 ActiveWindow.ScrollRow = 176 ActiveWindow.ScrollRow = 178 ActiveWindow.ScrollRow = 179 ActiveWindow.ScrollRow = 181 ActiveWindow.ScrollRow = 183 ActiveWindow.ScrollRow = 185 ActiveWindow.ScrollRow = 186 ActiveWindow.ScrollRow = 187 ActiveWindow.ScrollRow = 188 ActiveWindow.ScrollRow = 189 ActiveWindow.ScrollRow = 191 ActiveWindow.ScrollRow = 192 ActiveWindow.ScrollRow = 193 ActiveWindow.ScrollRow = 195 ActiveWindow.ScrollRow = 197 ActiveWindow.ScrollRow = 198 ActiveWindow.ScrollRow = 199 ActiveWindow.ScrollRow = 200 ActiveWindow.ScrollRow = 201 ActiveWindow.ScrollRow = 202 ActiveWindow.ScrollRow = 203 ActiveWindow.ScrollRow = 204 ActiveWindow.ScrollRow = 205 ActiveWindow.ScrollRow = 206 ActiveWindow.ScrollRow = 207 ActiveWindow.ScrollRow = 208 ActiveWindow.ScrollRow = 209 ActiveWindow.ScrollRow = 211 ActiveWindow.ScrollRow = 212 ActiveWindow.ScrollRow = 213 ActiveWindow.ScrollRow = 214 ActiveWindow.ScrollRow = 216 ActiveWindow.ScrollRow = 217 ActiveWindow.ScrollRow = 218 ActiveWindow.ScrollRow = 219 ActiveWindow.ScrollRow = 220 ActiveWindow.ScrollRow = 222 ActiveWindow.ScrollRow = 223 ActiveWindow.ScrollRow = 225 ActiveWindow.ScrollRow = 226 ActiveWindow.ScrollRow = 227 ActiveWindow.ScrollRow = 228 ActiveWindow.ScrollRow = 229 ActiveWindow.ScrollRow = 230 Range(“I22:I276”).Select ActiveSheet.Paste Range(“J32”).Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = “=STDEV(R[-29]C[-3]:RC[-3])*SQRT(252)” Selection.Copy ActiveWindow.SmallScroll Down:=249 Range(“J32:J276”).Select ActiveSheet.Paste ActiveWindow.ScrollRow = 8 ActiveWindow.ScrollRow = 7 ActiveWindow.ScrollRow = 6 ActiveWindow.ScrollRow = 5 ActiveWindow.ScrollRow = 4 ActiveWindow.ScrollRow = 3 ActiveWindow.ScrollRow = 2 ActiveWindow.ScrollRow = 1 Range(“H1”).Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = “HV d10” Range(“I1”).Select ActiveCell.FormulaR1C1 = “HV d20” Range(“J1”).Select ActiveCell.FormulaR1C1 = “HV d30” Range(“K1”).Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With ActiveCell.FormulaR1C1 = “HV w10” Range(“H12”).Select Selection.Copy Range(“L12”).Select Application.CutCopyMode = False Range(“K1”).Select Selection.ClearContents Range(“M7”).Select End Sub