Updated Rally Planning Spreadsheet - UPDATED

Greg Rice

Premier Member
IBR Finisher
#1
New update at the bottom of the thread.


I have updated my rally planning spreadsheets for 2023. I made changes to improve performance and reduce by 50% the calls to the Google map API’s for time zone and geo coding.

I also changed the way to mark a bonus as a combo bonus. I moved the list of combo bonus names to a separate worksheet so I could add more combo bonuses.

Please take the time to read how the spreadsheet works and the help file, link below and in the spreadsheet. Use the two links below to see how the spreadsheet works.

http://www.gregrice.com/rallys/rallys.html

http://www.gregrice.com/images/Bonus-prep-greg-rice-information-2.jpg

Here is a link to the updated rally planning spreadsheet.
http://www.gregrice.com/2023ibr/2023-IBR-Bonus-Prep-V3H-Bonus-Points-Import-P.xlsm

Post questions or comments here.
 
Last edited:

Jchauncey

Premier Member
IBR Finisher
#3
Thanks Greg! I have a question, and I'm sure it's just my ignorance. I can't get macros to work even though I have went in and enabled all macros. What am I doing wrong?
 
Last edited:

Greg Rice

Premier Member
IBR Finisher
#4
I believe on some Windows computers there is a new feature that blocks macros when you download a file from an unknown source.

Go to the folder where the spreadsheet is and right click and select properties.

Look at the image and you should be able to unblock macro.

EECFF194-6DB6-40A6-96BD-DB8C9467449E.png
 

Jchauncey

Premier Member
IBR Finisher
#7
I have had some reach out to me and ask for more help / information on using my rally planning spreadsheet and I provided them with the link to an online seminar I did. It is 3 hours long :eek: and has a lot of information that should answer most questions.

You can skip around the video as needed.

I watched all 3 hours last week. It’s very informative and worth the time!
 

Greg Rice

Premier Member
IBR Finisher
#8
I was asked by a couple people what is the key benefits of my spreadsheet. So I thought I would share this with everyone. Much of this is on my website also.

Easy to import bonus and points files from a rally master and automatically create a GPX file ready for Basecamp or a GPS.
Easy bonus name creation with bonus code, points and availability.
Automatic calculation of sunrise and sunset based on latitude and longitude. This is good information for daylight only bonuses.
Easy to tag a bonus availability, anytime, daylight only, group photo, etc…
Easy to tag a bonus as part of a combo bonus.
Bonus automatic symbol selection based on points value and bonus availability.
Easy to add comments to a bonus that is imported into Basecamp and then into your GPS so you can see the comments when riding up to a bonus.
Google map link created of the bonus location, you can take a look at the bonus in street view in many cases.

Generates a formatted GPX file for use with Basecamp and or a GPS that includes comments, sunrise and sunset times.
Fast entry into Basecamp with the GPX file that is generated from the spreadsheet with bonus symbols based on point value and availability.
You can also load the GPX file directly into a GPS.

Here are some screenshots showing the benefits of my spreadsheet.

Tagging a bonus with a combo bonus code. You can tag a bonus with multiple combo bonuses.


Adding comments to a bonus that carries over to Basecamp and a GPS.



Formatted bonus data in the spreadsheet.



Bonus comments, sunrise and sunset in Basecamp.


How comments, sunrise and sunset look in the GPS.



Comments and sunset also show up on the GPS in the favorites / saved places list. This makes it easier to see when routing to a bonus.



When you tag a bonus to a combo bonus I add that to the GPX file category tag and you can see that on the GPS under saved places. All bonsues tag with a combo bonus then show up under that category ( combo bonus ). This helps keep you from forgetting what bonuses are needed to score that combo bonus.
 

Greg Rice

Premier Member
IBR Finisher
#9
So, I have been doing a little work on my two rally planning spreadsheets and I decided to combine them into one spreadsheet to make it easier to use. No need to have to open two spreadsheets.

My second spreadsheet helps me identify how many riding hours and miles I have available in each leg of a rally. It also helps me understand how many bonus points I need to score in a leg to be a finisher based on the information from the rally master of target points for the leg.

My new version of my spreadsheet now has four ( 4 ) worksheets in one spreadsheet. See the screenshots below showing the 4 worksheets in the RED box.

The two new worksheet tabs have help in the worksheets to understand how to use them and what data needs to be entered. They are both pretty easy to understand.

I added some new functionality to my hours and miles planning worksheet to automatically calculate time zone differences in a leg. Say you start on the east coast and the leg ends on the west coast which is 3 hours difference. Now my spreadsheet automaticlly adds 3 hours to the leg hours. If I was riding the other direction, it will automatically subtract 3 hours from the leg time.

This really helps me so I don't have to try and figure that out when I am tired as time zone differences are hard, but they can be confusing when you are tired. I do this by getting the latitude and longitude of the start, checkpoint and finish of a rally with my developer API. Just enter the city and state and it will get the latitude and longitude. Then I take the latitude and longitude to calculate the time zone the city is in again with my developer API. With this information I and the start and stop time of a leg I can calculate how many hours there are in each leg of a rally. No longer do I make mistakes figuring out time zones and if I need to add or subtract hours from the leg.

The link to the new version of the spreadsheet is at the bottom of this post.

Screenshot showing the worksheet tabs in the red box. Click for larger version.



Hours and miles per leg planning worksheet. Click for larger version.



Worksheet for points required per leg planning. Click for larger version.



Screenshot showing the context help for cells in the worksheet. Just roll your mouse over a cell and it will pop up with some instructions.
Click for larger version.





Link to the new version of the spreadsheet.
http://www.gregrice.com/2023ibr/2023-IBR-Bonus-Prep-V3H-Bonus-Points-Import-P.xlsm
 

Jchauncey

Premier Member
IBR Finisher
#12
Greg, C2,3, 4, & 5 on the hours/miles sheet appear to be protected and cant be edited.
Thanks again for the work you're putting into this, and for sharing this valuable tool.
 

Greg Rice

Premier Member
IBR Finisher
#13

RobG4

Premier Member
IBR Finisher
#14
Greg, using a Mac, I'm having trouble. On the Bonus Prep tab, the "Clear Bonus Data from Green Section" button at I8 works just fine. But when I go to "Import Bonus Data from GPX File," I get a Runtime Error 1004. Also happens when I try to import the bonus points data.
Any idea what's causing that?
Thanks
 

Greg Rice

Premier Member
IBR Finisher
#15
Greg, using a Mac, I'm having trouble. On the Bonus Prep tab, the "Clear Bonus Data from Green Section" button at I8 works just fine. But when I go to "Import Bonus Data from GPX File," I get a Runtime Error 1004. Also happens when I try to import the bonus points data.
Any idea what's causing that?
Thanks
Hi Rob, I have had others with the same issue on Mac's. I don't have a Mac so I am not sure what the issue is. I will take a look again later today after work and see if I can find the issue.
 

RobG4

Premier Member
IBR Finisher
#16
Hi Rob, I have had others with the same issue on Mac's. I don't have a Mac so I am not sure what the issue is. I will take a look again later today after work and see if I can find the issue.
Thanks for looking into it. I have my Mac partitioned with Windows as well, so I may have to boot to Windows and use your spreadsheet, upload the new gpx file to the cloud, then switch back to iOS for Basecamp, etc. But I do appreciate your work on that beast of a spreadsheet. Thanks.
 
Last edited:

Greg Rice

Premier Member
IBR Finisher
#17
Thanks for looking into it. I have my Mac partitioned with Windows as well, so I may have to boot to Windows and use your spreadsheet, upload the new gpx file to the cloud, then switch back to iOS for Basecamp, etc. But I do appreciate your work on that beast of a spreadsheet. Thanks.
Rob, I think I found the issue and will try and fix it tomorrow and get you an updated version.
 

RobG4

Premier Member
IBR Finisher
#18
Rob, I think I found the issue and will try and fix it tomorrow and get you an updated version.
Greg, thank you. One other thing that may be an issue for Mac users. The export folder path for the new GPX file. I think that cell may be formatted to only accept a Windows path, ie C:\2023ibr. The Mac path doesn't go to a C drive. My path looks like this /Users/robgriffith/Desktop/GPX Files. Is that cell looking to start with "C:" and if not, it throws an error?
 

RobG4

Premier Member
IBR Finisher
#20
@RobG4, did you allow macros to run in Excel for this spreadsheet?

Can you take a screenshot and post here or email to me?
Yes, I did allow macros. I'll be glad to send you a screenshot once I get home this evening. I certainly appreciate you looking into this for me.