General Topics
Technical Topics
Brokerage Firms
Community Lounge
Site Support

# 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:
00012301200123456780000001230100001234500.

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

Any idea how to program this in Excel?

Hans

2. ### syswizard

Lordie- please tell me why you posed this question ?
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:
=ReverseAtZero(\$A1)
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
Application.Volatile
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))
Next
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
Next
lEndZ = 0: lStartZ = 0
Else
ar_final(i) = ar_init(i)
End If
Next
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. ### NoWorries

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:
A[1]=0
A[2]=0
A[3]=0
A[4]=1
A[5]=2
A[6]=3
A[7]=0
etc.

I want a result like:
B[1]=0
B[2]=0
B[3]=0
B[4]=3
B[5]=2
B[6]=1
B[7]=0
etc.

Syswizard, is your function doing this?

Thanks.

6. ### syswizard

00012301200123456780000001230100001234500
00032102100876543210000003210100005432100

123012001234567800000012301000012345
123021008765432100000032101000012345

012301200123456780123010123450
032102100876543210321010543210

These are the before/after test results.

7. ### sim03

Oh, brother. This is what's known as a "spec drift"...

8. ### syswizard

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.

Hans

10. ### syswizard

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
ET IS FREE BECAUSE OF THE FINANCIAL SUPPORT FROM THESE COMPANIES: