Monarch Degree Days Workbook

A read-only repository of popular topics and frequently asked questions - check here first!

Moderator: Monarch Watch

Monarch Degree Days Workbook

Postby Jim » Tue Feb 20, 2007 12:36 am

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.

Image

(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!]

Image

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

Image

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.

Image

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.]

Image

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! :)
Last edited by Jim on Wed May 30, 2007 1:15 pm, edited 2 times in total.
User avatar
Jim
Monarch Watch Research Assistant
Monarch Watch Research Assistant
 
Posts: 344
Joined: Tue Jul 13, 2004 1:09 pm
Location: Kansas, USA

Monarch Degree Days Workbook

Postby tcannard » Wed Feb 21, 2007 6:11 pm

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.
tcannard
 
Posts: 15
Joined: Mon Jul 03, 2006 9:38 am
Location: Havertown, PA

Postby MILW » Wed Feb 21, 2007 11:10 pm

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.
Waystations 15 & 629
Madison, Wisconsin
User avatar
MILW
 
Posts: 137
Joined: Sun Jul 25, 2004 9:18 am
Location: Madison, Wisconsin USA

bizarre date change

Postby Jim » Thu Feb 22, 2007 8:37 am

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.
User avatar
Jim
Monarch Watch Research Assistant
Monarch Watch Research Assistant
 
Posts: 344
Joined: Tue Jul 13, 2004 1:09 pm
Location: Kansas, USA

Postby tcannard » Mon Feb 26, 2007 5:03 pm

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
 
Posts: 15
Joined: Mon Jul 03, 2006 9:38 am
Location: Havertown, PA

OpenOffic.org 2.2

Postby tcannard » Tue May 29, 2007 12:33 pm

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.
tcannard
 
Posts: 15
Joined: Mon Jul 03, 2006 9:38 am
Location: Havertown, PA

Workbook Version 3

Postby Jim » Wed May 30, 2007 1:14 pm

Here you go...try this version:

mDDworkbook-v3.xls (476K)
User avatar
Jim
Monarch Watch Research Assistant
Monarch Watch Research Assistant
 
Posts: 344
Joined: Tue Jul 13, 2004 1:09 pm
Location: Kansas, USA

Postby tcannard » Wed May 30, 2007 1:31 pm

PERFECT on the date so I can only guess and hope the rest of the figures are good. Many THANKS.
tcannard
 
Posts: 15
Joined: Mon Jul 03, 2006 9:38 am
Location: Havertown, PA

Transferring logger data to excel worksheet

Postby Judy Molnar » Thu Jun 28, 2007 12:37 pm

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! :oops:

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
Education Associate
Virginia Living Museum
Personal Waystation #271
Beta tester at
Monarch Waystation #299
User avatar
Judy Molnar
 
Posts: 26
Joined: Mon Oct 04, 2004 10:26 am
Location: Newport News, VA

Postby MILW » Thu Jun 28, 2007 1:25 pm

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.
Waystations 15 & 629
Madison, Wisconsin
User avatar
MILW
 
Posts: 137
Joined: Sun Jul 25, 2004 9:18 am
Location: Madison, Wisconsin USA

Re: Transferring logger data to excel worksheet

Postby Jim » Thu Jun 28, 2007 10:17 pm

Judy Molnar wrote: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!

Judy Molnar wrote: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
User avatar
Jim
Monarch Watch Research Assistant
Monarch Watch Research Assistant
 
Posts: 344
Joined: Tue Jul 13, 2004 1:09 pm
Location: Kansas, USA

Transferring logger data to Excel worksheet

Postby Judy Molnar » Fri Jun 29, 2007 9:21 am

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
Education Associate
Virginia Living Museum
Personal Waystation #271
Beta tester at
Monarch Waystation #299
User avatar
Judy Molnar
 
Posts: 26
Joined: Mon Oct 04, 2004 10:26 am
Location: Newport News, VA

degree days for instar development

Postby tcannard » Sat Jul 07, 2007 2:58 pm

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.
tcannard
 
Posts: 15
Joined: Mon Jul 03, 2006 9:38 am
Location: Havertown, PA

Re: degree days for instar development

Postby wbl56 » Sun Jul 08, 2007 1:37 pm

tcannard wrote: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.
Wade Lawrence
Lawrence Family Garden
Monarch Waystation #163
Kalamazoo, Michigan
User avatar
wbl56
 
Posts: 18
Joined: Sun Sep 18, 2005 4:19 pm
Location: Kalamazoo, Michigan

questions on data as of July 15, 2007

Postby jbardwell » Sun Jul 15, 2007 8:28 am

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
Jenny Bardwell
HSTA Curriculum Coordinator
jbardwell
 
Posts: 9
Joined: Tue Jul 25, 2006 5:39 pm
Location: Morgantown, WV

Re: questions on data as of July 15, 2007

Postby Jim » Mon Jul 16, 2007 11:11 am

jbardwell wrote: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 ;-)
User avatar
Jim
Monarch Watch Research Assistant
Monarch Watch Research Assistant
 
Posts: 344
Joined: Tue Jul 13, 2004 1:09 pm
Location: Kansas, USA

Monarch DD

Postby Bigal_Monarch » Thu Jul 19, 2007 3:53 pm

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.
Bigal_Monarch
 
Posts: 3
Joined: Thu Jul 19, 2007 1:40 pm
Location: Volga SD


Return to Popular Topics

cron