More Excel help needed

Discussion in 'Trading Software' started by hans123, Aug 27, 2007.

  1. hans123


    I have a column in Excel containing strings of positive integers with before and after a string of zeros. For example:

    What I want to do is to revert the string of integers between the zeros. The above example will end in:

    Any idea how to program this in Excel?

    Thanks in advance.

  2. Lordie- please tell me why you posed this question ?
    Was this a test for quant-types like me or what ?
    Is there really a PRACTICAL problem to be solved here ?
    Here is the solution:
    It is good up to 100 characters in length.....
    Place the code below the asterisks into a Module.
    Hint: Hit F11 key, and then do an "Insert Module" from the Menu Bar.
    On the worksheet, make sure the source column is formatted as text as well as the target column.
    in the target column, enter this in the first row:
    Copy this cell to all cells below that need to be converted.
    (Note: this assumes your source data is in Column "A"...if not, please change to the appropriate column letter)
    Public Function ReverseAtZero(pRng As Range) As String
    Const MAX_CHARS = 100
    Dim rngData As Range, strIn As String, strOut As String
    Dim ar_init(1 To MAX_CHARS) As Byte, ar_final(1 To MAX_CHARS) As Byte
    Dim bZero_Char As Boolean, bChar_Zero As Boolean
    Dim i As Long, j As Long, lStartZ As Long, lEndZ As Long, k As Long
    Dim iZero As Integer
    iZero = Asc("0")

    strIn = pRng.Value
    If Len(strIn) = 0 Then Exit Function

    For i = 1 To Len(strIn)
    ar_init(i) = Asc(Mid(strIn, i, 1))
    For i = 1 To Len(strIn)
    bZero_Char = ar_init(i) = iZero And ar_init(i + 1) <> iZero
    bChar_Zero = ar_init(i) <> iZero And ar_init(i + 1) = iZero
    If bZero_Char Then lStartZ = i + 1 _
    Else: If lStartZ > 0 And bChar_Zero Then lEndZ = i
    If lStartZ > 0 And lEndZ > 0 Then
    k = 0
    For j = lEndZ To lStartZ Step -1
    ar_final(k + lStartZ) = ar_init(j)
    k = k + 1
    lEndZ = 0: lStartZ = 0
    ar_final(i) = ar_init(i)
    End If
    ReverseAtZero = ByteArrayToString(ar_final())
    End Function
    Public Function ByteArrayToString(ByRef bytArray() As Byte) As String
    Dim sAns As String, iPos As Long

    sAns = StrConv(bytArray, vbUnicode)
    iPos = InStr(sAns, Chr(0))
    If iPos > 0 Then sAns = Left(sAns, iPos - 1)

    ByteArrayToString = sAns

    End Function
  3. dtan1e


    sh...ttt, i never know people like that exists !!!
  4. Using R, this would collapse to a single line:

    > a <- "00012301200123456780000001230100001234500"
    > paste(lapply(lapply(strsplit(strsplit(paste(a,"0",sep=""),"0")[[1]],""),rev), paste, collapse=""), collapse="0")
    [1] "00032102100876543210000003210100005432100"
  5. hans123


    Just to be sure. The individual cells in the given example are:

    I want a result like:

    Syswizard, is your function doing this?

  6. 00012301200123456780000001230100001234500



    These are the before/after test results.
  7. sim03


    Oh, brother. This is what's known as a "spec drift"...
  8. Exactly....he never mentioned each digit was in a SEPARATE CELL.
    However, with VBA, it should not be a huge modification.
    But at this point, I wonder if he is just "playing" here.
    I haven't heard one word as to how and why the data is so strangely arranged like this.
  9. hans123


    Guys, don't be suspicious. I was asking for some serious help. Sorry, for the misunderstanding. I will give some background for my question.

    I have an Excel sheet with a column displaying the consecutive wins minus one. This column contains a zero or a positive integer. Each integer is followed by the previous integer plus one or a zero. I have some open positions also and a position should be closed only when the consecutive wins is ending (when there is a zero).

    How can I calculate the result of each trade? Some trades will take one, two, three or even eight days. I know I can use in Excel the command "Offset" for scrolling up and down in a column. If I have four consecutive wins, it's showing up after the fourth trade. This number should be showing up at the first trade, the second trade should show a three and so on.

    That the reason why I was asking. But maybe there are better solutions.

    Thanks for your response so far. Looking forward to see a solution.

  10. At this point, explaining this is fruitless...why don't you attach a sample of the actual XLS file for us to review and work-with. That way, there are no misunderstandings.
    #10     Aug 28, 2007