Integration of ascii data into excel

[sullivandavid]

I have created a excel spreadsheet that imports ascii data thru webquery directly into the spreadsheet .
The information is date,time, trade price,trade size using a product called q collector for esignal and refreshes the data every 5 seconds

From the data I use certain array calculations to extract useful info
I.E. Where col A = date B= Time of trade C= Price of Trade D=Tradesize

{=MAX(IF($A$7:$A$14449=Today,C$7:C$14449))} Max price for time period selected (MAXr) Named range
{=MIN(IF($A$7:$A$14449=$A$1,C$7:C$14449))} Min price for time period selected
(MINr) Named range

{=COUNT(IF($B$7:$B$14449>TimeRange,B7:B14449))} number trades durning time period

=SUM(IF($C$7:$C$14449=$G10,IF($B$7:$B$14449>$H$1,$D$7:$D$14449))) volume during time range

where colum G is a table of prices

The lastest time the contracted trade at price minutes ago
=NOW()-MAX(IF($C$7:$C$14449=$G9,IF($B$7:$B$14449>$H$1,$B$7:$B$14449)))

1st time traded at the price within the timerange

=MIN(IF($C$7:$C$14449=$G9,IF($B$7:$B$14449>$H$1,$B$7:$B$14449)))

$H$1=Timerange

TimeRange Last 60 minutes =(NOW()-TODAY())- (60/1440)

I plot the value that sums up the total at each price using

=IF(F9="x",REPT("n",ROUND(E9/Units,0)),"&quot using windings formating
where column E is the total traded at a price within the time frame

where column f =
=IF(G9<=MAXr,IF(G9>=MINr,&quot;x&quot;,&quot;&quot)
where colum G is a table of prices


The problem is there a way to import the most recent data only from an ascii file thru a webquery, pivot table or into access and create a DDE link to excel for the most recent data so the spreadsheet doesn’t need to perform the calculations.

The file gets very large and slows down the calculation process
Even with a timer to turn autocalc on and off its slow

Any Suggestions ?
Thank you

 

[SkipVought]

Hi,

Can't provide any insite on getting the &quot;New&quot; data, since I don't know how the data is structured, what your DDE link looks like, etc.

However, some OTHER comments.

1. TODAY is a Worksheet Function, and as so, is used

TODAY()

WITH the parens.

2. How do you know that you have 14449 rows of data? I would suggest using the OFFSET function and naming each range used in your formulas...

For instance the DATE range, $A$7:$A$14449 (assuming that it is in Sheet1), could be named in Insert/Name/Define, lest say rngDate...

=offset(Sheet1!$A$7,0,0,counta($A:$A)-6,1)

and you did the same for the price, naming it rngPrice,

then your MAXr formula would read...

{=MAX(IF(rngDate=Today(),rngPrice))}

AND you would be analyzing ONLY the data that is actually on the sheet!

VOLA! It's gotta save sumthin', mebe???

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[sullivandavid]

I read two ways to define a range using the offset method and hard coding ie

Sub Set_TradeSize()
Application.ScreenUpdating = False
[tradesize].Activate
FirstCell = ActiveCell.Address
curcolm = ActiveCell.Column
LastCell = Cells(65536, curcolm).End(xlUp).Address
rng = FirstCell & &quot;:&quot; & LastCell
Range(rng).Name = &quot;TradeSize&quot;
[a1].Select
Application.ScreenUpdating = True
End Sub

however the formula changes automatically when new data is added that why I have 14449 lines

Q_ Can one extract (import) only selective data from a text (ascii)
file rather than the entire file ????

ie last one hour of data express as column B

9/24/2003 7:30:12 9540 1
9/24/2003 7:30:21 9539 3
9/24/2003 7:30:49 9538 1
9/24/2003 7:30:50 9538 2

 

[SkipVought]

The OFFSET worksheet function and the Offset Method in VBA work in different ways.

I quoted you a WORKSHEET approch to make your formula more dynamic.

Your code could be refined as such into a function...

Function Set_TradeSize() As Long
    Set_TradeSize = Cells(65536, ActiveCell.Column).End(xlUp).Row
End Function
Sub Test()
   MsgBox Set_TradeSize & " is the last row in column " & ActiveCell.Column
End Sub

Yes, it's possible to read selected records from a tex file. What is the selection criteria?

How are you doing your DDE?


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[sullivandavid]

data has only 4 fields date time price size
file can be txt or csv space, delimiter etc

search or filter would be the last hour of data for todays
date I would have a macro refresh the query every 5 secs

The dde data is there to reconcile last sale info and the lag of the refresh function

I like the function approach you suggested
Thanks

 

[SkipVought]

geezz

it would be helpful to see the code that you re using.

as far as reading a file, you read a row of data, evaluate the data and when you get to a certain point in your evaluation, you stop reading, assuming that the element of data that you are evaluating has been ordered in the way that you expect, like the date/time is in decreasing order.

so when the input date/time <= your MAX date/time, you stop inputting records.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com