Posts by jpalmberg

Re: Speeding up my loop

Guys,

Many thanks for your quick replies. It was partly the auto calculation but also the fact that I was running an autochart updating on the selection_change event (oops - forgot to mention). I've managed to get it down to a few shuddering milliseconds turning calc to manual and disabling the selection change while executing the median-calculation sub.

anywho...

Great forum!

Thanks again

I cannot make this run any quicker. The data concerned covers a filtered range 56 columns wide and between 3 and 50 rows long. However it seems to be the number of columns that create the lag and it takes just under 3 seconds to complete. Any ideas?

application.screenupdating = false
Set medianrange = Range(Cells(7, 3), Cells(LastRow, LastCol)) ' last row and last column set
On Error Resume Next    'The column may have no data
For Each col In medianrange.Columns
Cells(4, col.Column) = Application.WorksheetFunction.Median(col.SpecialCells(xlCellTypeVisible))
Next
Application.ScreenUpdating = True

Thanks in advance

JP

I'm stuck and would appreciate some help with the following:

I have a workbook with a Financial Model in Sheet 1 which contains Data for all the stocks in an index grouped by sector.

Each sector is headed by a row containing, in ("A(x))", a toggle button to expand/collapse that sector and show the constituent stocks. The sector row-positions will change from time to time as the index is recalculated.

All the information is determined when the data is loaded (on a weekly basis) into sheets 2 - 20 (one for each sector). A procedure calculates the dimensions of the sectors and performs a number of calculations before dumping the information into the model in sheet 1 under the relevant sector rows.

At this stage, the toggle-button subs have the size-variables stored as the sub that determined them has just been run. However, when the worksheet is closed and subsequently reopened, the variables are no longer defined. My crude solution at the moment is to declare these variables by hand as constants in the worksheet ("MAIN").

However, to fully automate the process, how can I ensure that the the variables calculated during the initial data input are stored and available in Sheet1 at all times. All the calculations in the Model are based on these variables and its crucial that they are automated to avoid any human errors.

Apologies if it's unclear. I have included a sample to illustrate the problem.

One solution was to load the variables calculated initially into an area of redundant cells in the worksheet, which can be accessed by the toggle-button subs at any time. I would however prefer to use Public variables, constants or something similar.

Help is much appreciated.

JP

Re: VBA Historical Price Data Loop

m,

That's it, it's done.

I'm very grateful for your help.

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

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,

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

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