How can I chart the moon data against the tide data

[Aussiebear]

Within the attached workbook, is one sheet with Moon data (Date time of moon rise, meridian, and time of moon set), a second sheet with hourly tide data per day ( for 1st 4 months of this year) and a sheet with trial charts.

I have been plotting between 1 to 7 days tidal flow information, but am interested in if anyone can assist me in a method whereby I can also plot the moon data against the tidal data for the same period? Some of the older fishermen here have suggested that if you know when a moon rise occurs close to a high tide the fish activity is greatly increased, and I'm very interested in trialing any results against a plotted data set.

[p45cal]

if you know when a moon rise occurs close to a high tide the fish activity is greatly increased

In the attached in Sheet3 there is a chart.
The blue line is the tide height plotted against date and time. This data comes from Table1 which starts at cell A35 of the same sheet.
The first two columns of this data were obtained from running the macro reArrangeTideData which adds a new sheet and creates them. Note that there was an odd value at cell C116 of the Hourly Tide times sheet which I corrected.
I added a column to this data (cell C35) to mark the row if it was a high tide (mostly twice a day).

The orange points are the moonrise times plotted at the same tide height as the nearest high tide so that it becomes easy visually to assess their closeness in time to that high tide. They are a plot of columns 3 and 4 of Table2 which starts at cell A2918. This table started life being a copy of your data in sheet Moon Data columns J & K.
The column of this table headed nearest high tide, contains a formula, which, to keep as simple as possible needed a third table (Table3 starting at cell A3274) which is only a version of the first 2 columns of Table1 filtered for the word max in the third column. The last column of Table2 headed time diff will allow us later to limit which moonrises to plot by filtering this column to show only times below a threshold.

The reason these 3 tables are stacked vertically on the sheet is so that you can filter the top two to change what's plotted in the graph without each table's filter affecting other tables and thus the plot:
You can:
filter to only show the high tides on the blue line by filtering Table1 for max in column C
and/or
filter to only show moonrises less than x hours by filtering time diff in Table2. I've left it in the attached only plotting moonrises less than 2 hours from the nearest high tide which only plots 8 moonrise times. The first moon rise to be plotted in this case is 9th Feb at around 9am (just hover over the point on the chart to reveal date/times).

2019-04-07_174819.jpg

ps. looking at the chart there seems to be an odd value at cell S113 of the Hourly Tide Times sheet.

[Aussiebear]

Thank you P45cal. Good pickup for the two cells C116 and S113, for they were clearly incorrect when looking with fresh eyes (All the hourly figures are entered manually which is a big job for someone my age). It is interesting from the way you have presented the plotted data to show that the good combinations appear to occur on the run into neap tides. So this started me thinking....

Another old fisherman's tale is "an hour before and two hours after the top of the tide", which appears ( in our area of the woods), to equate to the last half meter of tidal flow in and out (turn of the tide). In the spring tides, this is roughly a 3 hour period, but in the neap tides it could be as much as 5 hours. So I may need to explore that option further by trying to plot any moonrise events that occur within the turn of the tide period

Second old tale is "No Run No fun". This I can account for given that fish tend to "anchor up", either behind structure or find no or low flow areas during strong tidal flows. This is why you can find schools of fish and hence increase catch rates. I don't believe that I need to follow this any further

[Aussiebear]

My bad..... A quick refresh and the graph plotted each moonrise at the top of every secondary high tide. Not quite what I was chasing.

[macropod]

A fundamental problem you'll have with this is that tides are also affected by the position of the sun, plus the seafloor geography and whether you're calculating for an esturary, bay, harbour or coast. Even the relative distance of the moon from the earth each month affects things.

[大灰狼1976]

@Ted
As I said, I send the attachment.
The test data is messy, but the basic functions can be achieved.
Now it's a simple example. It's still a long way from completion.
I haven't done many functions yet.，And I haven't revised the English expression of each name yet. It looks terrible.
It's just my idea. I don't know how far away from your goal.
I was worried that the map (sample) display would fail, so I sent the map pictures together.



--Okami

[Aussiebear]

Thank you Okami. I will need to study this to understand where this is headed.

[Aussiebear]

@Paul. The tide data is coming from AusmarineScience who I believe have taken the data from the BOM. So I am trusting it. Within each moon cycle the distance differs approximately 42,000 klms and yes I understand the bigger spring tides occur when the Sun and Moon align on the same side of the earth. I am at a loss of how scientists can calculate tide times given that up here tidal flows are effected by significant areas of low depth, non uniform reef structures ( resulting in both dinural and semi-dinural tide patterns) but I am prepared to take their data as near perfect.

[macropod]

The relative positions of the sun and moon affect both the tide timing and its strength. Presumably, there's an algorithm for calculating the nominal tide times & heights at some reference point, from which the figures for different locations are calculated. No doubt the latter incorporate adjustments based on the measurement of both the actual tide times and heights at those locations.

[Aussiebear]

It seems to me that my first issue is to decide how much data to plot. P45cal's first graph where tidal information is plotted over 4 months is good for showing longer term trends, such as Spring, Neap, Primary and secondary tidal moments. I think I need to zero in a smaller time frame, perhaps no more than 2 weeks at best to be able to plot a moon path to see where it intersects the tidal path. Given that the Y axis is using data on an hourly format, can a data set where the time factor is in a format where it can occur at any time with the hour going to cause a conflict?

[p45cal]

My bad..... A quick refresh and the graph plotted each moonrise at the top of every secondary high tide. Not quite what I was chasing.

Screenshot please. The plot is designed to show the moonrise time at the correct time that it happens, but placed on the y-axis at the same tide height as the nearest high tide height.

When you say you did a refresh, what did this refresh entail?
You realise that if you use new source data you will also need to re-create the maxima table (Table 3).

[大灰狼1976]

@Ted. Graph has both a left and right X axis scale and shares a common y axis.
--Okami
TEST2.jpg
TEST1.jpg

[Aussiebear]

Thank you Okami & P45cal. Unfortunately I am currently at work and my computer is 400 Kim’s away. Will be back to the file middle of next week