VBA Historical Price Data Loop

    I've been tearing my hair out over this one so help is much appreciated. I have an excel worksheet with the following data for an index of stocks. (This is an abbreviated version. The table is 60000 lines long, hence the need for a looping code).
    Column one contains the stock components of an index.
    Column two contains forward looking fiscal years
    Column three contains Earnings Estimates by a variety of analysts(this column is hidden as it's not required). So for stock1 there are 3 analysts covering it, each with a different forecast of figures per year. I want to tally the means of these forecast, per year for each stock.


    [col1]--[col2]-----[col3]------------[col4]
    stock1 Year 1 Earnings figures [average(earnings-yr1-stk1)
    stock1 Year 1 Earnings figures
    stock1 Year 1 Earnings figures
    stock1 Year 2 Earnings figures [average(earnings-yr2-stk1)
    stock1 Year 2 Earnings figures
    stock1 Year 2 Earnings figures
    stock1 Year 2 Earnings figures
    stock2 Year 1 Earnings figures [average(earnings-yr1-stk2)
    stock2 Year 1 Earnings figures
    stock2 Year 1 Earnings figures
    stock2 Year 2 Earnings figures
    stock2 Year 2 Earnings figures
    stock2 Year 2 Earnings figures
    stock3 Year 1 Earnings figures
    etc.


    What I want to do is the following:
    A code that loops through the stocks and for each range of earnings figures per year, calculates the average (mean) of this range and dumps the result in the fourth column next to the first line (row) of that range.


    If the solution is easy, I can't see it. I've tried by looping through the 2nd column and adding to a dynamic array all the figures until the value in col2 changes. This seems convoluted and doesn't seem to work anyway.


    Many thanks


    JP

    Re: VBA Historical Price Data Loop


    Hi JP,


    If you're willing to settle for a slightly different reporting format, then here's a suggestion that doesn't require VBA:


    1. In column (D), you could simply place an analyst code (or generate dummy entries via formulas- see attached workbook).


    2. Run up a Pivot table, with Security Names and Years as row fields, and Analyst codes as column fields


    3. Forecast EPS should be dropped as data items (with Average i/o Sum function)


    See attached workbook for details.


    HTH


    m

    Re: VBA Historical Price Data Loop


    M,


    Thanks for your quick reply.


    While the table does produce the results, what I ultimately want to do is to filter the fourth column to exclude non-blanks and then copy the information into a model I've built, so that Each company will have an average figure next to it. Using VBA I'd then also be able to apply the code to include standard deviation in col5 or any other statistical measure that may be useful.


    I've attached a portion of the worksheet if it helps.


    thx


    JP

    Re: VBA Historical Price Data Loop


    JP,


    Sorry to let you down, I normally use VBA as a last resort since Excel has so many powerful features built into its immediate environment - plus VBA is really not my strong point.


    So attached is yet another formula-based solution. I've used the OFFSET/SUMPRODUCT combination to identify the correct computational ranges - with a couple of dynamic ranges thrown in for improved formula readability.


    I'm hopeful that this will satisfy your needs vis-a-vis AVG, STDEV, auto-filter etc. etc. But this is really the edge of my creativity.


    HTH


    m

    Re: VBA Historical Price Data Loop


    m,


    Many thx for your help. You've got the excel programming down to a tee. However, to be a right pain, I would really like to get a VBA code to do these, as it's unfortunately something that I may have to repeat on a weekly basis with several worksheet of similar sizes. I think for now it'll do the trick, but I will somehow have to find a VBA solution in the near future.


    Once again, thanks for taking the time.


    Kind regards,


    JP

    Re: VBA Historical Price Data Loop


    M,


    That's fantastic.


    I will have to tinker with it a bit as it keeps breaking in the following line despite having set up my sheet exactly like yours.


    [Cells(ProcessRow, RefColumn + 1).Value = _
    Application.WorksheetFunction.StDev(DataRange)]


    I've discovered that with another parameter one of the stocks only has a single broker covering it per year, and as such the stdev breaks. I'm pretty sure I can add the extra code to deal with it. If any problems arise, I'll let you know.


    Help has been much appreciated.


    Kind regards,


    Johan

    Re: VBA Historical Price Data Loop


    Hi Johan,


    Just add the following code right after the sub statement (or anywyere near the top of the procedure).


    Code
    On Error Resume Next


    This will omit STDEV computations for one-liner securities.


    HTH


    m

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login