Trading Journal Spreadsheet ®

You open a workbook in Microsoft Excel, and cannot see the file. There appears only a gray screen, even if (sometimes) you can see a formula in the formula bar!

1) Your Excel page is maximized and for some reason your file is set to be minimized; try to locate the “minimized” screen, then maximize it. 

2) You have multiple monitors and the window of your Excel app is spanning across more than 1-monitor; drag the right-side border of your Excel app across your other screens to view the inside window. 

3) If you still cannot see the workbook, close down the program; Excel should ask if you want to Save the file. If it does, (your file is there… hidden)… please read on.

Macros are sets of instruction that work behind the scenes, and used by TJS to give you a better user experience. It will be necessary to allow them to run when using the product, however – Excel gives you choices on how to enable them.

You can choose to enable macros by clicking the Excel “warning” message near the top of your sheet, or better yet… have Excel automatically enable macros (recommended)*. To do this, please spend a moment by following these instructions:

*While performing the above instructions, Excel will say this is not recommended. If you commonly download Excel files from the internet, not knowing where they come from (that contain macros), you may not want to choose this option. Just use Excel’s ‘warning’ message to enable macros each time you open the file.

Macro enabled file icon

The file icon will have a yellow exclamation point in it, and the file extension will be (.xlsm), instead of (.xlsx).

… as seen in the image to the right —>

Please use the fix below, depending on your specific Excel version:

Excel 2003 (or earlier)

1) Excel does not open the file at all

• In the Toolbar menu, click Tools, and then click on the Options.
• Click the General tab.
• Click to clear the Ignore other applications check box, and then click OK.

Excel 2007

1) Excel does not open the file at all

• Click the Microsoft Office Button , and then click Excel Options.
• Click Advanced, and then click to clear the Ignore other applications check box in the General group.
• Click OK.

Excel 2010

1) Excel does not open the file at all

• Click the green File tab, and then click Options.
• Click Advanced, and then click to clear the Ignore other applications check box in the General area.
• Click OK.

This is not a normal occurrence, and if you click yes, then Excel will most likely repair the file while deleting the macros… which is not what you want.

So, things to check are:

• Make sure Macros are enabled.
• Make sure you did a Full Installation of Office, including VBA (reinstall using ‘defaults’ may be necessary).
• Make sure your Excel (or Office) is Updated (with the latest Service Pack, ie. SP3)
• Make sure file is not in a “protected” folder – it should be “trusted” (if you don’t use ‘trusted’ folders, then just check #1-3).

If after performing the above, and you’d like another download to start over, please let me know.

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.

All you’ll need to do is physically widen the upper column (letter) header.

Just grab the right-side of the header with your mouse and drag slightly to the right until your data is displayed.

This tip should be of no concern to the “Elite” product user, but user’s of older TJS products may experience little green triangles throughout the Trading-log sheet – when the sheet is Unprotected.

If you commonly use your Trading-log without the Protection feature, and would like to get rid of the green triangles, please follow these steps:

Excel 2003

1) Choose Options from the Tools menu.
2) Make sure the Error Checking tab is displayed.
3) Clear the Enable Background Error Checking check box.
4) Click OK.

Excel 2007

1) Click the Microsoft Office Button, click Excel Options, and then click the Formulas category.
2) In the Error Checking section, clear the check box in front of “Enable background error checking”.

Excel 2010

1) Click the green File tab, click Options, and then click the Formulas category.
2) In the Error Checking section, clear the check box in front of “Enable background error checking”.

It is recommended to use a tool like www.Dropbox.com to have your file automatically backed-up and synced between computers.

Make sure steps 1-3 of “Using the TJS on your multiple computers” have been accomplished first!

To enable the TJS Elite to work on your 2nd computer.

First, please know that the computers you plan to use must have the same user name. To ensure this is the case, do the following:

1) Go to the computer that you downloaded the original file to.

2) Open Excel.

3) Depending on your Excel version (2007, 2010, 2011), do the following:

• 2007
  • Click MS Emblem (top-left of toolbar).
  • At the bottom, click Excel Options.
  • Near the bottom, you’ll see a “User name” box. This name must be the same for both computers)
• 2010
  • Click the green File tab (upper-left of toolbar).
  • In the left column, click “Options”.
  • Locate the “User name” box and make sure the name is the same here as in your other computer.
• 2011
  • Look in the ‘Preferences’ section, then locate the “User name” box and make sure the name is the same here as in your other computer.

As long as your 2^nd or computer has the same Excel user name (as on the computer you originally installed the file on), you’ll be able to share your main file between your other computers.

*** Suggestion *** It is recommended to use an app like www.Dropbox.com to have your file automatically backed-up and synced between computers – make sure steps 1-3 have been accomplished first!

How to hyperlink (attach) a chart image to a trade in the TJS Trading-log.

First, you’ll need a screen-capture utility. Some popular choices* are…

For Screen-captures:

• Snagit (for Windows) or (for Mac)

For ‘sharable’ videos, images and documents:

• Screencast

* Windows users can use the basic (built-in) utility already included in Windows 7 or Vista, called “Snipping Tool”, accessed from the Programs menu.

With your trading platform open, use the Capture Utility to capture the image you’d like to attach to your trade. Then follow one of the two methods of linking your chart (or document) to the desired cell of your spreadsheet.

Preferred way…

If you want to make sure you don’t lose your hyperlinks (if you rename or move your folders), try the following, which will tell Excel to always look in the same location that your Excel spreadsheet is in. This involves using an actual formula command in the cell that you wish to hyperlink from… say, the ‘symbol’ column.

=HYPERLINK(“Link_location”,”File_name”)

• Link_location refers to the file to link to
• File_name is the text you’d like to display in the Excel cell

 Example: =HYPERLINK(“Trade1.jpg”,”Trade1″)

Doing it this way, you’ll never lose your hyperlinks, as long as the file names don’t change and you always keep your Excel file and your linked files in the same folder.

Conventional way…

1) In the Trading-log, Right-click the symbol (or any other clickable cell with text in it). Then click Hyperlink on the shortcut menu.

2) An Insert Hyperlink box will pop-up. Use a combination of the drop-down arrow and file folder to browse and locate your saved file (image).

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

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

The only drawback with this method is that if you ever make a change to your folders, you stand the chance of invalidating your hyperlinks.

Your done! View your chart hyperlinks periodically to help review your trades and compare the charts with the notes you recorded in the Entry / Exit journal section (at the end of each Trading-log row). 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.

A Pivot Table report gives you an interactive way to quickly summarize vast amounts of data. Easily summarize data by categories and subcategories, as well as filter, sort, group, analyze and explore useful subsets of data.

For the TJS, this means you can quickly find out how certain strategies, markets, timeframes, categories, etc., are performing each week, month (or year). With Pivot Table reporting, you’ll be able to display outcomes based on filtered subsets of information, instead of the cumulative basis that the Tracking sheet displays.

Each TJS Pivot Table reporting sheet gives you a basic example of how to set up the sheet, but feel free to check (and uncheck) the boxes and expirement to see what possibilities you can come up with. There really is no wrong way to filter your data, but since there are many ways, you may want to learn more about them as they can be very useful.

Remember, if Pivot Tables are intimidating to you, please don’t think you need to use them to analyze your trade data. The Tracking sheet(s) will give you the data and stats needed to assess your current progress, to narrow down your Strengths & Weaknesses and provide you with accurate expectancy figures.

Tips when using the TJS Reporting sheet (Pivot Table):

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. 

Since the TJS staff does not have the resources to teach users how to use Excel’s advanced features, some helpful links have been provided:

• Click to view the Microsoft Pivot Table course.
• Microsoft overview of PivotTables and PivotCharts.

We find that most traders manually enter their trade information, and use that time to reflect on their trading day – or they simply input information in real-time (during the trading day).

However, some traders may want to try and expedite the process by using their broker export utility. This utility allows you to download your trade information in an Excel format.

Assuming your broker has this utility, you may be able to copy/paste some of that information to the TJS TradingLog. This may save some time if you’re a high-frequency trader, or it may not – depending on the export format.

Remember, the TJS product is unique in that it summarizes your trade information into multiple Performance-tracking categories (customizable to you). Your broker will not have this information, so some info will have to be ‘manual’ entry. The export file will also be formatted differently, so a 1-click Copy/Paste will not be possible. 

You’ll (most likely) need to Copy specific columns of information, the pasting that information to a corresponding column in the Trading-log sheet. To make sure you don’t invalidate the formatting of the TJS, use the following function when pasting your information:

Paste Special > Values (only)

Don’t forget… there is a sheet that you may find helpful if you’d like to take notes of your trades (instead of trying to rely on memory) when entering your trades after the market, or your trading day) – is over. It’s the TradeSheet and you’ll find it in the HomeMenu sheet.

Since traders can be found all around the world, there is no default currency.* This way, any currency will be calculated correctly, as long as all trades are in one common currency. 

* Exception – The UK Spread Betting product does display the Pound currency symbol in some price fields.

If you trade with brokers using multiple currencies, you can do one of two things:

1) Simply enter your actual P&L (in one common currency) in the Gross P&L column of the Trading-log sheet.

• This will over-ride the formula and avoid having to convert prices.

2) Choose one common currency for all trades entered in the Trading-log sheet, then convert all pricing data (in the alternate currency) to the main currency. 

• This is advisable if you use Stop and Target prices, which will have an affect on yellow “Summary” section of the Trading-log sheet.

3) Duplicate your product file, and Record & Track your multiple currency trades in separate files. 

The (unique) “Tracking” sheet needs consistent P&L data, and by choosing one of these methods… all of your data will be consistent, giving you reliable tracking analysis.

After having viewed the Getting Started Slideshow, you’re now ready to choose a method of recording your trades.

First, the Tracking sheet, and its various “Performance-tracking” categories need to see “consistency” to produce meaningful data in which to track and analyze your results. Due to the complexities of Options trading, there are two different methods of trade input to choose from.

Read the methods below for a quick summary of each method, then scroll down for additional details and examples of each. Choose the one that makes most sense to you and your trading regimen, then stick to that one method for each successive trade input:

Tracking your Options trades can be more labor intensive than tracking other markets. Because you are trading an underlying issue, sometimes you’ll need to be slightly creative when looking to enter Stop and Target prices in the Trading-log.

Because Option prices are not linearly related to the underlying stock prices, there is no way to use the underlying stock numbers (prices) directly and produce a risk (and/or) reward amount for an option trade.

What the TJS user can do is use the “delta” value of the option to help evaluate the risk and reward scenario. For a long position, estimate the “stopped out” value of the option by multiplying the number of points (the stock-based stop is set underneath the stock purchase price) by the “delta” of the option that was chosen. Subtract this amount from the option purchase price.

The following example should help:

———————————————–

The Futures Tracking sheet allows you to Track & Analyze specific instruments, however, most will have varying contract sizes. Since the TJS will attempt to calculate your P&L for you, the sheet needs to know the size of each instrument that you list.

For a list of common Futures and Commodities (contract sizes), please visit the TJS Futures Contract Size page.