Getting the High and Low Tides from a Series of Data

H

Herbert Chan

Hello,

I have a long list of data for tide levels which is like as follows:

Date/Time Tide Level

Now, I want to extract from this long series of data all the high tides and
low tides, which in mathematical terms is to extract all the local maximums
and local minimums, together with the associated Date/Time from the data.

I have another programme that generates this series of data basing on a set
of parameters, but I don't know what the function is, so I can't really find
the 1st order derivative and set it to zero to get all the roots, etc.

How should I go about doing this? Using Excel or Access?

Thanks.

Herbert
 
K

Ken Snell [MVP]

The "brute force" way of doing this is to order the data in (likely)
chronological order, and then test each data point to see if it's larger
than the previous and next point, or smaller than the previous and next
point, in order to find the mins and maxs.

Off top of my head, if you want to do this just once, and you have fewer
than 65,536 data points, I'd probably use EXCEL and use a worksheet function
to put a value in an adjacent cell to show if it's a minimum (-1), nothing
(0), or maximum (1).

If you plan to do this often, then ACCESS may be better to use, which would
involve setting up a function to analyze the data in such a way that you
could create a similar data result.
 
B

Bernie Deitrick

Herbert,

Use Excel. Assuming your data is in column A and B, use a formula in column
C to identify the low and high tides. Enter this in cell C3

=IF(AND(C3>C2,C3>C4),"High",IF(AND(C3<C2,C3<C4),"Low",""))

and copy down to match your data. Then you can use the autofilter to show
just High or low, and use visible cells only to copy the filtered data to
another spot in your workbook.

Of course, if there is a lot of noice in your data, you may need to use more
complex logic to locate the High's and Low's.

HTH,
Bernie
MS Excel MVP
 
M

Michel Walsh

Hi,

I assume you want the Hi-Low "by ActionID" rather than for the whole
table:


SELECT a.ActionID, MAX(a.DateTimeStamp), a.Level
FROM myTable As a INNER JOIN myTable As b
ON a.ActionID = b.ActionID
GROUP BY a.ActionID, a.Level
HAVING a.Level IN ( MIN(b.Level), MAX(b.level) )


That returns just the latest dateTimeStamp having the Hi, or the Low mark,
for each ActionID. See http://www.mvps.org/access/queries/qry0020.htm for
alternatives.





Hoping it may help,
Vanderghast, Access MVP
 
B

Bernie Deitrick

Michael,

Can you extract all of the minimum values from the series using Access?

Bernie
MS Excel MVP
 
H

Herbert Chan

Thank you all for the very quick responses!

This one seems to be the easiest and most informative solution.

Herbert
 
M

Michel Walsh

Hi,

Sure. In the reference I supplied, methods 1, 2, and 4 (cascading
queries, sub queries and virtual table) will extract all occurrences
(dateTimeStamp) of an extremum. I prefer method 3, a total query, because it
is the only one to be 100% "graphically" compatible with the query editor,
but its limitation is to "naturally" retrieve just one occurrence of the
extremum. Method 1, cascading queries, is probably the next simpler method.
The idea is to make a query that find the min/max value for Level, per
ActionID, in query1:


SELECT ActionID, MIN(Level) As mi, MAX(Level) As ma
FROM myTable
GROUP BY ActionID



and then, used it as virtual table:


SELECT myTable.*
FROM myTable INNER JOIN query1
ON myTable.ActionID=query1.ActionID
AND
myTable.DateTimeStamp IN( query1.ma , query1. mi)


You can change the inner join to a cross join, and bring the ON clause in a
WHERE clause, if you prefer:


SELECT myTable.*
FROM myTable , query1
WHERE myTable.ActionID=query1.ActionID
AND
myTable.DateTimeStamp IN( query1.ma , query1. mi)





Hoping it may help,
Vanderghast, Access MVP
 
H

Herbert Chan

Thank you for your effort, Michel.

But my data don't have anything like an ActionID, which will be the same for
a number of records.

My data are

Date/Time, Tide Level

Date/Time is unique for every line of record and the tide level is a series
of data, the characteristics of which I want to extract. And I want to get
the local max/min of the series of Tide Level. The local max/min may be the
max/min over 3 days, a week, a month or whatever, depending on how the tide
actually behaves, and I don't know where the boundary of such periods are.

So if I have understood you correctly, your solution is more suited for
extracting such as the weekly or monthly Hi-Lo values for a series of daily
closes (by GROUPing by Format(datefield, 'ww') or Format (datefield, 'm'))?

Herbert
 
M

Michel Walsh

Hi,


Erratum: should be


...
AND
myTable.Level IN( query1.ma , query1. mi)




since query1.ma and query1.mi are Level values, not DateTimeStamp values.



Vandergahst, Access MVP
 
M

Michel Walsh

Hi,


Indeed, you got the picture. If there is no group to consider, you can
use:

SELECT a.*
FROM myTable As a
WHERE a.Level IN( SELECT MAX(level) FROM myTable
UNION ALL
SELECT MIN(level) FROM myTable)



Hoping it may help,
Vanderghast, Access MVP
 
Top