1) I want to sum the total of my multiple broker/accounts to my Analysis sheet(s).

We can do this by using a simple formula in one (or all) of your Analysis sheets. To do this, go to your Analysis sheet, and then click in the Account Capital cell at the top of the sheet. Now, click in the Formula Bar at the top of the Excel app. You’ll be entering a formula that will sum total the combined Capital you have for two (or more) accounts.

Here’s the formula to Sum Total all (5) accounts in to one Analysis sheet:

• =Brokers!J2+Brokers!J3+Brokers!J4+Brokers!J5+Brokers!J6

Whereas “ J2 ” equals the 1st broker, “ J3 “equals the 2nd broker, and so on.

So, if you only wish to Sum total the first two accounts, then the formula would be:  =Brokers!J2+Brokers!J3

Once your formula is entered, click Enter on your keyboard – you’re done.

2) I trade “Multiple Markets” under the same broker/account, so I’d like to link my one broker/account to multiple Analysis sheets.

Go to your Accounts sheet. In the blue “Account 1” cell, re-enter your actual Account/Broker name. Then, underneath that enter your start date and then your beginning account capital (usually, this is the amount in your account when you start your TJS trade-tracking).

Now, go to each of your Analysis sheets, and then enter a formula in the Account Capital cell.

Here’s the formula:

• =Brokers!J2  (Whereas “ J2 ” equals the 1st broker, “ J3 ” equals the 2nd broker, and so on).

Once the formula is entered in each of your Analysis sheets, they will all be updated and synced together based on the information you enter in the ledger section of that particular broker account, in the Accounts sheet. 

A Pivot Table report allows you to summarize vast amounts of data by categories and subcategories, as well as filter, sort, group, and explore useful subsets of data.

Each TJS Pivot Table Reporting sheet gives you a basic example of how to set up the sheet, but feel free to check (or uncheck) the boxes and experiment to see what possibilities you can come up with. There really is no wrong way to filter your data, but with that said – there are many ways to filter your data.

Tips when using the TJS Reporting sheet :

1.     You can change the pivot table summary formulas by right-clicking on the pivot table (not the graph), then selecting “summarize data by” option.

2.     To refresh the original data, just right-click on the pivot table (not the graph) and select “Refresh Data”.

3.     To drill down on a particular summary value, double click on it. A new sheet will be created with data corresponding to that pivot report value.

4.     To make a new chart from a pivot table, simply click on the pivot chart icon from the Options toolbar ribbon, then follow the wizard instructions. 

Entire books have been written on the use of Pivot Tables, and the TJS staff does not have expertise in the use of the Reporting sheets. So, if you require support for these sheets (above and beyond what we’ve listed here), you will probably get a blank stare from the developer ?

Here are some helpful links if you’d like to know more:

• Click to view the Microsoft Pivot Table course.
• Microsoft overview of Pivot Tables and Pivot Charts.

Excel (Windows) 2007 / 2010 / 2013 / 2016 / 365

1. Click the File (or MS Office) button at the (top-left) of Excel.
2. Click “Options” button (bottom-left).
3. Click “Trust Center” (left-side). 
   • In same window, click “Trust Center Settings (right-side).
4. Click “Macro Settings” button (left).
5.  Choose to “Enable all Macros…”
6. Click “OK”…
7. Save and Close the Excel program.
8. Reopen Excel.

Excel (Mac) 2011

1. On the Excel menu, click Preferences.
2. Under Sharing and Privacy, click Security.
3. Select the “Warn before opening a file that contains Macros” check box.

*While performing the above instructions, Excel may say this is not recommended. This is true if you commonly download Excel files from the internet and not knowing where they originate from. if so, just use Excel’s ‘warning’ message to enable macros each time you open the file.

If for some unusual reason you absolutely, positively, must have sheet tabs, please follow these instructions to make them magically reappear:

Excel for Windows

1.     Click the Green File button (or the colored Microsoft button in Excel 2007)

2.     Click “Options” (or “Excel Options” in Excel 2007)

3.     Click “Advanced” from the left sidebar

4.     Scroll down about half-way until you see “Display options for this workbook”

5.     Put a ‘check’ in the “Show sheet tabs” box

Excel for Mac

1.     Go to “Excel” (from the menu bar)

2.     click “Preferences’

3.     Click “View”

4.     In the “Windows options” section, put a check in the “Show sheet tabs” box

⇒ “Hyperlink” instructions:

Once you create your link, you’ll need to make sure you don’t (rename or change) your image name or folder location. If you do, you’ll invalidate your links. *

1.     In the Trading Log, Right-click the text in the Symbol column, and then choose Hyperlink on the shortcut menu. An “Insert Hyperlink” box will pop-up.

2.     Locate your saved (image) file.

3.     Click on the file name of your image — Click OK.

4.     The text you hyperlinked is now (blue/underlined). Clicking it will instantly open up your picture viewer and the image you associated the link with.

5.     Your done! *

Now, go to the Trade Review sheet to review your trades and compare the charts with the notes you recorded in the Entry Notes / Exit Notes journal section. Comparing your notes to the actual charts that you traded will allow you to more easily determine what you did right or wrong, and what you may do differently in the future.

* Note:
If you plan to enter screenshots, decide on a file location for your TJS and screenshot Images before you start. Always keeping your (images and TJS file) in the same folder is advised, and if so… you should never experience an issue with the Hyperlink function not finding your image. 

This is due to Excel needing to see a relative path to those images, which will be lost if the (image or TJS file) is moved from its original location. Although there are variables with some systems that may not experience these issues, it’s best to play it safe.

Just scroll & highlight any numeric data column and you’ll see some interesting calculations come to life, at the bottom of your Excel app:

* Sum
* Average
* Minimum
* Maximum
* Count (# of items)

• Depending on which column you’re highlighting, you may need to first unprotect the sheet [ Review > Unprotect sheet ].

You cannot permanently keep these results in your TJS, but when you want a quick answer to a question on your input area, it can’t be beat for its quick and instant display.

To change your choice of what the status bar displays (or if you do not see the Quick Summary info), just right-click on Excel’s footer bar and you will see your choices. Check or uncheck the ones you want displayed.

Method 1

Each trade (regardless of the number of ‘legs’) are summed together on one row in the Trading Log. 

Pros:

This is the simplest method, and recommended if you don’t wish to enter Quantity or Price information in the Trading Log sheet.

• Each (multi-leg) trade is entered on only one row, regardless of how many ‘legs’ were executed.

• Skip the (Entry – Stop – Target – Exit) columns.

• After ALL ‘legs’ of the trade are closed, sum the Profit/Loss, then manually enter your Gross P&L.

• The trade (as a whole) gets classified as one single strategy in the Tracking Codes section of the Trading Log.

Cons:

Without Entry and Exit price information, the Open and Closed Equity calculations in the top header won’t populate, and the following information in the Trading Log’s “Trade Summary” section will not populate:

• Risk Amount  —  R-Multiple  —  Reward-to-Risk  —  % Gain

• This is offset by getting some very detailed analysis in the Analysis sheet.

Example images of both Trading Log and Analysis sheet, for Method 1:

– Maximize the browser window for an enlarged full-screen image

• Trading Log

• Analysis

Method 2

Each trade ‘leg’ is entered on separate rows in the Trading Log.

Pros:

Since both Quantity and Entry & Exit price information is required, TJS will automatically sum your Gross P&L, along with additional fields in the “Trade Summary” section (if Stop and Target prices are entered).

Cons:

Since each leg of the trade is entered as its own trade, your Win % analysis in the Analysis sheet will likely reflect both winning and losing percentages for a single trade regardless of the outcome of the trade (as a whole).

Example images of both Trading Log and Analysis sheet, for Method 2.

– Maximize the browser window for an enlarged full-screen image

• Trading Log

• Analysis

Which Method (#1, or #2) is best for me?

TJS set out to ease the burden (or the complexities) of Tracking & Analyzing Options trades, but that does not mean it’s a one-size-fit-all approach. Before we can start tracking our Options trades, we need to figure out one of two Methods of trade input.

Personally, I always try to keep things simple and if that’s what you’re after, consider using method #1, but keep reading to understand if the ‘simple‘ method is truly for you.

While I do find that some Options traders use #1, the majority choose #2, as it provides additional (per trade) analysis in the Trading Log sheet; however, the Analysis sheet stats will be slightly skewed when looking at the Win % and the Payoff Ratio. This is in part to having strategies with more than one leg, where one (or more) legs may be a Win while another leg was a loss. As long as you understand this nuance, the “Expectancy” column will be your best Performance Tracking Category (no matter which Method you choose).

Here are some important questions to consider before deciding to commit to either input Method:

1)      Do you need TJS to show you Open and Closed Equity amounts? – Your trading platform may already tell you this.

• If so, #2 may be for you.

2)      Do you enter Stop and Target Prices? – Which opens up more calculations in the AH -> AK columns.

• If so, #2 may be for you.

3)      Do you trade Covered Calls?

• If so, view the next faq category (below) to read an excerpt of why C.C. traders may wish to use #2.  ⇓

If none of these points are relevant, then I’d opt to keep it simple and use #1.

Covered Calls – with fellow TJS user:

Avid Options trader, Bonnie Hayworth says…

“Some traders wish to see their total P&L combined on one Trading Log row (Method 1), which is not possible with (Method 2) and that is a good thing. I like to break it out using Method #2 to see how well selling premium is working against each product and more importantly, when selling premium makes sense! Here’s why; say you own SLV and APPL, and SLV moves sideways a lot of the time. Silver and gold run cyclically in the Market place, and they typically move higher in the latter part of summer. With that said, I am able to track on the the TJS that selling calls on SLV in the month of August and September is a bad idea… that my probabilities are very low in those months.”

Input:

I input this as two transactions (separate rows in the Options Trading Log) because that’s how it played out. This will show in the P&L (if /when) the stock is sold. It’s the opposite in the sold call depending on if the stock is already owned, which will dictate the way the transaction is inputted.

If you have the TJS Stock/Options product, then you may wish to enter your Options – Covered Call “Stock” transactions in the Stock Trading Log – this will be a personal preference.

If you have the TJS Options product (only), you’ll enter your Covered Call “Stock” transactions in the Options Trading Log. To get the proper P&L for your Stock trades, follow the instructions in cell N11 where it says “Adjust Size” — this will change the Contract Size back to Share size.

1) Close (and/or) Open the Option position in the Options Trading Log by entering a .00001 in the Exit Price column, and then enter the new position (on a new row). Make sure of the following… in your Options Analysis sheet, you should have an Entry Strategy (Code and Description) that includes the possibility of an “Assigned” position.

The Entry Strategy for this type of trade could, for example, have a Code and Description of:

• Code: A.O.P.
• Description: Assigned Option Position.

Now, ‘tag’ the trade accordingly in the Entry Strategy column of the Trading Log (below the blue Equity-curve graph) so the trade will appear in the Options Analysis sheet once the trade is finally Closed (with a Gross P&L entry).

2) Or, treat it as a new Stock trade if you have one of the following TJS product versions: (Stock/Options or All Markets). If this is your preferred method, then you would create any new assignments in the Stock Trading Log.

The Entry Strategy for this type of trade could, for example, have a Code and Description of:

• Code: A.O.P.
• Description: Assigned Option Position.

So, do whichever makes most sense to you and your trading style, and then remain consistent with it for each and every similar trade to achieve optimal analysis.

Suppose a stock price is at $20.00 and you have purchased a $17.50 Strike in the money option for $4.00. Suppose the “delta” for this option is .75 (The option chain should give the actual value.)

If you want to set a stop loss based on the stock price dropping to $18.50, you’ll need to calculate what the value of the option would be when the stock is at $18.50. There are option calculator tools that can do this but a quick way is to realize that for a stock with delta of .75, a $1.00 drop in stock price will cause a $.75 drop in the option price. So for a $1.50 drop in the stock price — $1.50 x .75 = $1.13. So when the stock is at $18.50, the option is worth approximately $4.00-1.13 = $2.87.

For the reward (Target) side of the equation, you can just estimate at what value you would be willing to sell the option, or if you want to tie it to a particular stock price, you can do it the same way by adding the option’s delta to its price for every $1.00 move in the underlying stock. This is complicated when the stock moves more than a couple of points, because delta will also increase along the way. The best way may be to look at what you paid for the option, then look at what your risk is and set a target price to sell the option at purchase price plus – 3(x) what your amount of risk is. So in this example you would estimate a stop loss value of option as $2.87, and estimate target price of option as $4.00 + (3 x $1.13) = $7.39, which gives a 3:1 reward / risk ratio.

The long and short of it is that, unlike stock prices, option prices do not correspond in a linear manner to the stock price. If you want to do the calculations exactly, you’ll need to evaluate the value of your particular option at your “Stocks” stop loss price and target price.

You may find it sufficient to use a quick head calculation of the effect of delta to set the value of the option at the stock stop loss point as described above and to set an arbitrary value for a target sell price of the option. This should permit a good enough risk and reward calculation in your spreadsheet for most purposes.

Microsoft (Office 365) Troubleshoot Links

• Excel not responding, freezes or stops working. Microsoft Crashing and not responding help page.

• Repair a corrupted workbook for Office 2013 / 2016.
  • This may be necessary if one of the internal files needed to function a specific aspect of Excel becomes damaged, deleted or corrupted during a brutal shutdown.

• Microsoft Office 365 Easy Fix  
  • If your Office 365 or Excel (2013, 2016) is not working correctly, try restarting it to see if the problem is fixed. If not, try repairing it with one of Microsoft’s “Easy Fix” tools.

• Repair an Office application 
  • When trying to repair Microsoft Excel from your computers Control Panel, use this.

• Recover a corrupted workbook
  • If Microsoft Excel detects a corrupted workbook when opening, it initiates File Recovery mode and then attempts to repair the file. If File Recovery mode doesn’t start, try using this manual process to recover your file.

• Automatically Save a backup of your workbook
  • With a backup copy of your TJS file, you’ll always have access to your TJS even if your workbook is deleted mistakenly, or if it becomes corrupted.

• Automatically create a recovery file at set intervals
  • A recovery file also helps to ensure you have access to your data if your TJS is deleted mistakenly, or if it becomes corrupted.

• Uninstall Office 2013 / 2016 or Office 365 from a Windows computer.
  • When all other solutions have been exhausted, or none exist, it’s best to fully uninstall Office/Excel 2013 from your computer, wiping it clean of all potentially corrupted files that may be left on your Windows set-up. – Click the link to have Microsoft do this for you.

• Office 365 frequently asked questions.
  • Applies to Excel 2013, 2016, Office 365

Microsoft (Excel 2010) Troubleshoot Links

Excel 2010

If your Excel (2010) is not working correctly, try restarting it to see if the problem is fixed. If not, try repairing it:

• Go to your Control Panel > Programs and Features, right-click on Microsoft Office 2010, and select Change.

• On the next screen select Repair. Microsoft Office will go through and do a repair and hopefully that will get you up and running again.

Microsoft (Excel 2007) Troubleshoot Links

Excel 2007 Fix It

If your Excel (2007) is not working correctly, try restarting it to see if the problem is fixed. If not, try repairing it with Microsoft’s “Diagnostics” tool. Here’s how:

• In Excel, click the top-left Microsoft button > click Options (bottom) > click Resources (left side-bar) > click Diagnose (right side-bar).

Windows Defender

Included for free with Windows 8, 8.1, RT, Win 10.

• If you are not running an existing Anti-virus program, make sure you enable it. Type in “Windows Defender” in your Start button ‘search’ bar, then configure (if needed).

A few of the cells don’t look formatted correctly

The TJS incorporates merged cells in some locations of the file (when necessary), but the Mac Excel software app does not display all of them properly, which…

• Gives an awkward appearance to some formatted cells (mostly in column headers). If you notice colors that don’t quite match the surrounding cells – this is the reason – but nothing to be alarmed about.

Most of these issues have been fixed in the “v8” product version.

Trade Review sheet, “Comments section may not expand (or will truncate) the text.”

Unfortunately, this is not an issue we have much control over. The TJS has gone through extensive re-coding to be as “Mac” compatible as possible. This is one of those issues that Mac Excel 2011 is not able to interpret the coding as its Windows counterpart does.

Yellow headers in Analysis sheet do not automatically change the corresponding headers in the Trading Log.

This does not apply to Windows Excel users!

• Go to your Trading Log, unprotect the sheet and manually enter your new category header name  in row 15, under the “Tracking Codes” section (which is under the Equity-curve).

Accounts sheet “Link-to” button (“v7” product users).

Not all Mac users will be able to utilize this function.

An easy workaround is to simply enter a formula in the Account Capital cell of your Analysis sheet. For example, to reference “Broker1” in the Accounts sheet, you’d go to your Analysis sheet and enter the following formula in the Account Capital cell (very top):

• =brokers!h2

To combine the total of more than one broker, just add to the formula, like so:

• =brokers!h2+brokers!h3

Aesthetic limitations:

Some “Images” may not display (or may appear as a Red X, or something similar)

This is a known Mac 2016 “layering” issue. If you are affected by this, please contact TJS. We will send a new modified “Mac” file that addresses this issue.

“Pop-up” Comment messages (from the small Red triangles) remain hidden under objects

For instructional purposes, the TJS incorporates pop-up comments in key locations in each sheet, which can be hidden from view by nearby ‘objects’ (Images, Buttons, Graphs, etc.) located near the pop-up comments. 

• If your Mac Excel 2016 has this issue, please contact TJS. We will reply a new modified “Mac” file that includes all affected pop-up messages.

Other non-critical functionality limitations:

Deleting market sheets (Home Menu)

If this functionality does not work, please do the following…

• Click “Abort” to close out of the error message.
• Close the file and then Reopen the file.
• Most users have reported that the function will now work.

Options Trading Log

If you receive an error message when clicking in a Gross P&L cell, or when clicking the Filter buttons under the blue graph, then…

• Click on the upper Review tab, and then Unprotect sheet. The error message should no longer appear. 

Data-transfer function

Most users will never use this function.

• So far, there is no work-a-round for this function, other than to manually copy the data from the old file (to the new file) using the Copy > Paste Special > (Values only) to paste your data. This is not always recommended because of the potential to compromise the new file. The only other option is to start ‘anew’.

TJS crashes after entering data in the Trading Log or Analysis sheet

The cause is most likely a recent upgrade to Windows 10. Although it’s not understood why it happens with this particular O/S upgrade, we’ve identified an Excel setting change that will cure the issue:

1. In the upper toolbar, go to File > Options > Advanced

2. In the Editing Options section, clear the checkbox for “Automatically Flash Fill”. Go to the next step…

3. Click on the General tab.

4. Clear the checkbox that says “Show Quick Analysis Options on Selection”

##### signs

This is not an error or malfunction… just an Excel warning sign that the column is not wide enough to display the figures in the cell. This happens because of the difference in screen resolutions between computers.

Go to the top column header, and then double-click the right-side of the column header. It should automatically widen it to display your data.

• If your sheet does not show the upper (letter) header, Go to the upper toolbar and click on View > Headings (check the box to view the column headings).

Restrictions on this Computer

(Also applies to, “Your organization’s policies are preventing us from completing this action for you.”)

First, be aware…

• If you are using the TJS file on a work computer, your working environment may have security restrictions. If so, please try it at home (where your system will not have these added restrictions).

To fix…

• Are you using an older Excel version? If so, it may need updating. Security updates happen often to keep you safe, and certain files will not open without these updates. If you are not sure if your system has the latest updates, please click for windows updates, then read on below. 

• Try using the Microsoft Easy Fix support page, which handles this (and other) issues. This should cure the problem – it has for everyone else with this issue. The most likely fix to try is the, “Find and fix problems with connecting to the Internet or to websites”.

• Do you have a default browser set? Sometimes browser apps are uninstalled, leaving no browser default that the link can associate with. If this is the case, simply go to your existing browser settings, and set it as your default browser. 

The File is Corrupt and Cannot be Opened

This happens very infrequently. Please view the two possible culprits…

1) This may be due to your Excel settings. Newer Excel versions introduced a feature called Protected View, preventing documents from opening if they are deemed to be from an unknown source. This setting can be easily changed to help avoid this message, and open the file.

• Open Excel
• Go to: File (top-left of Excel) > Options> Trust Center > Trust Center Settings (in middle section) > Protected View
• Uncheck all three options
• Restart Excel

The file should now open.

2) If #1 does not help, there have been online reports of this exact issue from other Excel users (non TJS related). The solution mentioned is as follows:

• It may be due to your browser. Unfortunately, this happens more frequently with the Chrome browser, but is usually fine with IE and Firefox. Try downloading the file again using IE or Firefox, and if you need a new download link to try again, please contact us.

Unreadable Content

This is usually due to an older Excel version not being automatically updated, or an Excel app that was not installed with “vba” (macros enabled). 

If you click yes to the error message, then Excel will likely repair the file while deleting the macros… which is not what you want.

So, what to do:

Make sure your Excel (or Office) has been updated with the latest Service Pack. 

» The following faq category (below) has Microsoft Update and Support links ⇓

Updating your Excel (or Office) application should cure the problem (if you were running an outdated version). If not, please:

• Make sure you did a Full Installation of Office, including VBA which should be the default install setting.

• If you use “trusted folders”, make sure the file is not in a “protected” folder – it should be “trusted”  [ Excel Options > Trust Center > Trust Center Settings > Trusted Locations > Add new location ].

If after performing the above, you’d like another download to start over, please contact us.

RUN TIME / VISUAL BASIC error

This is very rare, but could happen If using Mac Excel and do not reside in the US.

The cause is likely one of the following (in this order):

1) Language and Text Region should be set for “United States”. To change, go to…

• System Preference > Language & Text > Format > United States
• Close, and then Restart Excel

3) Did you install the “Solver” add-in?

• If so, please change the setting so that it does not open when Excel is started.

Everything should be working now, but after verifying the first two points and the file still won’t open, please try the following:

• Reinstall Excel, making sure to do a (full) install, including VBA/macros.

• Re-download the TJS by using a different browser, in the thought that your browser settings may be preventing the document from opening if it deems the TJS to be from an unknown source. If you need a new download link, please contact us.

If receiving a RUN-TIME / VISUAL BASIC error after entering data in the Trading Log or Analysis sheets, please do the following:

1. In the upper toolbar, go to File > Options > Advanced
2. In the Editing Options section, clear the checkbox for “Automatically Flash Fill“. Go to the next step…
3. Click on the General tab.
4. Clear the checkbox that says “Show Quick Analysis Options on Selection“

Excel for Mac Error: “The application Microsoft Excel quit unexpectedly”

Microsoft has a direct support/fix page for this type of error.

• Please visit Support.Microsoft.com/Excel_Quit.