+ Reply to Thread
Results 1 to 1 of 1

VBA Calculate average of stocks

  1. #1
    Registered User
    Join Date
    Leiden, Netherlands
    MS-Off Ver

    VBA Calculate average of stocks

    Dear reader,

    I need to calculate the average return of 600 stocks over a period of 3 months, i need to repeat this numerous times and pick the stocks that have the best 10% return. So for every three months pick the best 10% of stocks, and repeat this 20 times which creates 20 portfolios. I was looking for a VBA code that can help me do this.

    As a test I created a new excel file with a small amount of numbers, and the only thing I am able to do is calculate the average of 1 stocks for 2 months...

    Sub average()
    For i = 1 To 1
    av = (Range("a1") + Range("a2")) / 2
    Range("a6:h8").Offset(i) = av
    End Sub

    I find it difficult to use VBA where it gives the solution on the excel sheet, and how to make this a code which you can use multiple times (moving average). I can only use the cells I named and I can't let it roll down..
    I am not sure how to use a loop here...
    Please let me know if anyone understands this or if you need more information.

  2. #2
    Forum Expert
    Join Date
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010

    Re: VBA Calculate average of stocks

    Can you attach a copy of your spreadsheet explaining what it looks like at the beginning and add a sheet which shows the result? To attach a file, click on Go Advance scroll to the Manage Attachments button, browse to the required file, and then push the Upload button.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How do i calculate this stocks trading excel?
    By nederay in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-04-2013, 03:25 AM
  2. Calculate average
    By Ramesh Velu in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-28-2013, 07:58 AM
  3. Calculate Average
    By umayank in forum Excel General
    Replies: 1
    Last Post: 07-17-2012, 02:07 AM
  4. Replies: 2
    Last Post: 06-24-2011, 05:12 AM
  5. Calculate average withouot incorporating sub average
    By ybu1106 in forum Excel General
    Replies: 25
    Last Post: 06-15-2010, 09:47 AM

Tags for this Thread


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1