Archives

Nov 17, 2007

Converting data to NF1 format

 If you have a list of date/data pairs, you can convert it to NF1 format using something like the following: 
 SternSetCharacteristics[datalist_, formatcode_, name_, timeunit_, 
  type_] := 
 Module[{prob}, prob = 0; 
  prob = prob + 
    If[MemberQ[
      StringMatchQ[{"year", "quarter", "monthly", "week", "day", 
        "hour", "minute", "second"}, timeunit], True], 0, 1]; 
  prob = prob + 
    If[MemberQ[StringMatchQ[{"pctchanges", "delta", "value"}, type], 
      True], 0, 10]; 
  If[Mod[prob, 10] > 0, 
   "SSC: Trouble with inputs. Prob = " <> prob <> ".", 
   If[Length[datalist[[1]]] == 2, 
    Prepend[{datalist}, {formatcode, name, timeunit, type}], 
    Prepend[Drop[datalist, 1], {formatcode, name, timeunit, type}]]]]
(note that the code above dates to a time before I had figured out how to create proper Mathematica error messages). 
SternSetCharacteristics::usage = 
  "SternSetCharacteristics[datalist_,formatcode_,name_,timeunit_,type_
]: Given a financial data object and a list of characteristics, 
assigns the characteristics to the object. Timeunit must be year, 
quarter, monthly, week, day, minute, or second. Type must be 
pctchanges, delta, or value. Works on data/data lists, or on existing NF1 
objects. With NF1 objects, though, SternChangeCharNF1 will often be 
more useful.";
If you have an NF1 object and want to change its characteristics, the following is useful. 
Options[SternChangeCharNF1] = {formatcode -> "df", name -> "df", 
   timeunit -> "df", type -> "df"};

 SternChangeCharNF1[nf1list_, OptionsPattern[]] := 
 If[(ToString[Head[OptionValue[formatcode]]] != "String" || 
    ToString[Head[OptionValue[name]]] != "String" || 
    ToString[Head[OptionValue[timeunit]]] != "String" || 
    ToString[Head[OptionValue[type]]] != "String") , 
  Message[SternChangeCharNF1::"notstring"], 
  SternSetCharacteristics[nf1list[[2]], 
   If[OptionValue[formatcode] == "df", 
    SternGetChar[nf1list, "formatcode"], OptionValue[formatcode]], 
   If[OptionValue[name] == "df", SternGetChar[nf1list, "name"], 
    OptionValue[name]], 
   If[OptionValue[timeunit] == "df", 
    SternGetChar[nf1list, "timeunit"], OptionValue[timeunit]], 
   If[OptionValue[type] == "df", SternGetChar[nf1list, "type"], 
    OptionValue[type]]]]

SternChangeCharNF1::notstring = 
  "Something other than a string was passed where a string was 
expected.";

SternChangeCharNF1::usage = 
  "SternChangeCharNF1[nf1list_,opts___]: Given an NF1 list and some 
subset of formatcode->(whatever, but typically nf1), 
name->(whatever), timeunit->(year, quarter, monthly, week, day, 
minute, or second) and type->(pctchanges, delta, or value), sets the 
properties of the nf1 object accordingly. Typically easier to use 
than SternSetCharacteristics, as the arguments are optional.";
Closely related, the following function can be used to extract a given characteristic. I use this commonly when using Mathematica to determine which of many data sets fits a specified characteristic (highest risk-adjusted returns, for example). This function makes it easy to report the name of the selected series. 
SternGetChar[datalist_, whichchar_] := 
 Module[{prob, fieldnum}, 
  If[StringMatchQ[datalist[[1]][[1]], "nf1"],(* have nf1 format *) 
   fieldnum = 
    Switch[whichchar, "formatcode", 1, "name", 2, "timeunit", 3, 
     "type", 4] (* else we have a problem *)]; prob = 0; 
  prob = prob + 
    If[MemberQ[
      StringMatchQ[{"formatcode", "name", "timeunit", "type"}, 
       whichchar], True], 0, 1]; 
  If[prob > 0, "SGC: Trouble with inputs. Prob=" <> prob <> ".", 
   datalist[[1]][[ToExpression[fieldnum]]]]]

SternGetChar::usage = 
  "SternGetChar[datalist_,whichchar_]: Given a financial data object 
and an identifying field (formatcode,name,timeunit, or type), returns 
the value of that field.";
Financial data can come from many sources, and it is best to convert it to NF1 format before moving it into Mathematica. I have code in MySQL that does this, and in Microsoft Access, as well as VBA code that does this in Excel. At some point, if anybody wants it, I may post my current production code, which can handle data formatted in many ways and correctly tags the NF1 object in almost every case, even processing multiple series properly and copying them all into the clipboard for ease in moving them to Mathematica. For now, here is a very simple version which assumes you have two columns -- one with dates, and the other with data. Create an empty cell with the name "output", then select the date and data cells and run the following VBA: 
Sub selectconversion()
' if it tries to output excel-style scientific notation, change the "General Number" formats to "#,##0.0000"
' needs a cell call "output"
    Dim i As Integer
    Dim outstring As String
    Dim fmtcode1 As String
    Dim fmtcode2 As String
    Dim col1isString As Boolean
    Dim col2isString As Boolean
    
    col1isString = IsDate(Selection.Cells(1, 1))
    fmtcode1 = IIf(col1isString, "Short Date", "0.#################")
    outstring = "{"
    If Selection.Columns.Count = 1 Then
        outstring = outstring & IIf(col1isString, Chr(34), "") &
        IIf(Selection.Rows.Count > 0, Format(Selection.Rows(1), fmtcode1), "")
        & IIf(col1isString, Chr(34), "")  ' for avoiding an extra comma
        If Selection.Rows.Count > 1 Then
            For i = 2 To Selection.Rows.Count
                outstring = outstring & ", " & IIf(col1isString, Chr(34), "") &
                Format(Selection.Rows(i), fmtcode1) & IIf(col1isString, Chr(34), "")
            Next i
        End If
    Else    ' more than one column
        col2isString = IsDate(Selection.Cells(1, 2))
        fmtcode2 = IIf(col2isString, "Short Date", "0.#################")
        outstring = outstring & IIf(Selection.Rows.Count > 0, "{" &
        IIf(col1isString, Chr(34), "") & Format(Selection.Cells(1, 1), fmtcode1) &
        IIf(col1isString, Chr(34), "") & "," & IIf(col2isString, Chr(34), "") &
        Format(Selection.Cells(1, 2), fmtcode2) & IIf(col2isString, Chr(34), "") &
        "}", "")    ' for avoiding an extra comma
        If Selection.Rows.Count > 1 Then
            For i = 2 To Selection.Rows.Count
                outstring = outstring & ", {" & IIf(col1isString, Chr(34), "") &
                Format(Selection.Cells(i, 1), fmtcode1) & IIf(col1isString, Chr(34),
                "") & "," & IIf(col2isString, Chr(34), "") & Format(Selection.Cells(i, 2),
                fmtcode2) & IIf(col2isString, Chr(34), "") & "}"
            Next i
        End If
    End If
    outstring = outstring & "}"
    Range("output").Value = outstring
End Sub
This is enough of utility functions for now. Starting with my next entry, we will have code that can be used in the analysis of data.

permalink | | 2007.11.17-22:45.00

hum