Monarch Degree Days Workbook

[Jim]

Version 3 of the Monarch Degree Days Workbook is available for download:

mDDworkbook-v3.xls (476K)

Taking the K.I.S.S. method to heart we have decided to provide this first temperature monitoring workbook with only a single spreadsheet. We will probably add a summary page to allow easy access to monarch Degree Day values by date (24-hour period) and perhaps some other details such as max/min temperatures, etc. but we'd like to get your thoughts on this first.

In this version I have decided to stay away from using macros since I do not know how well they will translate across all spreadsheet programs that are being used. Rather, simple functions are used to automatically do the necessary calculations provided that your data are copied and pasted into the appropriate cells. I think this is pretty straightforward but please share any questions or concerns here.

Download the workbook file using the link above and then follow the directions in the "Instructions for Retrieving Data" thread. Once you have all of your hourly temperature data in three columns of a spreadsheet you need to do a few things before pasting this information into the Monarch Degree Days Workbook:

(1) make sure that the first row of data corresponds to the first hour of a day (the midnight hour, which will appear as 0:## or 12:## AM). You will probably need to delete a few entire rows of data to do this.



(2) insert a blank row at the top so that your temperature data begins with the midnight hour in row #2 of your spreadsheet. To do this select the first cell (A1) and choose Insert --> Rows from the menubar in Excel. [If you are using another spreadsheet program please let me know what the appropriate command/menu item is - thanks!]



(3) select the three columns of your hourly temperature data (in their entirety) and copy them.



Now open the Monarch Degree Days Workbook. You will be presented with a single worksheet, "Monarch Degree Days" into which you will paste your data and review the resulting values.



Select the upper-leftmost cell (A1) and paste your data here. [Confirm that cells A1, A2, and A3 are empty and that your temperature data begins on Row 2; if this isn't the case please revisit the steps listed above to prepare your "raw" data.]



You will notice that the numbers in the other three columns of this worksheet will change. We will discuss these columns in greater detail later, but from left to right they are Adjusted T (a temperature adjustment necessary for the Monarch Degree Day calculation), mDD (monarch Degree Days), and Acc mDD (accumulated monarch Degree Days).

The sheet is set up to accept 80 days worth of hourly data (24 hours x 80 days = 1920 rows).

All numbers used in these calculations are monarch specific; we plan to include other sheets that will be able to calculate Degree Days for other plants (e.g., milkweed) and animals (e.g., monarch predators), all using the single hourly temperature data set you record with your temperature logger.

We have not yet worked out the details of getting your data to us, but it will either be by entering the data online or by sending us your workbook files via email (probably the latter for the initial study).

So take this worksheet for a test drive with your temperature data and let us know how things turn out. If you have any questions along the way please let us know. Thank you for your patience!

[tcannard]

I am using a freshly downloaded copy of OpenOffice.org 2.1 on my new ThinkPad T60p running Windows XP Pro Service Pack 2.
The first date collected in my raw data spreadsheet shows as 10/29/06 at 06:00 PM so I go down to 10/30/06 at 12:00 AM, insert a blank row, mark that along with the other two columns of data and go to the end of my "raw data", copy it and paste it starting at A2 on the Workbook. After the Paste, my date no longer displays in A2 - it shows ### and when I click on A2, the window at the top shows 10/31/2010 00:00:00
I have even tried including the inserted blank row and then pasting the data starting at A1 in the Workbook with the same results. If I include the blank row and paste at A2, the first row is blank.

[MILW]

The ####### indication just means that your cell is not wide enough to show the actual text in that cell (in Excel anyway)- you can usually make a cell wider by dragging the divider line right between the A and B column labels at the top. It is weird that your year is converting to 2010- I haven't tried putting any of my data into this new workbook, so I'll have to look into it.

[Jim]

Tom: As mentioned above, a cell filled with ####### indicates the cell is sized too small to display all of the data. You can resize the columns (1) manually by clicking on the line that separates the header columns (e.g, the line between A & B and dragging it out to whatever size you want OR (2) by double clicking that line to resize a column so that it automatically accommodates all data OR (3) by selecting the entire column or sheet and choosing Format --> Column --> AutoFit (in Excel) or Format --> Column --> Optimal Width... (in OpenOffice). FYI you can resize rows in a similar manner.

As for the bizarre date change is concerned (e.g., from 10/08/06 to 10/09/10; the time is unaffected) I'll have to do a little investigating (I observed this as well in OpenOffice). I can't say that I'm completely surprised though as I have experienced weird handling of dates in Excel before. This anomaly seems to rear its ugly head when copying data from your OpenOffice document and pasting it into an Excel-generated worksheet. Pasting the date into any Open Office worksheet seems to work as expected.

I can create an OpenOffice worksheet for you that will work (it would probably work for everyone actually) but I shouldn't have to...I'll investigate this a little further.

[tcannard]

Many THANKS for the ### "fix" - not being a spreadsheet user, I was not aware of that but stretching the cell did indeed display the full but still incorrect date.
I know different operating systems use different ways to keep track of dates. UNIX used the number of seconds since 1/1/1970, but that was used "internally" and I had never seen a date displayed in a "standardized" format change like that. It is good you can duplicate the date change anomaly as that means you can probably write a workaround or fix it.
THANKS for the help!

[tcannard]

I saw OpenOffice.org 2.2 was posted so I uninstalled my 2.1, downloaded and installed the FULL 2.2 version and started with one of my "text" files I created with Windows XP's NotePad to first convert that to a spreadsheet, mark my "midnight" reading and paste it into the Version 2 Monarch Watch workbook. Unfortunately, I still get the same date of 2011 (when I use the 2007 data). I even tried using "System" instead of the "Western Europe (Windows-1252/WinLatin 1) Character set it comes up with in the "text import" window in scalc.

On a positive note, my "common" milkweed is up almost 12 inches so we are ready for Monarchs in South Eastern PA.

[Jim]

Here you go...try this version:

mDDworkbook-v3.xls (476K)

[tcannard]

PERFECT on the date so I can only guess and hope the rest of the figures are good. Many THANKS.

[Judy Molnar]

Jim
Followed instructions to use the worksheet but came across a glitch or two.

1) I guess it needs to be emphasized to folks [like me] as they set up the logger's mission to make sure the temperature for Farenheit MUST be checked. I'd forgotten to do that and all temps on the logger recorded in Celsius. When I cut and pasted into the Excel workbook, no degree day conversion occurred. Whoopsie!

ARG! Is there a way to convert the temp from C to F either in the text document before you cut and paste or in the Excel workbook itself?

2) If you cut and paste into Cell A1 of the degree day workbook sheet, does that actually work? Isn't Cell A2 the first row for the workbook to convert degree days and A1 is a "header" row?

Regards,
Judy Molnar

[MILW]

You can easily switch from C to F in the logger, it will display the recorded temps in either format. If you didn't erase the old data, you can just read it again and copy as F.
You can use this formula in Excel to convert a column of C to F also
if your Centigrade values are in column A, paste this into the first cell of an adjacent column

=(A1*1.8)+32

and then fill down as many rows as you have data.

[Jim]

1) I guess it needs to be emphasized to folks [like me] as they set up the logger's mission to make sure the temperature for Farenheit MUST be checked.

As mentioned above, you can toggle C/F at any time - no worries!

2) If you cut and paste into Cell A1 of the degree day workbook sheet, does that actually work? Isn't Cell A2 the first row for the workbook to convert degree days and A1 is a "header" row?

Step #2 above instructs you to add a blank row ahead of your data in the process of formatting your data prior to the calculations. You're right, if you don't do this the calculations will not work properly.

Make sense?

Jim

[Judy Molnar]

MILW, Jim and all
Thanks for the help! All your comments and instructions were dead on. I just toggled the Farenheit on the logger [which I didn't reset, thankfully] and went through the data retrieval steps.

Once I cut and pasted into the worksheet, I thought, "darn, it still didn't work!" But then I looked at the data. The logger was running since March and the temps were so low that no degree days were recorded at the beginning. Once I scrolled down the worksheet, there they were! Wow!

I will give the loggers their missions and set them out today.

Thanks again,
Judy Molnar

[tcannard]

I see from Dr. Karen Oberhauser's page at:

http://www.learner.org/jnorth/tm/monarc ... wer07.html

the number of degree days/instar of Monarch development are pretty well documented but since I purchased two of the Thermochron "kits" (total of 4 thermochrons), I was wondering if anyone else tried to check the "calibration" or consistancy of readings on their thermochrons before placement at the HI and LO levels on their mounts? I put all 4 at the same height on my original mount and found almost a 2º difference between the one which read "highest" and the one which read "lowest" even during darkness.

[wbl56]

I was wondering if anyone else tried to check the "calibration" or consistancy of readings on their thermochrons before placement at the HI and LO levels on their mounts?

See my postings under the topic "hardware - Version 2." I found a similar difference wtih my 2 thermochrons to what you found with your four thermochrons.

[jbardwell]

I have the data from both loggers. Worksheet labeled "lower" is the logger positioned at .5m and worksheet labeled "Upper" is the logger positioned at 1.5m.

My question is about the Upper logger, which is the 2nd one you all sent me about a couple months ago. This has dates of 1/1/00. Did I not adjust the date properly or did your instructions forget to tell me to? Also, does this mean the times are not correct?

I will send you an e-mail with my mDDworkbook attached.
Jenny

[Jim]

My question is about the Upper logger, which is the 2nd one you all sent me about a couple months ago. This has dates of 1/1/00. Did I not adjust the date properly or did your instructions forget to tell me to? Also, does this mean the times are not correct?

Have you tried pasting the "raw" data into a text file first, to see if this is an Excel/spreadsheet issue? One other person reported similar behavior before using Open Office instead of Excel...

When you attach the "upper" logger to the probe and view the clock tab in OneWire Viewer, is the date and time correct?

When you started a new mission and checked the "Sychronize Real-time Clock" checkbox as indicated in the instructions the logger's clock would have been synched with your computer's clock. Now if your computer thinks it's the year 2000 - well that would explain things

[Bigal_Monarch]

How many State Climatologists do you have working on the program? I used to post this kind of data from my automatic weather stations on a daily basis.