Interest rates in VBA

[beni12]

Hi dear VBA/ Excel community,

I`m writing a seminar in "Financial Modelling" and I definitely have problems at the moment with modelling my data. I`ve a data for stock prices for the whole month of January (around 1 048 567). Next to each stock price is the day of the month (so I`ve got 2 collumns for that). I`ve to match each one of these stock prices with the particular interest rate for the day (e.g., 15th January - 2.2).
For the interst rate I also have two collumns- one with the day and one with the interst rate. The whole data is in a one worksheet but in two tables. I`m not sure if they`ve to be in a separate worksheets and if I described that correctly in the VBA model. I think that`the part where I did everything wrong.
It would be great if you could help because I`m not so experienced with VBA. Thanks a lot in advance to all of you guys!!

Here`s my try to formulate the VBA code for the problem.....feel free to correct the mistakes you see in it :-)

Please Login or Register  to view this content.

Best regards,
Beni

[watersev]

hi, beni12, the code looks ok except:

1. Screenupdaing feature is not used - it will increase the speed
2. Not the best solution in terms of performance

If you had posted a sample workbook (100 rows of stocks and a whole table of interest rates will do) we could look at it in detail.

[royUK]

I don't think screen updating will save much time, more important:

Don't Activate sheets

Limit the loop to the last used row, you currently loop through the whole column here

Please Login or Register  to view this content.

[beni12]

Thanks for the help so far

I attached a woorksheet with a bit of data so that you can have a closer look at what I was asking you before =). There`s the data with the stock prices in the first table and in the second is the interest rate with the corresponding date.

I would appreciate it if you`ve some comments on it so that I could write correctly the VBA code

Greetz!
Beni

[watersev]

hi, beni12, I know you want a VBA solution but still will start with simple formula, check attachment.

[beni12]

Yepp, thanks watersev!

Actually I thought that I can solve that only with VBA, it`s great that you`ve found a simpler solution
It would be great if you write me what you`ve exactly done....I saw that you use a formula with INDEX which contains a matrix and afterwards a submatrix. Once more many many thanks!!