Trading Post Portfolio Spreadsheet

My current market portfolio, complete with pie charts.

Today I would like to talk a little bit about portfolio management. This is something that I just started doing thanks to Wintersday. I wanted a way to quickly at a glance see how much my on hand inventory is worth, how much I can expect to make selling at current market values, and how much I have made from sales to date. 

To solve this problem, I did what anyone would, I made a spreadsheet. You too an make a spreadsheet or you can use mine if you would like. Either way having a spreadsheet like this is very helpful in making decisions on the fly. It also help you track your success in the market, such that you can determine where to reinvest your profits. You could probably even use accounting software such as Quickbooks or Simply Accounting.

Using my Spreadsheet

To access my portfolio spreadsheet click HERE. The copy I have shared is not editable, so you will need to make a copy for yourself. Select File > Make a copy.... This will save a copy into your Google Docs folder which you will be able to edit. If you prefer to work in Excel or a similar program offline than select File > Download As > Excel Spreadsheet.

Select File > Make a copy... to add a copy to your Google Drive.

My spreadsheet is laid out across multiple sheets. You can see along the bottom that there is one sheet called, "Portfolio," and then each product is on its own sheet. This way you can track everything to do with a specific product on one page, and your overall portfolio on another. There is also a sheet titled, 'Blank Product,' which I use as a template when adding new products.

This spreadsheet does require some manual work to link the portfolio sheet to the product sheets. To make linking the portfolio and products easy for you I have colour coded the cells that need to be linked together. Thus, a green cell on the portfolio page needs to be linked to the green cell on the product page. Similarly for the yellow cells and the blue cells. There are instructions in the spreadsheet on how to do this. Also, I have set up six examples to help you get started. Below, I also have instructions walking you through the process of adding a new product.

The pink cells need to be filled in manually by you the user. Pink cells are for the current market price of the product. Simply go to the Trading Post in game, determine the current value you could sell a good for, and enter that number into the pink cell.

Adding a new product

If you wish to add another product you simply start by creating a new sheet,

The new sheet button.

Next ensure you title the sheet with the name of the product by using the renaming option,

The rename option for a new sheet.

In this case, Sample Product 6,

The rename dialog box.

Next open the Blank Product sheet, and press CTRL-A to select the entire contents of the sheet then press CRTL-C to copy the selected contents.

Open the Blank Product sheet I have already made and copy the entirety of its contents to the clipboard.

Then navigate back to the new sheet you just made (I titled mine "Sample Product 6"), select the upper left cell (A1) and press CTRL-V to paste the contents of the "Blank Product" sheet into this new product sheet. Your new product page should now be filled in a ready to go. You should see a purchases section and a sales section now.

Return to the main portfolio sheet. Select the next available row (in my case row 7) and select the only green cell in that row (in my case cell A7) and enter the following formula without the quotation marks, 

='Product Name'!A1

Replace 'Product Name' with the title you gave the new sheet which in my case is 'Sample Product 6'. If you entered the formula correctly the cell should now say, "insert your products name here." If you go back to the sheet you just made you will notice it says the same thing in cell A1. Replace the contents of cell A1 on your new product sheet with the name of your product. Now when you go back to the portfolio sheet you should see your product name in the cell you just changed the formula on.

You should see this if you entered the formula correctly. Be sure to replace
 'Sample Product 6' in the formula with the title you gave your brand new sheet.

Next select the first yellow cell in the B column. Enter the following formula into that square,

='Product Name'!B3

Again, replace 'Product Name' with the title you gave the sheet which in my case is 'Sample Product 6'. Now to auto-fill the yellow cell to the right, simply select the yellow cell you just filled out, grab the square in the lower right of the cell, and drag it over one cell to the right,

Drag the highlighted square to the right one cell.

This should automatically put the following formula in the yellow square in the C column,

='Product Name'!C3

Finally, scroll to the right until you find the blue cells. Select the blue cell in your new products row under the K column and enter the following formula,

='Product Name'!G3

Again replace 'Product Name' with the titled of your new product sheet. Now use the same auto-fill technique described above to auto-fill the contents of this blue cell to the blue cell to the right. It should auto-fill the following formula into that cell,

='Product Name'!H3'

That's it! That is all you have to do to add a new product. 

Adding new rows to portfolio

If you run out of rows on the portfolio page follow these steps to add a new row. Start by right clicking on the bottom most row (row 15 by default) and select 'Insert 1 above'. 

Insert 1 above option.

A new blank row will then be inserted above row 15. It is important to ensure the new row is added between our upper most row (2 by default) and the lower most row (15 by default) to ensure that the formula are preserved. Next select the row above the new row you just inserted (in my case row 14). Select the entire row from column A to column O. For me these are cells A14 through O14.

Select the entire row above the one just inserted.

Next, grab hold of the square handle in the lower right of your selection and drag it down one row to auto-fill everything down one row.

Use auto-fill to pull the old row down into the new row.

That is it! The auto-fill automatically fills in all the formula used to calculate the non-user defined cells. Now you just need to follow the steps outlined above to create a new sheet and link the new sheet to the main portfolio page. You can continue adding as many rows as you require.

Day-to-day Use

I have described above the procedures you must follow to add a new product to the sheet. You will not need to do this very often, and once you have done it once you will be able to do it again easy enough. 

Day-to-day use of this sheet is even easier. On each product page all you need to do is enter the quantities you bought and the price you paid into the purchases section. When you make a sale, you do the same thing in the sales section. If you ever need to add more rows to either section simply use the exact same process used to add a new row to the portfolio page.

Other than that you just need to keep the pink cells on the portfolio page up-to-date with the data directly from the trading post. Using this the spreadsheet will predict your expected profits.  As for reading the spreadsheet and understanding all the columns, I will leave that for tomorrow. For now I hope you can find some use out of my spreadsheet, or at least I hope I have inspired you to create your own spreadsheet which will track similar things.