Saturday, 18 July 2015

Excel to Help Your Game

As promised, I’m taking July and August off from running a campaign, and putting to use the time I would otherwise spend on preparing adventures, to tweak, revamp, and streamline some things that will improve the game when we start playing again. The first of these is a better way to keep track of the weather and date compared to what I had before, which wasn’t much. 

I had some simple weather tables in Excel running really simple code, but what I really needed was a comprehensive spreadsheet that would have all the information I needed right in front of me: Not only the current date and basic overall temperature and whether or no there would be precipitation on a particular day, but that would allow for temperature fluctuations throughout the day, tell me precisely when it would start raining, how strong the wind was and from what direction, what the sky overhead looked like, whether there was fog, how humid it was, and—perhaps best of all, because the party is bound to attempt sooner or later to do something by moonlight, and because there might eventually be werewolves afoot—what phase the moon was in. 

Finally, having just this morning finished coding a spreadsheet that does all of these things, I can see with 20/20 hindsight what an absolute fucking mess it has been running without it. 

Shropesyre Campaign Weather and Time.xls

The screen capture above shows a not atypical day in the West Midlands of England—this particular one 24 March 1377. The 24-hour period is divided into seven sections, beginning around sundown, because medieval custom recognised this as the beginning of the following day. It’s cold and cloudy but dry from evening onward, the gathering clouds providing a light rain around daybreak, with the skies remaining threatening and bringing a thunderstorm at the warmest part of the day, which quickly passes and leaves a clear blue sky for the remainder of the day, bringing the humidity down sharply by evening. 

Incidentally, one of the obvious bad habits players bring to the game, living as they do in the 21st century, is asking what ‘time’ it is. If I have anything to say about running adventures in 14th-century England, players  should be able to get used to thinking of a day in terms of prime, terce, nonce, sext, and vespers, and night as that nebulous period of darkness when they’re supposed to be asleep. (That last bit alone is enough of a challenge; I don’t believe most of us can really imagine what it’s like to live with no electricity at all.) Considering the relative number of daylight hours this time of year, we can predict that an adventuring party might typically spend the time from prime to terce breaking camp and getting ready to get back on the road—less time if they’re traveling light and don’t have any horses to equip—they would understand late morning as the period between terce and nonce, with midday being from nonce to sext, and that they would have until vespers at the latest to find lodging in a town, especially if they wanted to make the evening church service and enjoy a meal before the town gate closed at curfew. They would have little use for knowing ‘time’ beyond these designations, and even if they happened to be carrying an astrolabe few NPCs would ask them what o’clock it is. 

Now then. You can find a brilliantly detailed Climate Generator at Tao-of-DnD Wikispaces, but I’m finding that, both in my immediate acquaintance and in descriptions floating about the cyberworld of the efforts of DMs and players, there’s an awful lot of people who are either too intimidated by Excel to do anything interesting with it, or who just don’t grasp how much it can improve their running. (Ideally, every player would bring his own laptop and constantly update XP, damage, equipment including encumbrance calculated automatically and so on, but as it is my campaign still mostly uses paper. I normally have two computers running: a desktop on which is displayed images relevant to the players, and a laptop on which I privately view area maps and other information to which the players do not have access.) I’m going to devote this post, then, to the utility of this resource, and break down some of the code and explain what it does, for the benefit of DMs who think they might like to build their own spreadsheets for their own worlds but don’t know exactly how. 

First, the absolute easiest and most reliable starting point for a game setting is the real world, because there’s plenty of information online that tells us what climate any given area has and why; and since we have one moon on a predictable cycle, this is a snap to program in. Of course, any fantasy world can be programmed as well, but the DM will have to be a bit more creative in coming up with astronomical features for the planet and its satellites, climate for different areas, and so on. (Or he could just say screw it and decide all weather events, lunar cycles, and the like by fiat, which sucks.) 

In my case, I picked up information for modern Shropshire on high and low extremes of temperature for each month of the year, typical wind directions by season, amount of precipitation by month, variations in humidity, and chance of fog. With all that information in the top half of the screen, the rest is a matter of getting things to change in a reasonable manner over the course of a given day within a given month. 

The moon is a separate matter, and it’s as good a place as any to show how Excel can give you the phase for a particular calendar day. I’m going to try to explain this in simple English rather than Computerese, because the amount of questions I get at work from people completely befuddled by far more elementary operations tells me that most people go through life without using this software very much at all. So. We need a date for the last full moon, and I want to put that off to the side in cell K26. (There are plenty of web sites offering conjectures or formulas for discerning this sort of information for any date in history; I don’t need to link to any particular one. Suffice it to say 24 March is the current full moon. I need cell C15 to show the current moon phase, C17 to show the date, and B17 to show the day of the week. 

In order to get the day of the week to display correctly, we need a year close enough to a modern year that Excel won’t completely screw it up. My sources tell me that, coincidentally, the weekdays of 2015 match those of 1377, so I don’t have to do much other than format the cells to show the date without the year. Then we want cell B17 to show the day of the week based on the date in C17, so we enter the command:


…with the first word required because by default the day of the week would be displayed only as a number. Now, to display predictions for the next full moon in cell I15, we need to link to the date in K26 and add 29.53 days, the number of days the moon takes to complete a cycle. So we use the following code:


I also want the date of the next new moon displayed for convenience’s sake, so for the formula cell C15 I divide the lunar cycle in half and use:


To get the moon phase in cell C15, I need to compare the date of the next full moon in I15 with the current date in C17. The code is a bit longer here, and requires multiple conditions. I want eight different moon phases, but Excel is limited to seven nested ‘IF’ functions, so I make the ‘Quarter’ phase the default since it occurs twice. Somewhat arbitrarily, I’ve made the moon full either three days before or twenty-eight days before the next full moon. Afterwards I set each phase to occur within a specified range of days. I’m fond of writing code, like my blog, in whatever text editor I happen to have open so that I can catch mistakes, so in MemoPad it looks like this: 

Moon phases Excel code

Most other formulas on the page operate similarly. Most of the cells are linked. For example, cloud level at each part of the day is partially contingent on whether it’s going to rain in the next part, so that the party gets at least some warning before they get soaked. Temperature is set to generally be highest at midday and lowest at midnight. Since the high and low temperature columns in the upper half of the sheet are already randomly generated, cell D22 takes the high temperature number from C4, D19 the low temperature number from D4, and the temperature cells for the other parts of the day are set to fall within a range that rises after the wee hours and falls after sext—usually. I did throw in a trick that can sometimes change this pattern, but I’m not going to reveal it here because I want players to be genuinely surprised when a warm or cold front comes out of nowhere. 

Humidity varies within a range by month, with the exception that when it’s raining humidity is always 100%. Wind is coded to follow the patterns listed in column E, ‘Typical Wind Origin’. This means that when there is reasonably strong wind, it tends to blow from the directions listed. Gentle breezes may be more random in direction, and ‘Calm’ conditions always default to no direction. 

Lastly, the ‘Chance of Fog’ percentages in column I are taken to mean the percent change that fog can occur at some point on a given day within the month listed. These percentages are used to link random d100 rolls at the right end of row 15, where, for example, rolls of 20 or lower result in fog for a day in March, with lower numbers indicating thicker fog. For simplicity’s sake, fog is assumed to last all day, but if this should require more gradations when we actually start playing, I can fine-tune this aspect later. 

There’s plenty more happening randomly at the right side of the sheet, but to explain everything would be superfluous. Before closing, though, I would like to call attention to the sections for notes, particularly at the bottom of the page, where I hope all the important information will be at hand for the following session—including the upcoming feast and fast days listed in the Shropesyre calendar, and which markets and festivals are happening in nearby municipalities—because it’s been in different files up to this point and things inevitably ended up getting lost or overlooked. I’m hoping that this will be enough to get the party off to a smooth(er) start at the beginning of each session. At any rate, this will free me up to just think about…well, everything else that hasn’t got a spreadsheet yet. 

If anyone wants more detail about code, I’m happy to answer any questions. If anyone already making good use of spreadsheets wants to tell me some ways to make mine better, I’m happy to hear that, too. 

No comments:

Post a Comment