Data Driven Deer Log (Part 2): Automating Data Retrieval From NOAA LCD in Your Log

[seazofcheeze]

After the recent Wired to Hunt podcast (Ep. 291) with the professor himself, JoeRe, a few people have expressed interest in creating a data driven deer hunting log. In the podcast, JoeRe talked about how he uses data to gain more insight into his hunting areas and glean information from his trail camera pics. However, it's kind of hard to explain in a podcast format how one would go about re-creating this type of spreadsheet. In the video below (Part 2 of this series), I illustrate the second step, making your hunting log do the heavy lifting for you! This step-by-step tutorial demonstrates how Excel formulas can retrieve National Oceanic and Atmospheric Administration (NOAA) Local Climatological Data (LCD) from a previous data import (Part 1 of the series).

This is a long and more complex video. You should have a good idea after this video if data collection and analysis is something you REALLY want to do or not. It can be a lot like work if you don't enjoy data.

I plan to put out another video by August 24th (hopefully sooner) on how to use Excel to identify trends.

If you have any questions, please post them on YouTube, so a wider audience can benefit from the questions and answers. Thanks!

https://youtu.be/c_xQ0O54REI

[Bio1]

Outstanding video! After listening to Joes podcast on Wired to hunt I figured out how to get the NOAA data but I was going through my hunt log and copying and pasting my observations into the NOAA spreadsheet. This is WAY easier. Thanks for taking the time! I’m excited to get this set up.

Lee

[Bio1]

Sea,

I tried to comment a couple times on YouTube but for some reason it isn't posting when I submit. First off, great tutorial! However, I am running into a snag. I downloaded all of the NOAA data from October of 2005 to the present to match up with my Hunt Log. When I am returning the Closest NOAA Time the formula appears to be keying in on the first close value to the Actual Observation Time. When I combine the actual date and Closest NOAA time under the Combined Date and NOAA Time and then write the formula to return the HourlyDryBulbTemp (for example) it is either returning a #N/A OR it is returning a value that is not actually the closest value in some instances. I imagine this has to do with the large amount of data and the large time span. Is there a formula I can write that will take into consideration the Actual Observation Date along with the Actual Observation Time to ensure that it returns the corresponding time closest to the Actual Observed Time? Not sure I'm good enough with Excel to figure this out! Hope this makes sense.

Thanks,

Bio1

[seazofcheeze]

Sea,

I tried to comment a couple times on YouTube but for some reason it isn't posting when I submit. First off, great tutorial! However, I am running into a snag. I downloaded all of the NOAA data from October of 2005 to the present to match up with my Hunt Log. When I am returning the Closest NOAA Time the formula appears to be keying in on the first close value to the Actual Observation Time. When I combine the actual date and Closest NOAA time under the Combined Date and NOAA Time and then write the formula to return the HourlyDryBulbTemp (for example) it is either returning a #N/A OR it is returning a value that is not actually the closest value in some instances. I imagine this has to do with the large amount of data and the large time span. Is there a formula I can write that will take into consideration the Actual Observation Date along with the Actual Observation Time to ensure that it returns the corresponding time closest to the Actual Observed Time? Not sure I'm good enough with Excel to figure this out! Hope this makes sense.

Thanks,

Bio1

When I am returning the Closest NOAA Time the formula appears to be keying in on the first close value to the Actual Observation Time.

This is correct, since the NOAA data is only recorded every hour or so, and NOT every minute, we need to find the closest NOAA time to our actual observation time. The reason we do the formula to convert our actual observation time to the closest NOAA time first is because the NOAA data may not (and almost always will not) contain our actual observation time (again, because NOAA takes measurements hourly, not by the minute) and later when we are using vlookup to retrieve our weather variables, we NEED an exact match for the formula to work correctly (here exact match means our actual observation date that we combined with the capital T, and the closest NOAA observation time to get our compound date+T+closest NOAA time. I hope that made sense.

Also, on this same point, when you are writing the formula to find the closest NOAA time, make sure of the following:
1. The first part of the formula (lookup value) NEEDS to be a cell reference, and NOT a manual text entry of the date. For example, you want =vlookup (A3,.....) where A3 is a reference to a cell that contains the first actual observation time. If, on the other hand, you enter =vlookup(2018-10-01,....) the formula will ALWAYS look for "2018-10-01" that is the difference between using a cell reference vs. hard-coded text within the formula. This could be causing the error.

When I combine the actual date and Closest NOAA time under the Combined Date and NOAA Time and then write the formula to return the HourlyDryBulbTemp (for example) it is either returning a #N/A OR it is returning a value that is not actually the closest value in some instances.

If your formula is working in the first few rows of the spreadsheet and then not working the farther down you get, I'd almost guarantee you forgot to use the "F4" key to lock the second part of the vlookup formula. This is the #1 area people new to vlookup make mistakes. This shows the first two parts of the formula from the video: =VLOOKUP(B3,Sheet2!$B$2:$B$33,1,TRUE) Notice the second argument in the formula Sheet2!$B$2:$B$33 Each column and row have a "$" before them. If your formula doesn't have these dollar signs to lock your table array in place, you will definitely get #N/A errors.

I imagine this has to do with the large amount of data and the large time span.

No. If the formula is written correctly, you could potentially have the entire worksheet filled up with data (which I believe allows 1 million+ rows and 16k+ columns). So I do not believe that is a problem.

If these steps don't help, I sent you a PM also.

[stash59]

I'm gonna have to be feeling extra smart. At the time I watch this one!!!