Filter thousands of daily data

[Mark2Aus]

Hi everyone,
I have no knowledge of VBA and I will need great help to solve this problem.
Everyday, as they occur, every single price of a contract traded on the Sydney Futures Exchange is downloaded to my Excel 2007, starting from row 2 up to row 10,000 (+/-).
I would like to filter all these prices and only record on another sheet (row 3 starting from column B onwards), only prices which show a minimum 3-point correction.
Example:
Data received online from Sydney Futures Exchange, downloaded to column A from row 2 downwards:
4002
4001
3999
4000
4001
4002
4001
4003
4004
4005
4005
4006
4004
4003
4001
4001
3999
4002
4003
4000
etc..

Only the following prices fulfill the required filter of showing a minimum 3-point correction and will should be recorded on Sheet 2, Row 3, starting from column B...onwards:
4002,3999,4006,3999,4003.
Explanation: 4002 is recorded because prices from 4002 dropped to 3999 before moving to a level higher than 4002.
3999 is recorded because prices from 3999 moved higher to 4006 without any 3-point correction in between.
4006 is recorded because from there prices dropped to 3999 without any 3-point correction in between.
4003 is recorded because from there prices showed a drop of 3 points to 4000.
Thanks for the help
Mark

 

[N1GHTEYES]

This sounds pretty easy, but the problem is unclear.

Please specify in more detail how this all works. e.g. you say:

"every single price of a contract traded on the Sydney Futures Exchange is downloaded to my Excel 2007, starting from row 2 up to row 10,000 (+/-)"

- how do you get the data? Are you given a blank workbook with this day's data in it? or does it come as a data file which you load onto the workbook? or does somebody else's code take your workbook and populate it with the data?

Having got the data, do you keep a copy of previous data? If so, for how long? How big can your workbook get? Does it get a new sheet every day? When you filter the data onto the new sheet, do you overwrite yesterday's filtered data, or add a new filtered sheet, or column?

All of the above info and more will be needed if you want some help.

Tony

 

[ettienne]

I don't get it.
If you start with 4002 and record 3999 when the price dropped, why not record 4002 on the rise?
What I understand is that the sequence should be 4002, 3999, 4002, 4005, 4003, 3999, 4002, 4000.

 

[Mark2Aus]

Thanks for the comments.
So, let's go to each one of them.
1) N1GHTEYES:
I use a IRESS platform with Real Time Display. My spreadsheet is populated live with prices as they happen.
The market has varied a lot but the maximum number of contracts traded everyday can reach up to 50,000 but as the average lot is around 5 contracts per trade, I would say that 10,000 different prices are traded everyday.
I will not keep the raw daily data and everyday, I will blank the raw data and get ready to receive the next day raw data. Therefore it will always be the same column in the same sheet.
But, The filtered data will be kept and every day should be a new row on the Sheet 2.
2) ettienne:
You see, each number filtered must have a correction of a minimum of 3 points again it. Therefore, in your sequence,
4002 and 3999: both OK as 4002 has a 3-point move from it to 3999 and 3999 has a 7 point correction against it (it moved from 3999 to 4006 without having any 3-point correction until it reached 4006).
But, the next number in your sequence, 4002 is not correct as it did not have a 3-point correction against it. In fact prices kept moving higher without a 3-point correction up to 4006. The same apply to your next number 4005, etc.
Of course, as prices come live online and prices could move quite a bit alongside the same direction, each cell (say F3) should be changing and showing the highest (or lowest) price in that move alongside the same direction up to the point when prices reach a level from which they correct by 3 points. At that moment, Cell F3 should show the maximum (or minimum) price reached before the correction took place and then the next cell (G3) should be the one changing until a 3-point correction occurs, etc...repeating the process.
Thanks
Mark

 

[PHV]

So, what have YOU tried so far and where in YOUR code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Mark2Aus]

Thanks PHV,
Sorry. I have no knowledge of VBA. I don't know how to write codes. All my experience is with building formulas using Excel functions. But, never with macros.
Mark

 

[SkipVought]

"I have no knowledge of VBA"

So what do you expect here?

Have you tried anything on the sheet?

Have you tried plotting your data?

No one, here at Tek-Tips, is going to write a system for you in VBA. If you need that done, go hire a programmer.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Mark2Aus]

Thanks SkipVought.
I was able to plot my data, but my point is the amount of prices to plot (let's call it "noise") is enormous. That is why I thought of eliminating the "noise" and only plot prices which show a minimum of 3-points correction. But how to filter it? I tried examining all the available functions in Excel but can I do a dynamic check in every cell to see if it fulfills the required 3-point correction using an Excel function? I dont' think so.
When I try to plot the data (around 10,000 different prices per trading day) the chart was meaningless. I believe that I need a code for doing this. But, as I've never done any code, I need some help.
Thanks for any help you and the other members might give to me, and I am sorry you had the impression I was trying to have the VBA code written for me. No, what I need is help with ideas about how to find a way to filter (as I explained before to etienne) the data in the correct way.
I dont't work for anybody and I am not in a position to hire a programmer as I am literaly broke ( I lost my job 4 weeks ago). I am trying to restart my life and as my background is in the Financial Futures, I am trying something in this area.
Thanks for any help and I am sorry I gave you the wrong impression.
Mark

 

[SkipVought]

"I thought of eliminating the 'noise'"

How are you plotting this data? What Chart Type?

I would not think that "noise" would be a problem. Please explain exacly WHAT the problem seems to be.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Mark2Aus]

I am using a Line Chart and it is plotted with Select Data Source referring to the range ($B$3:$B$10000) and as a new price occurs, a new point is automatically plotted on my chart (on a separate workbook).
The problem is that plotting all the prices restrict the ability to see the real picture. Prices move in a random fashion and unless you can eliminate what I call "noise", that is, prices moving few points in both directions, going nowhere and lasting hundreds of prices, you cannot see the clear trend.
The second point is if you plot every single price traded, and there are around 10,000 prices in a single day, it becomes impossible to see what the market is doing and the view of the chart becomes an issue, as too many points are plotted. For you to "read" the chart and be able to interpret what you see, it is necessary to throw away the "junk" stuff: price moves that have no meaning to the market trend. It is like examining the DNA sequence. There have millions of letters but most of it is junk.
By eliminating prices that do not help to understand the market, it will make it easier to interpret the chart.
Thanks

 

[ettienne]

There is an area of statistics known as regression analysis which deals with modeling and analyzing numerical data, such as what you have. The purpose is to determine linear or non-linear trends, i.e. predict what will happen in the future based on what happened in the past. This is way beyond what can be discussed in a forum like this.
You are making some fundamental errors in your assumptions, there is no "noise" or "junk" in your data, every piece of data is significant and tells you something about the trend, even if it appears to be random and not make sense.
Looking at a line chart in Excel is not going to give you the picture, you need to look at a statistical program to analyze the data. There are some free and not so free programs around, some are Excel add-ins. Here is a list:

http://en.wikipedia.org/wiki/List_of_statistical_packages.

Statistics and quantitative analysis was one of my majors, so I kind of know what I am talking about.

 

[Mark2Aus]

Thanks ettienne for your comments. I really appreciate your help.
The point is everybody in the markets use the same statistical tools and the same technical analysis. And that is the major reason why 99% of the players lose all their money playing futures. There is no way to predict the future based on the past and who tries to do it will fail.
What I am trying to do is escape from the common statistical analysis and see what the market is doing and showing now. That is the big difference in what I am doing. Obviously every single price is important, but try to see through 10,000 or 15,000 prices everyday without a filter, discarding some of the prices that go nowwhere and you will see that it is impossible to make any sense of it.
I am 60 years old. My education background is in quantitative finance and I have almost 35 years trading futures on the floor of major institutions (before having been made redundant a month ago).
Thanks anyway
Mark

 

[dixiematic]

What you have to deal with is called a Time Series Analysis and there are commercial programs available to study it. I do not quite understand the idea of the 3 point correction but anyway using an appropriate software you could filter your series as you like.
The software to analyse time series is mainly based on routines how to find a certain pattern within series and to estimate the unpredictable proportion of the series. For the first task it uses statistical tools, Fourrier analysis and wavelets approach. None of them alone and none of them is as easy as "smoke of nothing", but combined methods could be very effective and one can extract a large proportion of the predictive part of a series so that it is possible to make a very reliable forecasting.
About 15 years ago I was for about two years following the money exchange rates (before we got the Euro) and my forecastings for the next day rate were true to the third decimal place and a forecasting a week in advance was still within 15%.
Why am I not making money with it? Firstly because I am a technician, because I am not American and because I have much more attractive hobbies.
M777182