Excel File Generates Shop Inventory For You

By Jacob Valdez

Download Jacob's ShopKeeper Excel file
Download Jacob's ShopKeeper Excel file

As a player and a GM, I’ve always felt it would be a little easier to suspend my disbelief if sometimes it wasn’t possible to get a particular item in a shop, or if the prices were different from town to town. The problem is this isn’t easily accomplished on the fly – most GMs in my experience simply go with the book prices as written. For some groups, though, the haggling to get a good deal can be an interesting roleplaying encounter in itself.

As such, I put together a spreadsheet listing all the mundane items in the D&D3.5e Player’s Handbook, and for flavor, the contents of the three “Good Baron Dave’s” sundry netbooks. You simply enter the size of town and the size of the shop.

The generator then takes into account:

  • The gold piece limit of the town
  • The likely inventory level depending on the size of the shop
  • A price that may be higher or lower than the book price (keep your players on their toes)
  • The relative rarity of the item

I set an arbitrary rarity for my game world, all formulas can be adjusted (I only made the text white to hide the intermediate calculations).

Load the page (or reload [F9] to get new values), and the quantity on hand and selling prices are determined. You can then cut and paste the table onto a new page (Special > Only Values, so it doesn’t copy the formulas). You can then sort and remove items that have no inventory or the shop doesn’t carry.

With this tool, you can easily copy the entire worksheet and have different settings for item rarity for different regions in your world.

I hope you or other GMs might find this useful.

Thanks for writing such a great newsletter!

[Johnn: thanks for the awesome tool, Jacob!]

Readers, download Jacob’s Shop inventory .xls file.

  • http://www.notsounwashed.com/ Tim

    Hi Jacob/Johnn – the download link at the bottom is not working, unfortunately!

  • http://www.roleplayingtips.com Johnn

    Hey, thanks Tim! all fixed. Please try again.

  • alan

    The file doesn’t seem to DO anything. All I get is the item data and four columns of #NAME errors?

    • http://www.roleplayingtips.com Johnn

      Hi Alan,

      Do you have macros enabled? Also, the file opens to worksheet #2. Change to worksheet #1.

      • Arbanax

        Hi John same for me, I tried to enable Macros But I couldn’t find if I had them on or not. I’m using office 2003 so I don’t know if that is the problem. I don’t know my way around excel at all so any tips of what to do would be appreciated as it is the thing is just a list with nothing else going on!

        Thanks

        Ab

  • Jacob Valdez

    I’m not sure offhand what could be causing a bunch of name errors, as I’m unable to replicate the problem. The file was originally exported from OpenOffice, so downloaded the file on a separate machine, into Excel, to see if that was the problem, and it came up normally. I’m re-saving the file as Excel 97-2003 format .xls and re-sending it to Johnn.

    The spreadsheet is dependent on several formulas, which I’ll note here — you may want to go select all the cells, and format them so the font color is black for all of them — this will let you see the results of the intermediate calculations.

    In Row 2, columns C through I, I have the gold piece limit of the towns, expressed as copper pieces (to have a lowest common denominator for price calculation)

    In cell A3, the formula just picks the number out of the gold piece limit cells. the formula should read as follows:
    =IF(A2=1,C2,IF(A2=2,D2,IF(A2=3,E2,IF(A2=4,F2,IF(A2=5,G2,IF(A2=6,H2,IF(A2=7,I2,IF(A2=8,J2,””))))))))

    Cells C5 through F5 are an arbitrary die type, to figure up the base inventory.

    Cell A6 is a formula that picks up the number from the die type cells. It should read as follows:
    =IF(A5=1,C5,IF(A5=2,D5,IF(A5=3,E5,IF(A5=4,F5,””))))

    Column I (from Cell I10 and down) assigns a number to the item rarity. It should read as follows, adjusted for row number:
    =IF(B10=”C”,2,IF(B10=”U”,5,IF(B10=”R”,7,IF(B10=”E”,9,””))))

    Column H (from Cell H10 and down) rolls the die type for the shop size, and subtracts the rarity value. The dollar-signs where it refers to cell A6 are to make sure it didn’t auto-adjust the row for that cell when I was copying the formula to the rest of the rows. This formula should read as follows (adjusted for the row):
    =RANDBETWEEN(1,$A$6)-I10

    Column G (from row 10) comes up with a random percentage, adjusting it by the size of the city, and multiplies it by the base price. It should read as follows (adjusted for the row):
    =ROUNDUP((RANDBETWEEN(6,21)-ROUNDUP($A$2/RANDBETWEEN(1,3),0))/10*D10,0)

    The Selling Price Column checks the value in column G. If it exceeds the gold piece limit of the town, it is marked as “Not for Sale” It should read as follows:
    =IF(G10>$A$3,”Not for Sale”,G10)

    Finally, the Quantity in Stock column checks the inventory determined in Column H, as well as the Selling Price. If the value in column H is 0 or a negative number, this field is left blank. Likewise, if the item was marked as Not for Sale for a price that was too high, the field is left blank. Otherwise, it copies the value in column H.
    =IF(H10<=0,"",IF(F10="Not for Sale","",H10*$A$2))

    I hope this solves some people's problems, and also shows where you can make adjustments and tweak the results to suit your campaign. What I uploaded here was a starting point, meant to be adjusted to your preferences. Currently, I'm adding the Gems, magic items, and such that would be available for sale in different kinds of shops into mine. I also tweaked it a little so the quantities aren't quite so uniform. If people are interested, I'd be happy to upload the final result when I'm done with it.

  • Jacob Valdez

    Okay, I think I found the #NAME error problem — my version of Excel (and apparently OpenOffice) have a function called RANDBETWEEN, which returns an integer between two numbers. This was much easier for me to work with than the older RAND function, which returned a value between 0 and 1, and I would then have to convert it in the formula to an integer in the range I wanted.

    Programming jargon aside, this problem is noted in the Excel help here: http://office.microsoft.com/en-us/excel-help/randbetween-HP005209230.aspx

    It says you just have to download the Analysis ToolPak add-in. This page explains how to get it:
    http://office.microsoft.com/en-us/excel-help/load-the-analysis-toolpak-HP001127724.aspx?CTT=1

  • http://www.notsounwashed.com/ Tim

    Thanks guys, download is working now! I had no difficulty getting the file to work either, using the latest version of Microsoft Excel.

    Cheers!

    • http://www.roleplayingtips.com Johnn

      Great to hear. Thanks for letting us know, Tim!