D
daniroy
As usual, I really want to thank everyone who did help on my VB code. I
today come with new problems that did arise while developing and I have
a few brand new questions unfortunately ...
I need to create a matrix of prices on Sheets("Matrix"). In other
words, I want to input a Price at the intersection of a product
(products are on lines) for a given month (months are in columns). It
will be more or less like a Pivot Table at the exception I will
interpolate the missing data for a given month.
References of the products are thus sorted on lines, dates on columns
of the Matrix Sheet.
Thanks to Bernie Deitrick I now create automatically the Product
Reference list as well as the month series.
The prices I want to retrieve are up-to-now displayed on the Sheet
called "FT" and here are how they are setup.
Column E Column F Column I Column AA
Date Products C/P Price
18 Nov 05 38 C 24
18 Nov 05 38 P 25
18 Nov 05 39 C 22
18 Nov 05 39 P
18 Nov 05 40 C
18 Nov 05 40 P 18
And so on, months after months
For the time being, my code is:
__________________________________________________________
Sub Setup_Vol_Points()
Dim Vol_Call As String
Dim Vol_Put As String
Dim Vol_Strike As Single
Sheets("Matrix").Select
Vol_Call =
"INDEX(FT!$AA$10:$AA$500,MATCH(1,(FT!$F$10:$F$500='Matrix'!C09)*(FT!$G$10:$G$400='Matrix'!B42)*(FT!$I$10:$I$400=""Call""),0))"
Vol_Put =
"INDEX(FT!$AA$10:$AA$500,MATCH(1,(FT!$F$10:$F$500='Matrix'!C09)*(FT!$G$10:$G$400='Matrix'!B42)*(FT!$I$10:$I$400=""Put""),0))"
If Application.Evaluate(Vol_Call) = "" And
Application.Evaluate(Vol_Put) = "" Then Vol_Strike =
Application.Evaluate(Vol_Call) + Application.Evaluate(Vol_Put)
If Application.Evaluate(Vol_Call) <> "" And
Application.Evaluate(Vol_Put) = "" Then Vol_Strike =
Application.Evaluate(Vol_Call)
If Application.Evaluate(Vol_Call) = "" And
Application.Evaluate(Vol_Put) <> "" Then Vol_Strike =
Application.Evaluate(Vol_Put)
If Application.Evaluate(Vol_Call) <> "" And
Application.Evaluate(Vol_Put) <> "" Then Vol_Strike =
Application.Evaluate(Vol_Call) / 2 + Application.Evaluate(Vol_Put) / 2
Sheets("Matrix").Range("C30").Value = Application.Evaluate(Vol_Call)
Sheets("Matrix").Range("C31").Value = Application.Evaluate(Vol_Put)
Sheets("Matrix").Range("C32").Value = Application.Evaluate(Vol_Strike)
End Sub
__________________________________________________________
I need to give more information about what I am trying to achieve.
If for the same product ("39" for instance) I have both a line for
C or for P (2 sub products) I want to average the price of both. Here
is why I have the code
If Application.Evaluate(Vol_Call) <> "" And
Application.Evaluate(Vol_Put) <> "" Then Vol_Strike =
Application.Evaluate(Vol_Call) / 2 + Application.Evaluate(Vol_Put) / 2
If I only have one Price for P, I only want this one as my reference
price. Same thing for C. And if I do not have any, I will later have to
interpolate it.
As you can also see, I used the product in the Range("B42") and the
month Range("C09") in that example. It woks just fine except I am
inputing these data in cells C30, C31, C32 at the moment rather than
C42.
Anyway here is the time I cannot advance with my VB knowledge.
First of all, I need to retrieve my input value for 39 different
products from line 10 to 48, moreover on six different months. Thus I
did first try to modify my code to infer
For i = 10 to 38
And modifying Vol_Call and Vol_Put, but without success.
Volatility_Call =
"INDEX(FTSE!$AA$10:$AA$500,MATCH(1,(FTSE!$F$10:$F$500='Matrix'!C09)*(FTSE!$G$10:$G$400=Sheets(""Matrix"").Range(""B""&i).Value)*(FTSE!$I$10:$I$400=""Call""),0))"
Volatility_Put =
"INDEX(FTSE!$AA$10:$AA$500,MATCH(1,(FTSE!$F$10:$F$500='Matrix'!C09)*(FTSE!$G$10:$G$400=Sheets(""Matrix"").Range(""B""&i).Value))*(FTSE!$I$10:$I$400=""Put""),0))"
But it does not work. Neither does the initial code works if there is
no data for the according products on the "FT" Sheet. I do not know
if I am clear enough ...
To resume my points I am now trying to extend the initial code for
every i from 10 to 48, or whatever value, and this code has to be able
to work if it does not find any data to work on in the FT Sheet. May be
a specific code should be add at the very beginning about that ?
Anyway any help on any of these points will be more than appreciated,
be sure of it ! and obviously I am ready to give more explanations if a
few things are far away to be clear!
Best regards
Daniel
today come with new problems that did arise while developing and I have
a few brand new questions unfortunately ...
I need to create a matrix of prices on Sheets("Matrix"). In other
words, I want to input a Price at the intersection of a product
(products are on lines) for a given month (months are in columns). It
will be more or less like a Pivot Table at the exception I will
interpolate the missing data for a given month.
References of the products are thus sorted on lines, dates on columns
of the Matrix Sheet.
Thanks to Bernie Deitrick I now create automatically the Product
Reference list as well as the month series.
The prices I want to retrieve are up-to-now displayed on the Sheet
called "FT" and here are how they are setup.
Column E Column F Column I Column AA
Date Products C/P Price
18 Nov 05 38 C 24
18 Nov 05 38 P 25
18 Nov 05 39 C 22
18 Nov 05 39 P
18 Nov 05 40 C
18 Nov 05 40 P 18
And so on, months after months
For the time being, my code is:
__________________________________________________________
Sub Setup_Vol_Points()
Dim Vol_Call As String
Dim Vol_Put As String
Dim Vol_Strike As Single
Sheets("Matrix").Select
Vol_Call =
"INDEX(FT!$AA$10:$AA$500,MATCH(1,(FT!$F$10:$F$500='Matrix'!C09)*(FT!$G$10:$G$400='Matrix'!B42)*(FT!$I$10:$I$400=""Call""),0))"
Vol_Put =
"INDEX(FT!$AA$10:$AA$500,MATCH(1,(FT!$F$10:$F$500='Matrix'!C09)*(FT!$G$10:$G$400='Matrix'!B42)*(FT!$I$10:$I$400=""Put""),0))"
If Application.Evaluate(Vol_Call) = "" And
Application.Evaluate(Vol_Put) = "" Then Vol_Strike =
Application.Evaluate(Vol_Call) + Application.Evaluate(Vol_Put)
If Application.Evaluate(Vol_Call) <> "" And
Application.Evaluate(Vol_Put) = "" Then Vol_Strike =
Application.Evaluate(Vol_Call)
If Application.Evaluate(Vol_Call) = "" And
Application.Evaluate(Vol_Put) <> "" Then Vol_Strike =
Application.Evaluate(Vol_Put)
If Application.Evaluate(Vol_Call) <> "" And
Application.Evaluate(Vol_Put) <> "" Then Vol_Strike =
Application.Evaluate(Vol_Call) / 2 + Application.Evaluate(Vol_Put) / 2
Sheets("Matrix").Range("C30").Value = Application.Evaluate(Vol_Call)
Sheets("Matrix").Range("C31").Value = Application.Evaluate(Vol_Put)
Sheets("Matrix").Range("C32").Value = Application.Evaluate(Vol_Strike)
End Sub
__________________________________________________________
I need to give more information about what I am trying to achieve.
If for the same product ("39" for instance) I have both a line for
C or for P (2 sub products) I want to average the price of both. Here
is why I have the code
If Application.Evaluate(Vol_Call) <> "" And
Application.Evaluate(Vol_Put) <> "" Then Vol_Strike =
Application.Evaluate(Vol_Call) / 2 + Application.Evaluate(Vol_Put) / 2
If I only have one Price for P, I only want this one as my reference
price. Same thing for C. And if I do not have any, I will later have to
interpolate it.
As you can also see, I used the product in the Range("B42") and the
month Range("C09") in that example. It woks just fine except I am
inputing these data in cells C30, C31, C32 at the moment rather than
C42.
Anyway here is the time I cannot advance with my VB knowledge.
First of all, I need to retrieve my input value for 39 different
products from line 10 to 48, moreover on six different months. Thus I
did first try to modify my code to infer
For i = 10 to 38
And modifying Vol_Call and Vol_Put, but without success.
Volatility_Call =
"INDEX(FTSE!$AA$10:$AA$500,MATCH(1,(FTSE!$F$10:$F$500='Matrix'!C09)*(FTSE!$G$10:$G$400=Sheets(""Matrix"").Range(""B""&i).Value)*(FTSE!$I$10:$I$400=""Call""),0))"
Volatility_Put =
"INDEX(FTSE!$AA$10:$AA$500,MATCH(1,(FTSE!$F$10:$F$500='Matrix'!C09)*(FTSE!$G$10:$G$400=Sheets(""Matrix"").Range(""B""&i).Value))*(FTSE!$I$10:$I$400=""Put""),0))"
But it does not work. Neither does the initial code works if there is
no data for the according products on the "FT" Sheet. I do not know
if I am clear enough ...
To resume my points I am now trying to extend the initial code for
every i from 10 to 48, or whatever value, and this code has to be able
to work if it does not find any data to work on in the FT Sheet. May be
a specific code should be add at the very beginning about that ?
Anyway any help on any of these points will be more than appreciated,
be sure of it ! and obviously I am ready to give more explanations if a
few things are far away to be clear!
Best regards
Daniel