Initial Setup - Harry's Little Corner of the Internet



Table of Contents TOC \o "1-3" \h \z \u Initial Setup PAGEREF _Toc282592514 \h 2Installing the SMF Add-In PAGEREF _Toc282592515 \h 2Starting from scratch PAGEREF _Toc282592516 \h 2Upgrading from a previous spreadsheet PAGEREF _Toc282592517 \h 3General Usage PAGEREF _Toc282592518 \h 3Cash PAGEREF _Toc282592519 \h 3Switching Option Price Source PAGEREF _Toc282592520 \h 4Compare Performance to DOW and S&P PAGEREF _Toc282592521 \h 4Using Targets PAGEREF _Toc282592522 \h 5Using the Log PAGEREF _Toc282592523 \h 5Using the Notes sheet PAGEREF _Toc282592524 \h 6Viewing Portfolio Progress\Status PAGEREF _Toc282592525 \h 6Returns at a glance PAGEREF _Toc282592526 \h 6Top Gainers\Losers PAGEREF _Toc282592527 \h 7Portfolio Weights and Daily Change PAGEREF _Toc282592528 \h 7Preparing for upcoming expirations PAGEREF _Toc282592529 \h 7Using the TradeStats spreadsheet PAGEREF _Toc282592530 \h 9Adding a trade PAGEREF _Toc282592531 \h 10Steps to add a put PAGEREF _Toc282592532 \h 10Steps to add a Covered Call (or the call portion of a diagonal) PAGEREF _Toc282592533 \h 11Steps to add a Bull Call Spread PAGEREF _Toc282592534 \h 12Steps to add a Bear Call Spread PAGEREF _Toc282592535 \h 13Steps to add a Straddle (or Strangle) PAGEREF _Toc282592536 \h 14Steps to add a Synthetic Long PAGEREF _Toc282592537 \h 15Steps to add a LEAP (or any plain long call) PAGEREF _Toc282592538 \h 16Steps to add a Stock PAGEREF _Toc282592539 \h 16Adding Cash to account PAGEREF _Toc282592540 \h 17Adding Dividends PAGEREF _Toc282592541 \h 17Closing a trade PAGEREF _Toc282592542 \h 17End of Year Update Steps PAGEREF _Toc282592543 \h 18Initial SetupThis spreadsheet uses macros. When you first open it, it should ask you if you want to allow macros in some way. If you don’t want to enable macros, then most of this spreadsheet won’t work.Installing the SMF Add-InThis spreadsheet uses the SMF Add in to enable pulling quotes directly from Yahoo: – I’ve attached the latest copy of the .zip file to this package, so you don’t need to download it if you don’t want to. Just use the copy I’ve included. Extract the .zip file out (to somewhere like c:\SMF Add-in)Then you just need to tell Excel where it is - under File -> Options -> Add Ins tab, and then click on the Go button at the bottom, next to Manage Excel Add-Ins, and then click browse, and search for c:\SMF Add-in\RCH_Stock_Market_Functions.xlaStarting from scratchIf starting from scratch, on a clean sheet (as provided in this package), you will need to delete that ‘upgrade’ button. In order to remove the Upgrade button, simply perform the following.To remove the button when not upgrading, right click on the button and select Cut.Now begin adding activity with the various buttons on the Summary tab.Upgrading from a previous spreadsheetOpen the new spreadsheet and click the Upgrade from Old Spreadsheet button.Find and select your previous spreadsheet.Click OK to upgrade.This will copy all user entered information to the new spreadsheet, leaving the old spreadsheet untouched. Once complete, verify that both spreadsheets contain the same information. Once you a confident that it upgraded correctly, switch to using the new spreadsheet.General UsageCashOn the Summary tab, in the bottom left corner is a blue section that contains Net Worth, Total Stock, Total Cash, and so on. You can periodically enter your current cash available in your account into the dark blue box as it changes (when you buy/sell something, deposit or withdraw money, receive dividends …)If you choose to enter your Total Cash in the dark blue box, and have entered all the rest of your trades into the spreadsheet, then the Net Worth and Available funds should be accurate. Otherwise, they will be inaccurate. This won’t affect anything else in the spreadsheet, so you only need to do this if you want the Net Worth box to be correct. This is one of the values logged, if you use the Copy to Log function, and that in turn is used for comparing performance to the DOW and S&P, but if neither of these are important to you, then there is no reason to keep updating the Cash amount.[Note from HG: This statement is slightly incorrect, as the ‘Cash’ value is also used to calculate the Net Asset Value on, among other places, the ‘Cash’ tab. If the ‘Total Cash’ amount is incorrect this will impact calculating the NAV, as well as the number of ‘shares’ of the account involved in any cash transaction. I’m going to work on making this more automated, in the future.]Switching Option Price SourceIf there are problems with Yahoo, you can switch the source where prices are retrieved by switching this field. Switch the dropdown to Y, MSN, MW or OX2. There are notes in there for reminders of which are useful. Yahoo and MSN are fine most of the time, but if you look at the spreadsheet before the market opens, Options Express shows correct values (everywhere else clears out the bid\ask values in the morning, so it has to use the last values, which can be very wrong).Compare Performance to DOW and S&P If you want to also track your performance vs. the DOW and S&P, there are a couple more things to fill out. This doesn’t need to be done, and doesn’t affect anything except for the three “Gains” lines on the Summary tab. Performance tracking is based on three dates – The first day you want to track from, the beginning of the current year, and 30 days ago. Starting date – This is the date that you want to treat as your initial date. The original author of this spreadsheet used the date that he started using the Motley Fool services for this date. You could also set it to the date you open a brokerage account, or the date you want to begin tracking your own performance.Total Cash in at start of year – how much cash have you added, as of the start of the current year. For instance, if you started your account last year with $20K and then added $5K throughout last year, then no matter what the account is worth now, you added $25K as of the beginning of this year. This can be whatever makes sense to use, and it is just used to calculate total absolute gains of the account. Do NOT include interest or dividends or any other investment income in this field.Start of year balance – What was the account worth as of the first of this year. If using the Log sheet, this should be populated, but can be entered manually if needed.Cash Added This Year – How much more cash have you deposited to the account this year. If using the Add\Remove CASH button to log cash transactions, this will be automatically populated.Cash Added in last 30 days – How much cash have you deposited in the last 30 days. If using the Add\Remove CASH button to log cash transactions, this will be automatically populated.Cash Secured Account (IRA) – If this is a cash secured account, enter an ‘X’ in this field to switch the margin requirement calculations used by the rest of the spreadsheet.Using Targets You can enter a couple of target annual goals on the Summary tab now, and it will show your progress as the year progresses. It shows that, as of today, where you should be to meet this goal by the end of the year. Also, if you use the Copy to Log functionality, it will show your target rate on the graph on that page, for comparison with your actual rate of return.Goal is your goal for the year, Target is where you should be today to meet that goal, Current is where you actually are today, and On Target is how far you currently are from your target. This last column will become less extreme as the year progresses (the example image was captured on Jan 7th).Using the Log This tab can show you a graph of your portfolio performance, as long as you keep your Total Cash field up to date and click the Copy Today to Log button periodically. This will copy information about the current state of your portfolio to a row in the Log tab. It is currently set up to assume that this will be done daily, which will produce a more detailed graph, but this can easily be switched to only be done once a week.To use this weekly, change the formula on the Log tab in cell A3 to be “=A2+7” without the quotes. It will then use this formula to set the next date to be one week from the previous date. Click the “Copy Today to Log” button sometime Friday night or over the weekend, to get an end of week summary each week.You portfolio’s progress will be shows against the S&P, as well as a Target return rate, if you have entered that on the Summary page. Using the Notes sheetWhen you purchase a new stock, it may be necessary to add the new ticker to this page. This is done by clicking the left-hand ‘Update’ button at the top of the page. The page will be refreshed, and if the ticker isn’t already on the page, it will be added, along with some pertinent information on the stock. The righthand ‘Upgrade’ button is used to update the ‘earnings date’ and ‘ex-dividend’ dates. It can be clicked at any time. Note that when the left-hand ‘Upgrade’ button is used to add a new ticker, it is NOT necessary to click the right-hand ‘Upgrade’ button, as that gets done for you automatically.This is a place to record various information about companies that could prove to be useful later, like target portfolio size, prices to watch for, purpose for making the trade, where you got the idea, asset class, goals for holding this company, and so on. Some of these fields are taken from lists located on the ‘Graphs’ tab, and are used for data validation purposes.You can add tickers manually, by entering their symbol in a blank cell in column A, and then clicking the left-hand ‘Update’ button. If you do not hold a position in the stock, column B will be set to NO, but all automatically maintained fields to the right will be updated. If you DO hold a position in the stock, column B will be set to YES. Note that an OPTION position is treated as holding a position, for the purposes of this page of the spreadsheet.Viewing Portfolio Progress\StatusReturns at a glanceThe top right block on the Summary tab shows basic information at a glance, like how many days till next expiration, total trades and commissions paid this month, total profit\loss, and comparisons to S&P and DOW indexes. Top Gainers\LosersThe second section shows the top 10 gainers and top 10 losers in your portfolio today. Note – this should probably be re-worded to read top 10 performers and bottom 10 performers, since, on any day when all positions are up, you don’t have any losers, and on any day when all positions are down, you don’t have any gainers.Portfolio Weights and Daily ChangeThe third column section on the summary sheet shows the total weight and portfolio percentage of all the tickers in your portfolio. It also shows the change in value since the last time Copy to Log was clicked. This can show you which stocks are having the largest effects on your portfolio gains/losses today. Holding only an option position will put the base ticker into this list. The gains/losses include option movement.Preparing for upcoming expirationsAt the top of the second set of columns on the Summary tab, you are shown upcoming trades by expiration. This will let you know how many of each type of trade that you have expiring each month. If you click on the label (Puts, Cov Calls, Spreads …) then it will take you to the tab for that type of trade for you to see more information about the specific trades. [Note – I believe that the original version of this spreadsheet was written before weekly options, and at present, while you can enter weekly options, I’m not certain if the ‘Upcoming Expiration’ cells work properly for them. This needs testing – HG]Below this table is another table that shows at a glance, based on current stock prices, what your expected cash flow will be for each upcoming month, as well as a worst case scenario. Cash in would consist of ‘in the money’ spreads expiring, ‘in the money’ covered calls, and so on. Cash out would consist of ‘puts expecting assignment’, ‘LEAPS coming due’, and so on. Likely Cash Flow is the sum of the two. The Worst case shows how much would be needed if no money comes in and all trades requiring money execute.Clicking on the Expected Cash Flow label will take you to the Cash Flow tab or you can switch to that tab directly. This shows a more detailed breakdown of what types of trades will require what money.Using the TradeStats spreadsheetTo use the TradeStats spreadsheet:Place the file in the same directory as OptionTracker.xlsm (it assumes that name, so it won't work properly if that file is renamed)Open the OptionTracker.xlsm spreadsheet in ExcelOpen this spreadsheet (TradeStats.xlsm) in Excelclick the "Run Update" button on the left side of the "Leaderboard" sheetOnce this has been done once, you should be able to view results from different files with the “Select New File” button.This spreadsheet will then show gains/losses for each ticker, and each type of trade, as well as total gains/losses by ticker. It includes all open and closed out positions that are in the other spreadsheet.[Note – There may be some issues with the right-hand most columns on the TradeStats sheet – I haven’t played with this one at all, but will do so, if issues are uncovered with it. – HG]Adding a tradeClick the button for the type of trade that you are entering and you will be taken to the tab for that type of trade. If you click the wrong button, or for any reason want to remove an incorrectly added line in any of the trade tabs, simple click on the number of the row (all the way to the left of the window) to select the entire row, and select Delete from the pop up menu. This will remove that row and shift everything below it up.This image doesn’t show it, but there are two buttons at the top of each ‘trading’ page, one labeled ‘NEW’ and one labeled ‘UPDATE’. The ‘NEW’ button on each page does the same thing as the ‘New <option>’ button on the Summary page, so if you want to add more than one action, you don’t have to flip back to Summary, click the ‘NEW’ button there, and return to the current page. Just click the ‘NEW’ button, and enter the trade. Of course, this will only allow you to enter multiple trades of that type (i.e. multiple ‘puts’, multiple ‘calls’, etc.).Once all trades of a given type have been entered, it is NECESSARY to click on the ‘UPDATE’ button at the top of the page (or the ‘Update’ button on the Summary page – they all do the same thing). Please note that until the Upgrade button has been clicked, certain fields on multiple pages of the spreadsheet will not contain correct data – any cell that refers to the ‘current price’ will be incorrect. Once the ‘Upgrade’ button has been clicked, those fields will contain correct values.Note that the spreadsheet does NOT maintain REAL-TIME data, so any time you want to update the current prices, it is necessary to click (any one of) the Upgrade button. This forces the spreadsheet to obtain current prices for all the symbols in the spreadsheet for which you currently hold a position.Steps to add a putFrom the Summary Tab, click the New Written Put button, or from the Puts tab, click the NEW buttonThis will switch to the Puts tab, add a new row, and populate all the needed formulasEnter the information about the tradeEnter the ticker symbolThe Date in defaults to today, but you can change it if you need toEnter the Quantity. If selling puts, enter a negative value (e.g. -2)Enter the strike price of the putEnter or Select the Expiration Date from the list (the list will only contain monthly expiration dates, but you can manually enter a weekly date)Enter the premium that you receivedEnter your commission costs (nowadays, this is mostly zero, so enter a zero rather than leaving the field blank)Click the NEW button at the top of the page and repeat this process for entering any other puts that you want to enterWhen finished, click on the UPDATE button at the top of the pageWhen finished entering all, return to the Summary page and click on the Clean Up After Add / Remove button so that everything can be resorted and updatedSteps to add a Covered Call (or the call portion of a diagonal)From the Summary Tab, click the New Covered Call button, or from the Covered Call page, click the NEW button.This will switch to the Cov Calls tab, add a new row, and populate all the needed formulasEnter the information about the tradeEnter the ticker symbolThe Date in defaults to today, but you can change it if you need toEnter the Quantity - if selling calls, enter a negative (e.g. -2)Enter the strike price of the putEnter or Select the Expiration Date from the list (see note under ‘Puts’ regarding weekly options)Enter the premium that you receivedEnter your commission costsIf this call is backed by a LEAP instead of stock (a diagonal) then Place an X in the LEAP column and enter the strike price of the LEAP in the Call Strike column after that.The main purpose of this is so to calculate the margin requirement properly, but it is also a way of seeing which are diagonals and which are plain covered calls.Click the NEW button at the top of the page and repeat this process for entering any other trades that you want to enterWhen finished entering trades, click the UPDATE button at the top of the pageReturn to the Summary tab and click on the Clean Up After Add / Remove button so that everything can be resorted and updatedSteps to add a Bull Call SpreadFrom the Summary Tab, click the New Bull Spread, or from the Bull Call Spread tab, click the NEW buttonThis will switch to the Spreads tab, add a new row, and populate all the needed formulasEnter the information about the tradeEnter the ticker symbolSelect from the list if this is a Call or a Put spreadThe Date in defaults to today, but you can change it if you need toEnter the Quantity (as a positive number)Enter the lower strike price of the spreadEnter the higher strike price of the spreadEnter or Select the Expiration Date from the list (see note about expiration dates on the ‘Puts’ tab)Enter the premium for the lower strike option (paid for call or received for put)Enter the premium for the higher strike option (received for call or paid for put)Enter your total commission costsClick the NEW button at the top of the page, and repeat this process for entering any other trades that you want to enterClick the UPDATE button when doneReturn to the Summary tab and click on the Clean Up After Add / Remove button so that everything can be resorted and updatedSteps to add a Bear Call SpreadFrom the Summary Tab, click the New Bear Spread, or from the Bear Spread tab click the NEW buttonThis will switch to the Bear Spreads tab, add a new row, and populate all the needed formulasEnter the information about the tradeEnter the ticker symbolSelect from the list if this is a Call or a Put spreadThe Date in defaults to today, but you can change it if you need toEnter the Quantity (as a positive number)Enter the higher strike price of the spreadEnter the lower strike price of the spreadEnter or Select the Expiration Date from the list (see note about expiration dates on the ‘Puts’ page)Enter the premium for the lower strike option (received for call or paid for put)Enter the premium for the higher strike option (paid for call or received for put)Enter your total commission costsClick the NEW button and repeat this process for entering any other trades that you want to enterWhen finished entering trades, click the UPDATE button at the top of the pageReturn to the Summary page and click on the Clean Up After Add / Remove button so that everything can be resorted and updatedSteps to add a Straddle (or Strangle)From the Summary Tab, click the New Straddle, or from the Straddle page, click the NEW buttonThis will switch to the Straddle tab, add a new row, and populate all the needed formulasEnter the information about the tradeEnter the ticker symbolThe Date in defaults to today, but you can change it if you need toEnter the Quantity (as a negative number) At present, this page does NOT support buying straddles, only selling them. The formulas would all need to be updated to support this [Note – this has to be tested to confirm – the formulas may have been updated and the documentation left behind – HG]Enter the Call strike price of the straddleEnter the Put strike price of the straddle (same as call for straddle, lower than call for strangle)Enter or Select the Expiration Date from the list (see note about expiration dates on the ‘Puts’ page)Enter the premium for the call optionEnter the premium for the put optionEnter your total commission costsClick the NEW button and repeat this process for entering any other trades that you want to enterWhen finished, click the UPDATE button at the top of the pageReturn to the Summary page and click on the Clean Up After Add / Remove button so that everything can be resorted and updatedSteps to add a Synthetic LongFrom the Summary Tab, click the New Synthetic Long, or from the Synthetic Long page, click the NEW buttonThis will switch to the Synth Long tab, add a new row, and populate all the needed formulasEnter the information about the tradeEnter the ticker symbolThe Date in defaults to today, but you can change it if you need toEnter the Call Quantity (as a positive number) Enter the Put Quantity (as a negative number)This page does not support synthetic shorts yet. The formulas would have to be updated. [This needs to be tested – the formulas might have been updated but the documentation left behind – HG].Enter the Call strike priceEnter the Put strike price Enter or Select the Expiration Date from the list (see note about expiration dates on the Puts page).Enter the premium for the call optionEnter the premium for the put optionEnter your total commission costsClick on the NEW button and repeat this process for entering any other trades that you want to enterWhen finished entering all, click on the Update buttonReturn to the Summary page and click on the Clean Up After Add / Remove button so that everything can be resorted and updatedSteps to add a LEAP (or any plain long call)From the Summary Tab, click the New LEAP, or from the LEAP page, click on the NEW buttonThis will switch to the LEAP tab, add a new row, and populate all the needed formulasEnter the information about the tradeEnter the ticker symbolThe Date in defaults to today, but you can change it if you need toEnter the Quantity (as a positive number) Enter the strike price Enter or Select the Expiration Date from the list (see note about expiration dates on the Puts page)Enter the premium paid for the call optionEnter your total commission costsClick on the NEW button and repeat this process for entering any other trades that you want to enterWhen finished entering all, click on the Update buttonReturn to the Summary page and click on the Clean Up After Add / Remove button so that everything can be resorted and updatedSteps to add a StockFrom the Summary Tab, click the New Stock or from the Stock page click on the NEW buttonThis will switch to the Stock tab, add a new row, and populate all the needed formulasEnter the information about the tradeEnter the ticker symbolThe Date in defaults to today, but you can change it if you need toEnter the Quantity (shares purchased) Enter the purchase price if this stock was acquired from a written put – enter the strike price (the price you really paid for the shares)Enter your total commission costsClick the NEW button and repeat this process for entering any other trades that you want to enterWhen finished entering all, click on the Update buttonReturn to the Summary page and click on the Clean Up After Add / Remove button so that everything can be resorted and updatedAdding Cash to accountFrom the Summary tab – click on the Add / Remove Cash button when you add or remove cash from the account. This will add a new row to the Cash tab and populate several formulas. Once you enter the correct date that the money was added (it defaults to the current date) and the amount added, then it will calculate your new ‘shares added’. This is used to calculate your Net Asset Value, for calculating your time weighted internal rate of return (TWIRR)Adding DividendsTo add dividends, go to the Dividends tab and add the new dividend to the list by adding the Date received, the Ticker symbol, and the amount received. This isn’t used anywhere else in this spreadsheet, but is used in the TradeStats spreadsheet when showing your total gains\losses by ticker symbol.Note that at present, there is no provision for recording ‘return of capital’. This means that unless you manually adjust the basis, your NAV will begin to be off, cumulatively, after every distribution. [I’m not quite sure how to go about handling this just yet, and must research it – so any suggestions would be appreciated – HG]Note that at present, there is no provision for entering any interest paid by the brokerage. This just ‘rolls up’ into the ‘total cash’ field.Closing a tradeWhen closing any trade, simple go to that tab that has that trade, enter the Date Out, Price Out, and Commission out, and then select everything in the second section and delete to clear it out.If a trade expires worthless, then enter $0 for the price out and the commissionI.E. if you bought back the GSK Put for $0.10 on Friday, then you would do this:Before:After:When closing out a trade, it is not necessary to click on the Update button. However, it IS necessary to return to the Summary tab and click on the Clean Up After Add / Remove button so that everything can be resorted and updated.End of Year Update StepsAt the end of the year, a few things that the rest of the spreadsheet uses need to be updated. [Note – these steps can all be automated, and that is something I am planning to do, adding a new button on the Summary page to initiate them – HG]On the Details Tab – update the Total Cash In at start of year to be the total cash added to the account, as of the start of the New Year. This would be the previous year’s cash added at start of year plus all cash added in the past year. This is used to calculate your overall absolute gains. On the Summary Tab – click on the Add Cash button to add a new row to the Cash Tab. Set the date to be the last day of last year (12/31/09), set amount to be zero, and set your account balance to be your account balance as of the end of the year. This is used several places for your year to date performance.Clear out the Log tab - Select all rows with data (everything but the header) and delete. Then go back to the Summary Tab and click on Add to Log. This will make a new row for the beginning of the year. If the date isn’t 12/31 of the previous year, change it to that date. Also, update anything else that isn’t correct. (If doing this before the first trading day of the New Year, everything should be correct, otherwise, update as necessary) ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download