Re:Elaborate on the file Excel Help...VB 6.0 if necessary...

[Bill]

Patrick;

I'll try an elaborate. I have a fixed length text file emailed to me. I open
the file with Ms Word. Shrink the font, clean things up a bit and print,
where we have to manually go through a 200 page report everyday to
grab a few numbers...Anyhow the file is organized by stocks and option symbols.
There could be 1 or 100 lines of data. But the "key" is that
at the end of every "block" of symbols is last 1 to 5 records whose string
begins with an asterick. It looks like this:

7 records 5 with beginning astericks:

2LC00 VAN JAN 03 70.000 C 45 .........etc etc
2LC00 VAN JAN 03 75.000 C 45 .........etc etc
2LC00 VAN JAN 03 80.000 C 45 .........etc etc
** TOTAL ** 300* 30*
** TOTAL LONG SIDE VS. SHORT SIDE**
** STOCK**
** UNSTL**
** NET STK**
(PHLX) -71/ 1537

or it might look like this

5 records 3 with beginning astericks:

2LC00 VAN JAN 03 70.000 C 45 .........etc etc
2LC00 VAN JAN 03 75.000 C 45 .........etc etc
2LC00 VAN JAN 03 80.000 C 45 .........etc etc
** TOTAL ** 300* 30*
** TOTAL LONG SIDE VS. SHORT SIDE**
** STOCK** AOL
(PCX ) -4571/ 1537

I need nothing in the whole report except to print a report , or the results
of our filtering the last few lines.
I need the report to print out
a) The account number: (2LC00 in this case)
b) The Stock symbol: ( AOL in this case)
c) The Exchange: ( PCX in this case)
d) The numbers in the record: ( -4571 / 1537 )

If you have any more questions, or would like to recieve a sample of the
text file let me know..


Thanks for taking so much time out to help me :>)

Bill














"Patrick Marshall" <nospam@nospam.com> wrote:
>
>Hi Bill,
>
>Can you elaborate on what you are trying to do? When you say "generate
a
>report..." do you mean that you are trying to break up the text file into
>blocks bordered by the marker row (CBOE) -1745/ -124 ? If so, this can
easily
>be done with VB (or you can automate Excel to do it to, but I don't think
>it would be much simpler).
>
>For example:
>
>const kEndToken1 ="(CBOE) -1745/ -124"
>const kEndToken2 ="(AMEX) -1745/ -124"
>const kEndToken3 ="(PHLX) -1745/ -124"
>
>dim FileNumInt as integer
>dim BlockStrArr() as string 'one block for report
>dim SizeOfBlockStrArrLng as long
>dim s as string
>
>fileNumInt = freefile()
>open "c:\testInpt.txt" for input as fileNumInt
>
>'NOTE use instr with vbBinaryCompare since it is MUCH faster than
>'the default text compare (MUST be sure that your end tokens are
>'always same case
>do while not fileNumInt.eof
> line input #fileNumInt, s
> redim preserve BlockStrArr(SizeOfBlockStrArrLng)
> BlockStrArr(SizeOfBlockStrArr) = s
> SizeOfBlockStrArrLng = SizeOfBlockStrArrLng + 1
> if instr(1,s,kEndToken1,vbBinaryCompare) > 0 then
> 'insert your subroutine here to generate report
> GenerateBlockRept BlockStrArr(), SizeOfBlockStrArr, kEndToken1
> SizeOfBlockStrArrLng = 0
> redim BlockStrArr(0) 'blank out block for next run
> elseif instr(1,s,kEndToken2,vbBinaryCompare) > 0 then
> 'insert your subroutine here to generate report
> GenerateBlockRept BlockStrArr(), SizeOfBlockStrArr, kEndToken2
> SizeOfBlockStrArrLng = 0
> redim BlockStrArr(0) 'blank out block for next run
> 'elseif kendtoken3 etc.
> end if
>
>loop
>
>close #FileNumInt
>
>"Bill" <uliv@eqtc.com> wrote:
>>
>>Sirs or Madam;
>>
>>I have a HUGE fixed length text file to work with. I need to ready each
>line
>>until I “see” the last line in each group.In this case the string is:
>>
>>(CBOE) -1745/ -124 or
>>(AMEX) -1745/ -124” or
>>(PHLX) -1745/ -124”
>>
>>I need to write / print a report wherever it appears.
>>
>>Rather than write a VB program to do it, ( It seems rather complicated
for
>>this newbie)Does anyone know how to use Excel to sort, or filter each row
>>for this information?
>>
>>Excel seems to do most of the work, but VB would be cooler...
>>
>>Thanks in advance;
>>
>>
>>Bill
>>
>>
>>
>

[Patrick Marshall]

Hi Bill,

The code I posted should work partially for what you have detailed. What
is required is further processing to extract the report itself from each
"block" of data (now represented as a variable dimension string array, BlockStrArr()).
Also, since I now realize that the digits are a count in your block "tokens",
your tokens should be something like:

const kEndToken1 ="(CBOE)"
const kEndToken2 ="(AMEX)"
const kEndToken3 ="(PHLX)"

or your could read these out of a table and assign them at run time.

Once you have each block separated into BlockStrArr() with a size of
SizeOfBlockStrArrLng you can create a subroutine which takes these as parameters
and parse the required report data from this block. You might as well keep
it in the array form and do array manipulation to start from the bottom elements
and work your way upwards, e.g. the pertinent info in your report is clustered
in the bottom 10 or 15 lines and can be parsed.

>2LC00 VAN JAN 03 70.000 C 45 .........etc etc
>2LC00 VAN JAN 03 75.000 C 45 .........etc etc
>2LC00 VAN JAN 03 80.000 C 45 .........etc etc
>** TOTAL ** 300* 30*
>** TOTAL LONG SIDE VS. SHORT SIDE**
>** STOCK**
>** UNSTL**
>** NET STK**
> (PHLX) -71/ 1537

>a) The account number: (2LC00 in this case)
>b) The Stock symbol: ( AOL in this case)
>c) The Exchange: ( PCX in this case)
>d) The numbers in the record: ( -4571 / 1537 )

These values seem to be available in the last block of lines, and you should
be able to extract this data by counting columns. Just remember to work
BACKWARDS in the array, the last element contains the (PHLX) and counts,
by working backwards you can get the stock symbol.

Very generally, you can use mid$ to extract a substring from an array element,
e.g. s = mid$(BlockStrArr(SizeOfBlockStrArrLng-4),5,10) 'extracts a five
character string from the array element 4 from the BOTTOM starting at character
5. You can use instr to find specific positions of character strings (as
keys to column location) and val to convert padded values such as " 141
" to number 141.

With a little bit of fiddling, you should be able to extract the data you
need. Sorry I can't be more specific, but it is quite dependant on the column
spacing on the data (it would help if you had the format specification from
the sender of the document).

Please note, I added a correction to the code below, so use that instead

<Patrick>

"Bill" <uliv@eqtc.com> wrote:
>
>Patrick;
>
>I'll try an elaborate. I have a fixed length text file emailed to me. I
open
>the file with Ms Word. Shrink the font, clean things up a bit and print,
>where we have to manually go through a 200 page report everyday to
>grab a few numbers...Anyhow the file is organized by stocks and option symbols.
>There could be 1 or 100 lines of data. But the "key" is that
>at the end of every "block" of symbols is last 1 to 5 records whose string
>begins with an asterick. It looks like this:
>
>7 records 5 with beginning astericks:
>
>2LC00 VAN JAN 03 70.000 C 45 .........etc etc
>2LC00 VAN JAN 03 75.000 C 45 .........etc etc
>2LC00 VAN JAN 03 80.000 C 45 .........etc etc
>** TOTAL ** 300* 30*
>** TOTAL LONG SIDE VS. SHORT SIDE**
>** STOCK**
>** UNSTL**
>** NET STK**
> (PHLX) -71/ 1537
>
>or it might look like this
>
>5 records 3 with beginning astericks:
>
>2LC00 VAN JAN 03 70.000 C 45 .........etc etc
>2LC00 VAN JAN 03 75.000 C 45 .........etc etc
>2LC00 VAN JAN 03 80.000 C 45 .........etc etc
>** TOTAL ** 300* 30*
>** TOTAL LONG SIDE VS. SHORT SIDE**
>** STOCK** AOL
> (PCX ) -4571/ 1537
>
>I need nothing in the whole report except to print a report , or the results
>of our filtering the last few lines.
>I need the report to print out
>a) The account number: (2LC00 in this case)
>b) The Stock symbol: ( AOL in this case)
>c) The Exchange: ( PCX in this case)
>d) The numbers in the record: ( -4571 / 1537 )
>
>If you have any more questions, or would like to recieve a sample of the
>text file let me know..
>
>
>Thanks for taking so much time out to help me :>)
>
>Bill
>
>
>
>"Patrick Marshall" <nospam@nospam.com> wrote:
>>
>>Hi Bill,
>>
>>Can you elaborate on what you are trying to do? When you say "generate
>a
>>report..." do you mean that you are trying to break up the text file into
>>blocks bordered by the marker row (CBOE) -1745/ -124 ? If so, this can
>easily
>>be done with VB (or you can automate Excel to do it to, but I don't think
>>it would be much simpler).
>>
>>For example:
>>
>>const kEndToken1 ="(CBOE) -1745/ -124"
>>const kEndToken2 ="(AMEX) -1745/ -124"
>>const kEndToken3 ="(PHLX) -1745/ -124"
>>
>>dim FileNumInt as integer
>>dim BlockStrArr() as string 'one block for report
>>dim SizeOfBlockStrArrLng as long
>>dim s as string
>>
>>fileNumInt = freefile()
>>open "c:\testInpt.txt" for input as fileNumInt
>>
>>'NOTE use instr with vbBinaryCompare since it is MUCH faster than
>>'the default text compare (MUST be sure that your end tokens are
>>'always same case
>>do while not eof(fileNumInt) '''''''''''''corrected
>> line input #fileNumInt, s
>> redim preserve BlockStrArr(SizeOfBlockStrArrLng)
>> BlockStrArr(SizeOfBlockStrArr) = s
>> SizeOfBlockStrArrLng = SizeOfBlockStrArrLng + 1
>> if instr(1,s,kEndToken1,vbBinaryCompare) > 0 then
>> 'insert your subroutine here to generate report
>> GenerateBlockRept BlockStrArr(), SizeOfBlockStrArr, kEndToken1
>> SizeOfBlockStrArrLng = 0
>> redim BlockStrArr(0) 'blank out block for next run
>> elseif instr(1,s,kEndToken2,vbBinaryCompare) > 0 then
>> 'insert your subroutine here to generate report
>> GenerateBlockRept BlockStrArr(), SizeOfBlockStrArr, kEndToken2
>> SizeOfBlockStrArrLng = 0
>> redim BlockStrArr(0) 'blank out block for next run
>> 'elseif kendtoken3 etc.
>> end if
>>
>>loop
>>
>>close #FileNumInt
>>