VBA String Manipulation Help

[lettuceplay]

Hello,

I need help with manipulating strings in VBA. For example I would like to convert:

CALL GOOG:US 12AUG2011 560 into GOOG1112H565-US
CALL C:US 21JAN2012 40 into C1221A40-US
PUT ABX:CA 21JAN2014 30 into ABX1421M30-T

Format would be.. ie first example..: SYMBOL (GOOG) YEAR (11) DAY (12) CALL/PUT MONTH (A) STRIKE (560)

The months are setup as A = January Call, B = February Call, C = March Call... L = December Call, M = January Put, N February Put, etc...

and have the two formats shown side by side.

Would anyone be able to lead me in the right direction?

 

[Jerry Sullivan]

Welcome to the Board!

You can use the function below to convert the Input string to the format you showed.

It wasn't clear from your example what happens to the CALL/PUT part when the input string is a PUT.
Does that become PUT/CALL ? If that is the case, you could use an If...Then statement to insert that part.

Sub TEST()
    MsgBox Convert("CALL GOOG:US 12AUG2011 560 into GOOG1112H565-US")
End Sub

Function Convert(strIN) As String
    Dim strArray() As String
    Dim strSymbol As String, strDate As String
    strArray = Split(strIN, " ")
    strSymbol = Split(strArray(1), ":")(0)
    strDate = strArray(2)
    
    Convert = "SYMBOL (" & strSymbol & ") YEAR (" _
        & Right(strDate, 2) & ") DAY (" & Left(strDate, 2) & ") " _
        & "CALL/PUT MONTH (" & Mid(strDate, 3, 1) & ") STRIKE (" _
        & strArray(3) & ")"
End Function

 

[lettuceplay]

Thank you for such a quick response!

To clarify the use of this, I get a report with desciptions as CALL GOOG:US 12AUG2011 and would like it to read "GOOG1112H565-US" so that it is compatible with another program:

GOOG (Option Symbol) 11 (Year) 12 (Day) H (Call Expiring August) 565 (Strike Price) -US (U.S. Option)

The Call/Put months are broken down as:

A = Jan Call M = Jan Put
B = Feb Call N = Feb Put
C = Mar Call O = Mar Put
D = Apr Call P = Apr Put
. .
. .
K = Nov Call Q = Nov Put
L = Dec Call R = Dec Put

So if the description reads "PUT AAPL:US 21APR2012 410" - a put option for Apple expiring on 21 April 2012 with a strike of 410 then the corresponding outcome would be "AAPL1221P410-US".

 

[lettuceplay]

I've made the following for the above problem about months:
If Mid(strDate, 3, 3) = "JAN" And strArray(0) = "CALL" Then
strMonth = "A"
Else
If Mid(strDate, 3, 3) = "FEB" And strArray(0) = "CALL" Then
strMonth = "B"
Else
If Mid(strDate, 3, 3) = "MAR" And strArray(0) = "CALL" Then
strMonth = "C"
Else
If Mid(strDate, 3, 3) = "APR" And strArray(0) = "CALL" Then
strMonth = "D"
Else
If Mid(strDate, 3, 3) = "MAY" And strArray(0) = "CALL" Then
strMonth = "E"
Else
If Mid(strDate, 3, 3) = "JUN" And strArray(0) = "CALL" Then
strMonth = "F"
Else
.
.
.
If Mid(strDate, 3, 3) = "OCT" And strArray(0) = "PUT" Then
strMonth = "V"
Else
If Mid(strDate, 3, 3) = "NOV" And strArray(0) = "PUT" Then
strMonth = "W"
Else
If Mid(strDate, 3, 3) = "DEC" And strArray(0) = "PUT" Then
strMonth = "X"
***

But are there any suggestions to make the code more efficient? There are 24 If Statements for Calls from January to Dec and Puts from January to Dec

 

[Jerry Sullivan]

Wow, I really misread your first post.

Here is a general purpose function that could be used to convert the month code.

Sub TEST2()
    MsgBox GetOptionCode(DateValue("4-Apr-2000"), "CALL")
End Sub


Function GetOptionCode(dtDate As Date, strType As String) As String
    If Left(strType, 1) = "P" Then
         GetOptionCode = Chr(Month(dtDate) + 64)
    Else
         GetOptionCode = Chr(Month(dtDate) + 64 + 12)
    End If
End Function

I'll post the other parts of the convert shortly.

 

[Jerry Sullivan]

Here is the complete code. Note that in the previous post I had PUT and CALL reversed in GetOptionCode.

Sub TEST3()
   MsgBox Convert("PUT AAPL:US 21APR2012 410")
End Sub

Function Convert(strIN) As String
    Dim strArray() As String, strDate As String
    Dim strSymbol As String, strCountry As String, strPutMonth As String
    strArray = Split(strIN, " ")
    strSymbol = Split(strArray(1), ":")(0)
    strCountry = Split(strArray(1), ":")(1)
    strDate = strArray(2)
    
    strPutMonth = GetOptionCode( _
        dtDate:=DateValue("1-" & Mid(strDate, 3, 3) & "-2000"), _
        strType:=strArray(0))
    Convert = strSymbol & Right(strDate, 2) & Left(strDate, 2) _
        & strPutMonth & strArray(3) & "-" & strCountry
End Function

Function GetOptionCode(dtDate As Date, strType As String) As String
    If Left(strType, 1) = "C" Then
         GetOptionCode = Chr(Month(dtDate) + 64)
    Else
         GetOptionCode = Chr(Month(dtDate) + 64 + 12)
    End If
End Function

 

[lettuceplay]

Wow that is amazing! Thanks for all of your help!

I'm a bit lost at how strPutMonth and GetOptionCode is able to find the appropriate letter depending on the month and if it's a call/put??

Is there anywhere I can read up more on that?

Thanks once again, it works great!

 

[Rick Rothstein]

Unfortunately, some of your examples appear "messed up"; however, I think I was able to piece together what you wanted. Give this function (which can be used as a UDF... user defined function) a try...

Function PutCall(PC As String) As String
  Dim Dte As Date, Parts() As String
  Parts = Split(Replace(PC, ":", " "))
  Dte = CDate(Format(Parts(3), "&&-&&&-&&&&"))
  PutCall = Parts(1) & Format(Dte, "yydd") & Chr(Month(Dte) + 64 - 12 * (Parts(0) = "PUT")) & Parts(4) & "-" & Parts(2)
End Function

Here is a sample of its use as a function called from within VB...

Sub TEST()
   MsgBox PutCall("PUT AAPL:US 21APR2012 410")
End Sub

On the other hand, if you had this...

PUT AAPL:US 21APR2012 410

in a cell, say A1, then you could use this formula directly on your worksheet...

=PutCall(A1)

 

[Jerry Sullivan]

Rick, Thank you for showing the more efficient code. I learned 3 things from your example.

 

[Rick Rothstein]

Rick, Thank you for showing the more efficient code. I learned 3 things from your example.

But I only posted three lines of active code. All kidding aside, you are quite welcome and I am glad that you found some useful techniques to add to your programming arsenal in the code I posted.