Creating Personalized Transit Datebooks

Contributor: Philip Covitz

E-Mail Address: pcovitz@mindspring.com

Software Recipe Title: Creating Personalized Transit Datebooks

Software Requirements: Halloran AstrolDeluxe ReportWriter 5 or higher, Microsoft Excel and Outlook

The technique described here uses Microsoft Excel and Outlook to create personalized transit datebooks for yourself and your clients. It is modeled after Llewellyn's astrological datebooks; however, instead of listing mundane aspects for each day, this technique will create a calendar showing daily transits to the natal chart. The format is similar to the Llewellyn datebooks, being printed on 8½ x 11 paper, landscape orientation, double-sided, so that when folded in half it results in a booklet 4¼ x 5½. This is a great product to offer clients, not to mention oneself. Furthermore, you can, if you choose, include dates for aspects as they enter orb, become exact, and leave orb, which the Llewellyn calendars do not provide; AstrolDeluxe also lists the house placements of transiting/transited planets. It does not, however, include planetary sign ingresses, moon phases, planetary stations, voids-of-course or eclipses. These, of course, can be added, but since AstrolDeluxe does not perform these calculations for you, they will not be discussed here (the Ephemeris Generator can be of use here, if you wish to include these additional features, although you could just as easily enter them manually by copying the data from an existing Llewellyn datebook).

Benefits of AstrolDeluxe

One of the greatest benefits, in my opinion, of AstrolDeluxe is the fact that the data it generates is in the form of delimited text, which means they can be copied and pasted into other applications. I also run a copy of WinStar on my machine, but the data tables it provides can only be exported as graphics, which to my mind makes them almost useless. Because AstrolDeluxe data can be so easily pasted into other applications, you can manipulate them in various very useful ways, including in terms of how they are formatted and printed.

The Power of Excel

Microsoft Excel is a very powerful program, and combining it with the data from AstrolDeluxe allows you to perform all sorts of calculations and formatting procedures that greatly expand the functionality of the data for practical use. The same thing applies to other Microsoft Office products; AstrolDeluxe data can be imported into an Access database, for example, or a Word document. If one is willing to become familiar with Excel formulas or, better yet, Visual Basic for Applications (VBA), one can also create simple or complex macros in Microsoft Office programs that will do things like calculate mean daily motion (using AstrolDeluxe's ephemeris generator), calculate cosmodynes, figure applying/separating aspects, and much more.

What You'll Need

For this project, you will need AstrolDeluxe (of course); Microsoft Excel and Outlook. It also helps to have an astrological font that has been mapped into AstrolDeluxe (I'm using the Halloran TrueType font). You should also be reasonably comfortable using Excel formulas and functions, copying and pasting, etc. It may also help to have a calendar for the transit period handy, to figure out Daylight Saving Time periods.

Considerations and Limitations

One drawback to this technique is that there is limited space available on the Outlook calendar pages. The Llewellyn datebooks use multiple columns within each daily entry that allow aspect listings to "flow over" from one column to the next. In Outlook, we are limited to one column of data per day. Even with the smallest legible font size (6 or 7 pt), you may still run out of room within a daily entry if that day's aspect list is very long; any overflow is truncated, with a little down-arrow indicating that there are more aspects that have not been listed. Therefore, the first thing to do is decide what you will include and what you will leave out of your transit calculations.

Which Planets?

You can, of course, exclude certain planets from the datebook. The Moon is one obvious candidate, since her transits are so frequent, and yet so fleeting. On the other hand, lunar transits are exactly the sort of thing you'd want if you were tracking transits day by day (as opposed to weekly, monthly or yearly perspectives). I also prefer to use Chiron and the four major asteroids, which further taxes the space considerations. In this example, I will not be excluding any of the planets, but you can, of course, modify the technique to exclude what you consider nonessential.

Which Aspects?

Another thing you can do is limit the aspects you track. The Llewellyn datebook uses the conjunction, opposition, trine, square, sextile, semisquare, sesquisquare, semisextile and quincunx (nine aspects total). For my example, I am using only the Ptolemaic aspects. You can also use different aspect sets depending on which planet is involved, since the transits need to be calculated in batches anyway. Thus, you could use an extended aspect set for the slow-moving planets, or do conjunctions only for the Moon, etc. This is probably the most important thing to consider when deciding how to limit your data.

Entering and Leaving Orbs

You can also choose only to list transits for when they reach partile. However, I like to include entering and leaving orbs at least for Jupiter through Pluto; in this example, I am using entering/leaving orbs for Mars and the asteroids as well. Using orbs for the Moon is certainly ruinous, and since the Sun, Mercury and Venus transits are rarely more than a couple of days, I have limited them to partile transits as well.

Astrological Fonts vs. Text

This does not save room for each daily entry per se, since it pertains to the length of each line, rather than the length of each column. Using the astrological font seems less cluttered for me, but you can choose whichever you prefer.

Phase One: Calculating Transits and Pasting into Excel

The first thing to do, of course, is to calculate your transits. This must be done in batches, for two reasons. First, there is a limit both to how many transits AstrolDeluxe can calculate at a time and to how many will fit on the clipboard. Second, you will need to calculate transits from April to October on Daylight Saving Time.

We'll begin with the Moon's transits. Bring up your natal chart in AstrolDeluxe. If you are using an astrological font, make sure it is turned on ("Customization/Use Astrological Font/On"). Now select "Options/List Transits and Progressions" (CTRL+T). For Starting Date, select 1/1/2003. Select the appropriate time zone for the native's location, making sure you have selected Standard, not Daylight Saving time. Now click "Customize Aspects" and apply your aspect orbs as required (I recommend that you save these aspects so you can reuse them later). The time period should be set to 1 month. (You can experiment to see how many months at a time you can calculate; it will depend on your aspect selection; also, you may exceed the clipboard limit when you try to paste into Excel; if so, you will need to paste in batches.) Select the Moon from the Planets list, and make sure you do NOT select the Moon from the Use Orb Planets list. Also, make sure you un-check the option "List Orb dates collated with Exact dates." I also recommend that you save these settings to use again (as well as for the other planets, as described below). Now click "Calculate."

From the resulting transit list, choose "Edit/Select All" (or CTRL+A), then "Edit/Copy to Clipboard" (or CTRL+C). Now switch over to Excel, and choose "Edit/Paste" (or CTRL+V). If you are using an astrological font, make sure to change the font for the entire sheet; this is done by clicking the little square above row 1 and to the left of column A, then choosing your font from the font list (this will need to be done for all other sheets in the workbook). You can change the column widths by selecting the four columns, and choosing "Format/Column/AutoFit Selection."

At this point, you should delete any rows for transits prior to or after the transit period. AstrolDeluxe first lists all house positions and any transits that are already in orb prior to the transit period-these always occur at midnight on the first day of the period; it concludes the list with any aspects that have not moved out of orb. If you choose, you could retain the ones before January 1 and after December 31, but you should definitely delete the relevant rows for the intervening months.

You should rename the worksheet "Moon" (right-click on the worksheet tab at the bottom and choose "Rename"). You should also save your work frequently, after each cut-and-paste operation.

Now you need to repeat the process for transit periods beginning 2/1, 3/1 and 4/1. Each time you select the transit list from AstrolDeluxe, simply paste it at the end of your Excel sheet. Be sure to delete any rows that are before or after the transit period, as well as the extra header rows, before you proceed to the next batch.

When you get to the transits for April, you'll need to do a little fiddling. First, you need to find the transit closest to 2 am on the first Sunday in April. If you don't have a calendar handy, you can also try this: select all of column A (click on the "A" column heading), then right-click and choose "Format Cells…" In the resulting dialog box, on the "Number" tab, choose "Custom." Now in the text box labeled "Type," enter the following code:

ddd, mm/dd/yyyy, hh:mm am/pm

This converts all of the date information in the column into a format like this example:

Sun, 04/06/2003, 12:57 AM

You can now locate the split between Standard and Daylight Savings time. Select ALL of the transit rows AFTER the last Standard Time transit and DELETE them.

Now re-run the April transits, choosing Daylight Time instead of Standard. Instead of selecting the entire list, scroll down to find the transit immediately after the last one listed on your Excel spreadsheet (they should be one hour different from each other). Select the REMAINDER of the list, and copy and paste as before.

If you know the date of the first Sunday in April, you can, of course, run these two lists by specifying the specific transit calculation dates. There will probably be overlap on the actual changeover-day itself; just make sure you haven't duplicated anything.
Continue this procedure all the way through October. For this month, you need to do the same thing as for April, but in reverse, finding the LAST Sunday in October and switching from Daylight Time back to Standard.

This completes the first phase of the project-for the Moon! In my example, this alone generates more than 2,000 transits! Now you need to do the other planets. It does get progressively quicker as you get closer and closer to the outer planets, so just be patient and diligent (it helps to have some strong Virgo or Saturn in your chart!).

For the Sun, Mercury and Venus, you can do things in larger time increments, or do the planets together as group, or both (again, save the transit settings for future use). I prefer to put them on a separate worksheet, which I rename "Sun_Mer_Ven." Once again, you'll need to make the necessary changes for April and October. Don't forget to SAVE frequently!

Using Entering/Leaving Orbs

Starting with Mars (or whichever planet you choose), I change the transit settings by selecting planets from the "Use Orb Planets" list. However, you still do NOT want to collate the orb dates with the exact dates. Save these transit settings, too. The slower speed of Mars reduces the number of transits considerably, but using enter/leave orbs adds a lot back again. The lists get shorter and shorter from this point forward, so you can calculate over longer stretches of time (but you still need to double-up for April and October!). I usually put Mars on a separate sheet, and then use another sheet for the four asteroids, one for Jupiter, Saturn and Chiron, and finally one sheet for the outer planets (six worksheets total). (Eventually, all of the transits will be combined onto a single sheet, but I think it's wise to leave the original calculations on separate sheets; if something goes wrong during the process, you won't have to recalculate them all over again.)

Once you have calculated all the transits for the entire year and pasted them into Excel, create a new (seventh) worksheet and name it "Combined." Now select, copy, and paste all of the data from each of the sheets one after the other onto this new "Combined" sheet. In my example, I ended up with a sheet that had about 3,400 rows. Now, if you want, you can sort this worksheet chronologically by choosing "Data/Sort…" In the resulting dialog box, make sure you tell Excel that your data has a Header Row (it should recognize this automatically if the headers are in BOLDFACE). In the "Sort by" list, select the column that includes the date and time information, and choose the "Ascending" option. When you click OK, it should have the entire spreadsheet sorted by date and time, from 1/1/2003 to 12/31/2003. Sorting the data is not necessary for importing the text into Outlook, but you may want it sorted for other uses.

Phase Two: Preparing the Excel Spreadsheet for Importing into Outlook

To save the Excel spreadsheet in a format that Outlook can import, as well as to simplify the data, we need to fiddle with the formatting. The "target" for these operations is a spreadsheet with five columns, each of which will be imported as a separate "field" into the Outlook calendar file: Start Date, Start Time, End Date, End Time, and Subject. Right now we have data that looks like this:

Date & Time	Orb	Transiting Aspect	Natal Aspect
Wed, 01/01/2003, 05:09 AM	0.00 (X)

But the format in Outlook will be more like this (leaving out the stuff after the @ and the Natal Aspect column):

[under Wednesday, 1/1/2003:]

If you are not using an astrological font, these examples will look like this:

Date & Time	Orb	Transiting Aspect	Natal Aspect
Wed, 01/01/2003, 05:09 AM	0.00 (X)	MOO (9) D SXT URA (8)	MOO --> URA 110° 18'

In which case, you might want it to look something like this in Outlook:

[under Wednesday, 1/1/2003:]
5:09 am Moon (9) D sextile Uranus (8) (X)

To achieve this, we need to do some text manipulation, some changing of cell formats, and (if you're not using the font) some Search-and-Replace procedures.

These are performed using predefined Excel functions and formulas. The ones I will use are as follows:

LEFT(cell reference, number of characters (including spaces)): (truncates the referenced cell according to the parameters of the formula)
RIGHT(cell reference, number of characters (including spaces)): (same as previous, but from the right rather than from the left)
LEN(cell reference): (returns the number of characters, including spaces, of the referenced cell)
FIND(text string, cell reference, starting position): (returns the position of a particular character or string within a cell)

Using these functions in combination, you can truncate a referenced cell according to the parameters of the formula. For example, suppose in cell C2 you have:

In the first blank cell of the same row, type the following formula exactly as it appears below:

=LEFT(C2,LEN(C2)-(LEN(C2)-FIND("@",C2,1)+1))

When you hit enter, the cell should look like this:

To extract the Enter/Exact/Leave symbol ("(X)," ">E>" or "<L<") from the Orb column, suppose in cell B2 you have:

0.00 (X)

In the next blank cell, you would type:

=RIGHT(B2,4)
(AstrolDeluxe generates this column with an extra space at the end,
which is why the second parameter is 4 instead of 3)

When you hit enter, the cell should look like this:

(X)

Finally, we want to put these two cells together in one string, for which we use the CONCATENATE function. Suppose, after the last two operations, cells E2 and F2 look like this:

(X)

In cell G2, enter the following formula:

=CONCATENATE(E2," ",F2)
(there is a space between the quote marks)

When you hit enter, the cell will look like this:

This is our target format. Instead of using three columns to do this, we could put it all in one cell, using the following (rather hard to read) giant formula:

=CONCATENATE(LEFT(C2,LEN(C2)-(LEN(C2)-FIND("@",C2,1)+1))," ",RIGHT(B2,4))
(don't forget the space between the quote marks!)

Fortunately, Excel allows you to copy this formula for the entire column without having to retype it! First, position the cursor on the cell containing the formula. Now scroll down to the last row of your transit list using the scroll bar. Hold down the SHIFT key and click in the last row of the same column. This should select the cell containing the formula and every other blank cell in the same column, up to the end of your list. Now from the drop-down menus, choose "Edit/Fill/Down." VOILA! The entire column now displays the entire transit list in the format we want. (As an alternate, you can position the cursor on the formula cell, and click on the little black square dot in the lower-right corner of the cell; holding the mouse button down, drag this corner point ALL THE WAY down to the bottom of your list.)

If You're Not Using an Astrological Font

If you're going to print your calendar with the "SUN (1) OPP MOO (3)" format, you have several other options for altering the appearance of this style. You may want to do a global search-and-replace procedure on the transit column, replacing "MOO" with "MOON" and "OPP" with "opposition." This can change the UPPERCASE to Title Case at the same time, if you prefer.

Clean-up and Rearrange

OK; the most complicated part is over. The rest is much, much simpler. At the top of your column of formulas, enter the heading "Subject" (while you're at it, make all of the column headers BOLDFACE).

Now we don't need the "Natal Aspect" column; so select the entire column, right-click your mouse, and choose "Delete." We can't delete the original "Transiting Aspect" column, but we don't need to see it, so select that entire column, right-click, and choose "Hide." Do the same for the "Orb" column. You should now have only two columns visible, "Date & Time" in column A and "Subject" in column D (which contains our text manipulation formulas).

Now we need to insert more columns for the date information. First, select the entire first column, right-click, and choose "Copy." Now right-click on the first cell of the next column and choose "Insert Copied Cells." Repeat this procedure until you have four identical "Date & Time" columns. Change their headings to read as follows: Start Date, Start Time, End Date, End Time. These are the fields that Outlook will look for when it imports the file.

Now we need to change the Date/Time format of these four columns. Select the entire first column, right-click, and choose "Format cells…" From the resulting dialog box, select "Date" from the first list. Then, from the list of options to the right, choose the format that looks like "3/31/1998." Click OK. All of the dates in this column should now be in the Month/Day/Year format.
Now select the second column; right-click, choose "Format cells…" and this time select "Time" from the first list. Then, from the list of options to the right, choose the format that looks like "1:30 pm." Click OK. All of the dates in this column should now display only the time, using a 12-hour, am/pm format.

Make the third column look like the first column and the fourth column like the second column. You now have five columns altogether, looking more or less something like this:

Start Date	Start Time	End Date	End Time	Subject
1/1/2003	5:09 AM	1/1/2003	5:09 AM
1/1/2003	6:23 AM	1/1/2003	6:23 AM
1/1/2003	6:23 AM	1/1/2003	6:23 AM
1/1/2003	10:39 AM	1/1/2003	10:39 AM
1/2/2003	1:55 AM	1/2/2003	1:55 AM

We are now ready to save the file in a format that Outlook can import. Save the original file first. Then choose "File/Save as…" In the resulting dialog box, change the "Save as type" option to "Text (Tab delimited) (*.txt)." Click "Save." When Excel warns you that it can only save the active sheet, click "OK" (but do make sure you actually have the appropriate sheet active!). When Excel warns you about features that are not compatible with the Text format, click "Yes." Now close the file (Excel will repeat these warnings, but you can just repeat the procedure until the file finally closes.)

Phase Three: Importing into Outlook

Now we are ready to import the TXT file into an Outlook calendar.

Start Outlook, and from your folder list, right-click on "Calendar;" choose "New Folder…" from the pop-up. Give your folder a name (like "2003 Transits" or the name of your client). The folder should contain "Appointment items" and can be placed wherever you wish.

Open the new Calendar folder. From the drop-down menu, choose "View/Active View/Current Appointments." Now we're ready to import!

Choose "File/Import and Export. This begins the Import/Export Wizard.
From the first dialog box, choose "Import from another program or file" and click "Next."
On the following dialog, choose "Tab Separated Values (Windows)" and click "Next."
In the next dialog, click on "Browse…" and navigate your way to the TXT file you just saved in Excel. For options, leave it set to "Allow duplicates to be created." Click "Next."
For the destination folder on the following dialog box, choose the calendar folder you just created. Click "Next."
There may be pause on the next step, but you should get a dialog box that says "The following actions will be performed:" followed by a checkbox and a phrase like "Import filename.txt into the Calendar Name folder." If it's not checked, check it. Now click on "Map Custom Fields…" The resulting dialog box shows you two columns, one listing the column headings from the TXT file, the other listing the available Calendar fields in Outlook. If you followed all of the instructions from the Excel section, Outlook should automatically map the fields for you. If not, click and drag the column headings from the left over to the corresponding field names on the right. There should be five fields total: Subject, Start Date, Start Time, End Date, End Time. Click "OK."
Ready? Click "Finish" and the conversion will begin. This will probably take a bit of time to complete.

VOILA! The Active Appointments should now list all of the transits from your TXT file. If you used an astrological font, it should automatically display that way, but if it doesn't, you can adjust the View settings manually under the "View" menu. You can also change to the Day/Week/Month view and see the transits listed on their appropriate days. You will notice that transits that don't fit within a particular day will be accessible with a little down-arrow at the bottom of each day.

Phase Four: Printing Your Datebook

Now for the final phase, printing your new datebook. There are a few things to consider at this stage. Creating a "folded booklet" is a special printing operation, in which all of the pages are specially paginated so that when you fold the booklet in half, the fronts and backs correspond properly. That is, on the first sheet of paper, one side will contain the first and last pages, and the other side will contain the second and second-to-last pages, etc. So double-sided printing is absolutely necessary.

If you have a printer that does double sided documents, you're in the gold. If not (like me) you have two options. You can print out all of the odd pages first, then replace the paper back into the tray and print the even pages on the other side. Alternately, if you have the software to create your own PDF files, you can print the whole thing using the Acrobat Distiller or PDF Writer as if you were printing it double-sided, and then take the whole thing to Kinko's or some such copy center and ask them to print it for you. Regardless of which option you use, I strongly recommend that you try it out first for 1 or 2 months only, to make sure you get it printed correctly (for example, to make sure you understand what direction the paper has to go back into the tray). Save a 1-2 month PDF version as well (if you use this method) and ask your printer to do a trial run with that before attempting to do the whole year.

In Outlook, with your new Transit Calendar folder open, choose "File/Print…" From the list of Print Styles, choose "Weekly Style." Below that, select your date range (either a 2-month trial run or the full year). If you're printing one side at a time, choose "Odd" from the "Number of pages" on the right.

Now click on the "Page Setup…" button. On the first tab ("Format"), choose "Top to Bottom" under "Options/Arrange" and "2 pages/week" under "Layout". Don't bother with the Notes or Task Pad areas, since space is at a premium. For the "Fonts" options, choose small but legible size in your appropriate astrological font. The Halloran font can go to about 7 pt.; but you may have to experiment to find something you like (another important reason to test it out for 2 months first!).

Now go to the second tab, labeled "Paper." Choose "Letter (8.5 x 11 in)" from the "Type" options. Under "Page/Size" choose "1/2 Sheet Booklet." The orientation should be "Portrait" (this may seem counterintuitive, but look at the little diagram and you'll see what Outlook means here). For margins, you need to consider how you are going to bind the pages. Are you going to hole punch them, or take them to a printer and have them spiral bound? The optimal space for this is probably 0.75" on the left, but you may be able to get away with 0.5" for spiral binding. Again, you may need to experiment. To line up the fronts and backs of the pages correctly, you should make the left and right margins identical. The top and bottom margins should be as small as possible; I use 0.3". (You can make the binding margin wider than the outer margin, but it takes a little experimenting. Most importantly, you need to reverse the Left and Right margins for odd and even pages, but which settings to use for which pages depends on the date range you choose. Using the Print Preview can help you figure it out without wasting paper, but I use identical Left and Right margins to avoid this hassle. The most important space considerations are for the top and bottom of the page anyway, so those should definitely be as small as possible.)

Under the "Header/Footer" tab, I usually leave the headers and footers blank, but you can put "Courtesy" information there, number the pages (I find page numbering confusing, but it can help in determining whether the booklet is paginated correctly), etc.

Now click "OK" at the bottom of the dialog box to return to the print menu. Double check your date range and the "Odd" pages setting. Click "Print!"

When the odd pages are done printing, take them out and put them back in the tray. Different printers require different ways of doing this, depending on how the paper is fed from the paper supply tray, what side it's printed on, etc. You should DEFINITELY experiment and make sure you know which way to put the paper back in, or you'll be wasting a LOT of paper!

Now go back to the Print… menu. Your "Page Setup…" options should have remained the same, but it's wise to double-check. The date range also needs to be identical to the first run, so change it if Outlook has reset it. Make sure to choose "Even" pages under the "Copies" options. When you've checked (and double-checked) everything, hit "Print."

When you're done, you should have a one-year datebook, ready to be cut in half and bound (or hole-punched), totally customized to track transits to the natal chart on a daily basis. If you'd rather fold the pages than cut them, you should probably print out only 3 or 4 months at a time, because of the "creep" effect when you try to fold too many sheets. This results in several smaller booklets that can be stapled along the spine and then, perhaps, bound together. I prefer to cut the pages (most printers can help you with this task as well; they have industrial-size paper cutters!).

This process may seem long, laborious and perhaps even confusing, but I really think the resulting product is totally worth it. Once you do it the first time, it becomes easier and easier. Good luck!