Making things less repetitive

  • Thread starter Thread starter qiong
  • Start date Start date
Q

qiong

Hi

I am presently working on some data that requires a lot of repetiv
work (pasting, copying ...) and was wondering if what i am doing is th
best way or if a more efficient way is possible.

I have a worksheet 'Cumulative1' where Column A contains Firms' names
Columns B,C,D are the past 6-month cumulative stock returns of th
firms on months Jan 73, Feb 73, Mar 73.... such that each row contain
the time-series cumulative returns of a single firm.

I have another worksheet 'Raw1' quite like the first worksheet excep
that each row contains the time-series raw returns of a single firm.

What i want to do is:
1) Each month, sort the firms in ascending order according t
cumulative returns (from 'Cumulative1').
2) And then, based on the sort order each month, divide the list int
the top 30%, bottom 30% and intermediate 40% firms.
3) And after this division, I want to retrieve the raw returns (fro
'Raw1') of the firms within each of the 3 catergories, for the next 6
months.
4) The last step involves finding the average returns in each categor
each month.

What I am doing now is I manually cut and paste the cumulative return
(from 'Cumulative1') each month onto a separate worksheet and then us
Excel's sort function. And then use a vlookup function to retrieve th
raw returns (from 'Raw1') for the next 60 months. And then use th
autofilter function to find the top 30%, then take the average of th
values within this catergory; and the same for the bottom 30%.

This whole process is so repetitive and tiring. I need to do this 36
times (my sample period is from 1973 - 2003... 30 years... 360 months)

I was wondering if there was a more efficient way to do this, tha
eliminates the month by month cut and paste and so on...


Thanks in advance!
Ken
 
Keng,

Sounds like you have an extensive project on your hands.
One that would be difficult to answer in a single post.
I'm sure that your task can be automated (that's one thing
that macros are good for).

What I'd suggest is to turn on the macro recorder and then
step through your entire procedure. The resulting code will
surely have a lot of garbage that you don't need but it'll
give you some clues as to how to get started.
Your references in the resulting macro are going to be
hard coded (not what you want) but if you take it piece
by piece and ask questions here when you get stuck,
I'm sure that you can eventually get it working.

John
 
hi,

i have actually got some macros running.

but the main problem is I need them to continously work for more than
worksheet. Here's my macros (3 in all):

-----------------------------------------------------------
Sub insert_worksheet_n_zoom_smaller_1()
Sheets("Jan 73 to Dec 93").Select
Sheets.Add
ActiveWindow.Zoom = 85
End Sub
-----------------------------------------------------------


-----------------------------------------------------------
Sub Macro2()

' Copy list of firms and insert in Col A
Sheets("Cum2").Select
Columns("A:A").Select
Selection.copy
Sheets("Sep 83").Select
ActiveSheet.Paste

' copy cumulative returns and insert into Col B
Sheets("Cum1").Select
Columns("DU:DU").Select
Selection.copy
Sheets("Sep 83").Select
Range("B1").Select
ActiveSheet.Paste

' select and sort in ascending order
Range("A2:B2", Range("A2:B2").End(xlDown)).Select
Selection.Sort Key1:=Range("B3"), Order1:=xlAscending,
Header:=xlGuess, OrderCustom:=1, MatchCase:=False
Orientation:=xlTopToBottom

' paste dates
Sheets("Date").Select
Range("A1:BI2").Select
Selection.copy
Sheets("Sep 83").Select
Range("E1").Select
ActiveSheet.Paste

' insert vlookup formula into cell E3
Range("E3").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC1,'Jan 73 to Dec 93' R1C1:R1100C254,R2C,FALSE)"

' fill down and right the vlookup forumla
Range("E3:E1055").Select
Selection.FillDown
Range("E3:BM1055").Select
Selection.FillRight
End Sub
---------------------------------------------------------------------------
I need to change some of the absolute references to relative referenc
so that i can combine all 3 macros into a single macro. Not so sur
how to do it.

I have renamed the new sheet that i inserted in the the first macr
"Sep 83" and referred to it in subsequent coding.

I would like the macro to keep inserting sheets, and then rename the
Oct 83, Nov 83, Dec 83 and so on till Jun 03.

Also, for Columns ("DU:DU").Select, I would like this to change t
DV:DV for Oct 83, DW:DW for Dec 83, DX:DX for Jan 84 and so on. Th
same goes for Range("A1:BI2").Select. I would like this to change t
B1:BJ2 for Oct 83, B1:BK2 for Dec 83, C1:BL2 for Jan 84 and so on....


---------------------------------------------------------------------------
Sub Macro3()

' define winners, losers and the intermediate catergories
Range("C3").Select
ActiveCell.FormulaR1C1 = "=RANK(RC2,R3C2:R86C2)"
Range("C1").Select
ActiveCell.FormulaR1C1 = "=0.3*R[2]C"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=0.705*R[1]C"
Range("D3").Select
ActiveCell.FormulaR1C1 = _

"=IF(R3C3:R86C3>R2C3,""Losers"",IF(R3C3:R86C3<R1C3,""Winners"",""Intermediate""))"
Range("C3:D86").Select
Selection.FillDown

' calculate average of the returns in each 3 catergories
Range("E88").Select
Selection.FormulaArray
"=AVERAGE(IF(R3C4:R86C4=""Losers"",R[-85]C:R[-2]C))"
Range("E89").Select
Selection.FormulaArray = _
"=AVERAGE(IF(R3C4:R86C4=""Intermediate"",R[-86]C:R[-3]C))"
Range("E90").Select
Selection.FormulaArray
"=AVERAGE(IF(R3C4:R86C4=""Winners"",R[-87]C:R[-4]C))"
Range("E88:BM90").Select
Selection.FillRight
End Sub
--------------------------------------------------------------------------------

Before running this 3rd macro, I need to do something manually: that i
i need to delete all the rows that had #N/As and blank values in Colum
B. In this case, the resultant last row standing is Row 86. Hence, th
absolute reference in the code.

How could I get the macro to do this automatically for each sheet an
then refer to it in subsequent codes?

Sorrie for the long post. But I have been staring at this for quit
some time. It doesn't help that I dun know nuts about macros and tha
time is running short for me to learn all from scratch.

Many thanks in advance
Ken
 
Back
Top