An easy guide with MO excel 2007, teaching you how to create an easy table that shows your investments, profits, % and much more.
Make a bunch of columns. I’ll explain each now:
should be easy
This is quantity, the number you have bought. Type it in manually.
Type in what you paid per item here.
It’s just the med price currently.
Fill this’n in after you’ve sold your stuff.
This is the increase from the purchase price to the selling price (or decrease, in unfortunate cases). In order to do this on excel, you must type this in the box: =if(e2=0,0,e2-c2) This can be applied down the column by clicking the bottom right corner of the first box (the cursor should turn into a plus) and dragging it down the length of the column. Fun stuff! (if you wanna know what all that stuff in the formula corresponds to, look at the notes section at the bottom).
This is how much you spent! This is gonna be =b2*c2. Apply this down the column, too. You can just go ahead and assume that you can do this for any formula. And you better, because that’s the last reminder.
This column is an indication of the net gain or loss if you sell at the current market price, and is (at least to me) an indication of the items current actual worth. You might wanna copy and paste this, its messy =if(e2=0,d2*b2-b2*c2,0) It will zero out when you sell (when you input selling price), because after you sell, the net gain from selling at market price is irrelevant. For the table colors, look into “conditional formatting”. Its pretty straightforward.
The best part of the spreadsheet! this is just =if(e2=0,0,e2*b2-c2*b2). It will remain zero until after you change the sales price. After entering the sales price… well I hope you know what earnings are o.o OH! As you may have noticed, this account and the net change account cant both be filled. This is useful in identifying which investments are still outstanding, and which ones have been cashed in.
Rate of return:
This is the percentage amount that you gained on the sale of your investment. OK, so not that crucial to keeping track of your investments, but I’m an accounting major with too much time, so sue me! (please don’t). This is =if(g2=0,0,i2/g2) In order to get it to display in the nifty way it looks in my spreadsheet, you also need to do some format changes. click the bottom right corner of the tab called “number”, then click “percentage” from the list. Then choose however many decimals you want displayed in the box. voila!
And that’s it! Now all you have to do is type in the name, qty, purchase/med price every time you buy stuff, and then the selling price upon selling, and it’ll do the rest!
OK I’m not really done… that was just the column stuff, and it’s not that great.
Make a table (3×3) or a new column section next to your first columns.
Here’s the run down:
All the money I’ve invested is recorded here. how? =Sum(G:G) nifty eh?
Self explanatory, I hope. =Max(G:G)
Total money earned:
Yep, the name says it all. =Sum(I:I)
Highest return $:
This is the highest profit from one investment. =max(I:I)
Highest return %:
This shows the highest percentage return from one investment. =max(J:J)
Total % yield:
This is the total return you’ve made in investments all together in %. =SUM(I:I)/SUMIF(E:E,”>-2″,G:G)this one was tricky, because outstanding investments contribute no yield, and need to be discounted. To discount the outstanding investments, this little bit of code looks at the sales price and, if there is one, sums the investment. If there isn’t one, it doesn’t sum it. Oh, and I guess it also sums the earnings and divides that by the total investments (outstanding investments excluded, as I’ve already mentioned). More on that in the notes, if you care.
Average % yield:
This is just the average of all the yield numbers. =averageif(J:J,”<>0″) Technically, it disregards all “0%”, which is intended to discount outstanding investments (which have a 0% default), but also discounts naturally occurring 0%’s; the ones that happen when you break even. Sorry.
Just the average of investments. Don’t worry, all unfilled investment boxes aren’t counted for the average. =averageif(g:g,”<>0″)
Average profit per investment. =averageif(I:I,”<>0″)
OK that’s it.
I’ve compiled some notes here, some are text-heavy, and most aren’t useful in keeping your merchanting spreadsheet up to date, but I’ve included them as a means to alleviate questions.
Here’s my notes:
- Turn off smileys before reading the formulas, I think some might produce smiles.
- When you make your own spreadsheet, (if you make your own spreadsheet note that the letters used in the codes were corresponding to my column and row choices, so if you decide to change the layout, even to make a smaller/ simpler spreadsheet, you’ll have to change around the letters in the codes to match your layout.
- When making your own formulas, you can speed up the coding by typing =, and then clicking individual cells that you’d like to mess with. You can even select more than one cell, if you’re in to that sort of thing.
- Columns go up and down
- Rows go across
- The colon symbol is used to specify a range within a row or column or blocked section of a spreadsheet. The box id in front of the colon is the top left box in the blocked section, and the box id after the colon is the bottom right box. Also, if you want to represent a certain blocked area of your spreadsheet without typing it out, you can select the area with your mouse as well, and, if you’re in the formula box, the block should be coded for you.
- averageif() is weird. I’ll explain it with this nifty example:
Range is the portion of cells you want averaged, and condition is the criteria that must be met for the number to be included in the average.
- If you’re wondering how if() works, wonder no longer:
“Statement” is/can be any mathematical equation or number (or, when dealing with words… a… word…). Value_on_true is the value that goes in the box when the statement is true, and Value_otherwise is the value when the statement is false.
- sumif() statements… ah… what fun. OK. there’s 3 arguments inside (if you don’t include a 3rd, the first argument is used as the third argument as well) a sumif(). range is first, and its going to refer to the block that will be TESTED. the second argument is the test. the tests can get pretty sticky, as computer code needs to differentiate between numbers and letters and all sorts of math mambo jumbo, so if you really wanna learn this, i suggest college=p. the third argument refers to the block that will be SUMMED. now, if data in the range argument passes the test argument (i.e. x=5, and the test is “>2), the data from the SUMMED block corresponding to this block will be summed. I should write textbooks.
okay I’ll illustrate:
we type: =sumif(B:B,”>6″,C:C)
b1=1, b2= 18, b3= 8
c1=2, c2=3, c3=5
b1<6, c1 is not summed
b2>6, c2 is summed
b3>6, c3 is summed
you should see 8 in the box with this formula in it.
in this case, the range and the summation range were different, which was what happened in the “total % yield” formula i used in my spreadsheet. However, you don’t always need to use a different range of cells. as seen with the averageif() usage in my spreadsheet, you can (well, in this case you’re averaging, not summing, but just let it go) just have one range that is tested, and, if it passes, the individual cell in the same range will be averaged, instead of a corresponding cell from a different range. err… yeah.
all of this last paragraph in 2 lines of code:
=sumif(range,criteria) just uses one range to test and sum.
=sumif(range,criteria,sum_range) uses 2 ranges, with the first range being tested, and the second being summed.
- Conditional formatting is spiffy. Select the cell block that you want to show conditional colors. Then click the conditional formatting button, and then the highlight cells rule button. Then, clicking one of the conditional buttons, you will see that particular conditions’ prompt window. All you have to do is put in a number to compare, and then the highlight color you want to show up on when the condition is met, and poof! Your cells are conditionally highlighted! My table uses greater than, =, and less than. The number I used to compare was… err… 0.
- I say these things for clarity, not cuz Ithink you’re stupid
- I might also think you’re stupid
- I typed this in its entirety using only my right hand, so cut me some slack would ya?
feel free to add your own spin to your spreadsheet, and lemme know about it! I want the best spreadsheet i can have, and any feedback would be greatly appreciated!
WELL! I hope you learned something. If not, why’d you read this? There’s plenty enough critics in the world already!