[ Home ] — [ Commodity Trading Products ]

C. Commodity Futures Trading Excel Forms

The purpose of these commodity futures trading Excel forms is to help you quickly identify and select commodities which have near-term potential for high profit. You would then use the process described in the Commodity FUTURES Trading Course to identify when to enter and exit the market.

This appendix contains a brief description of all the commodity futures trading Excel forms that are included with your Commodity FUTURES Trading Course and how they are used. A full and comprehensive description is provide in the Appendix of the complete Commodity FUTURES Trading Course.

Purchasers of the complete Commodity FUTURES Trading Course receive fully-functional commodity futures trading Excel forms. Visitors only have access to the "formula-stripped" (non-calculating) FREE versions of these forms to evaluate the usefulness of these resources.

Download each file into a previously-created futures directory/folder on your computer. You do this by moving your mouse pointer to each chapter file link so the mouse icon changes from an arrow to a pointing hand. Click the Right mouse button (a menu of choices is displayed). Move the mouse pointer to the Save Links As..." option and select it with the Left mouse button. This will display a "Save As..." dialog box where you can specify the futures directory/folder as the download location for the selected file. Repeat this procedure for each file.

Note 1: To accommodate differing verisons of Excel, all ".xls" files are saved in Excel 97 and 5.0/95 workbook format. This means that they can be read by higher versions of Excel if your copy of Excel was installed with the required file conversion program. If you encounter an error message that states; "file format is not valid" -or- "cannot be accessed, the file may be read only or you may be trying to access a read only location or the server the document is stored in may not be responding", this means the necessary Excel file conversion program was not installed. You will either need to install the Excel file conversion program(s) on your computer (using your Excel installation disk), or load the files into Excel on another computer and save them in the format recognized by your Version of Excel.

Note 2: These commodity futures trading Excel forms are for the Microsoft Excel program only. They will not load in Microsoft Works spreadsheet program.

Note 3: All commodity futures trading Excel forms are "locked" to avoid accidental erasure of important formulas in the forms. There are times when you will need to unlock a form to change the numeric format of certain cells so they will display the commodity's data values correctly. It is assumed that you are familiar with Excel to perform these tasks. Cells where data entry is permitted have a light blue background.

Note 4: All commodity futures trading Excel forms are files that have been provided to you as a convenient way to monitor and track your trading activities. I do not provide support for any of these files. If you want them changed, you must either do it yourself or find someone who can make the changes. You should make copies of your original forms and use the copies. Because your trades may exceed the timeframe available on the trading forms, you should make copies using filenames in sequence (i.e., filename1.xls, filename2.xls, etc.) transferring applicable data to the subsequent form.

Money Management Form. (substituted filename used: 1.xls)
This form is used to help you define your Money Management Plan. This example displays a Start Amount of $8,000. This is the amount you use to open your trading account, and is the only value that you will need to change on the Excel form. Just enter the raw number (without commas or dollar signs). The left column displays the # Trades (25 in all). There are six pairs of columns; the left column of each pair, % max risk, displays a fixed percentage (e.g., 30%) of your maximum risk represented in dollars that you will allow to be committed to all trades at any one time, which is generally the Margin deposit. The right column of each pair displays the Loss balance, (which is the dollar amount remaining in your trading account if you lose your entire committed amount). Your Stop-Loss orders will generally further limit your trading losses to a lesser Loss balance value.

What this form is doing is showing you how many consecutive losing trades it would take to deplete your trading account amount using a specific percentage of risk amount, ranging from 30% to 7.5%. A larger percentage results in less consecutive losses before you're broke, and a smaller percentage means more consecutive losses before you're tapped out.

The moral of this story: You will go through several losers before you catch a big winner. Because of this, you need staying power to stay alive financially and be able to participate in the inevitable major move. It is your personal homework assignment to work with these numbers until you have identified a Money Management Plan that you believe will meet your trading needs. You must do this first!
Commercial Trader Net Positions Form (substituted filename used: 2.xls)
This form helps you to monitor Commercial activity. It displays the low and high value of each commodity for the last twelve months. Space is provided to record the prev. cot and curr. cot if they exist. When the ratio of the Commercial long to short positions is greater than 5:1, this is significant and should be noted. Take the larger number and divide by the smaller number. Note the ratio in the curr. cot column with an "-S" if they are net short (e.g., 6.32-S), and an "-L" if they are net long (e.g., 12.67-L).

Insert a column at the most recent net position values. Enter the date in the heading and the data for each commodity. Save and print. I do the following things which are helpful to me. If any of the current net positions is at the high or low for the year, I draw an outline box with a red felt tip marker to remind me. If any current net position is now positive (where the previous position was negative), I highlight that value with a blue color. This visually helps me to see the state of the Commercial activity, and helps me to determine if any further analysis is warranted.
Closing Price Tracking Form (substituted filename used: 3.xls)
This form does several things. It is used to keep track of the front month contract for each commodity, as well as record their daily closing price. This makes it easy to transfer each Friday closing price to the 10-week Moving Average Tracking Form for subsequent analysis. This form also performs the following calculations: enter the date into the "A" Week Monday cell; the other dates are filled in, the Stop-Loss value, and the $ Potential for both the daily 50% and weekly 50% targets. All calculated fields are "locked" to prevent accidental erasure.

Each Monday (or whenever you get your printed Commodity Price Charts, compare the front month of each commodity in it with the front month of each commodity on this form to determine if a front month has changed. If it has, enter the new front month into the applicable Mo. column. After you have printed this form, use a red marker to draw an outline around each Mo. that is new to remind you that a fresh front month is being used.

I enter the daily closing price of each commodity onto the blank printed form in pencil. When both weeks are filled with closing prices, I transfer the data to the Excel form. Although this can be done on a daily basis, I find that doing it all at once every two weeks is a more effective use of my time. I use a ruler with this form, placing it under the commodity and its two weeks of closing price. While in Excel, I position the cursor (cell pointer) to the A week Monday column and enter the closing price for that date using the numeric keypad. I then press the Tab key to move the cursor rightward to the next day and enter the closing price for that day. When I have entered the Friday close for the second week, I press the Enter key in the numeric keypad. This moves the cursor to the next row, at Monday of the first week to repeat this process. When done, I temporarily save this form then print it for reference.

While I have this Excel form open and containing closing prices for all two weeks, I will also open the 10-Week Moving Average Tracking Form and transfer each Friday's close to the 10-Week Moving Average Tracking Form. I use the "Window" menu command to switch between these two forms. I will copy the data column (from the Feeder Cattle row to the Silver row) in each Friday's column (it's labeled "copy to 10-wk") to the next available column in the 10-Week Moving Average Tracking Form. Mark and copy the "A Week" Friday data first, followed by the "B Week" Friday data, switching between forms. Use the "Paste Special", "All except borders" option.
10-Week Moving Average Tracking Form (substituted filename used: 4.xls)
This form is used to help you determine the trend of a commodity. Transfer the Friday average to the corresponding date on the Price Chart and connect the dots to determine the existing trend.
Daily Update Form (substituted filename used: 5.xls)
This form helps you to track your market positions on a daily basis. Enter the date and the closing price to determine your Unit P/L.
Daily P&L Summary Form (substituted filename used: 6.xls)
This form summarizes all your active positions as a total.
Trading History Form (substituted filename used: 7.xls)
This form is completed when each position is closed, and presents you with a history of your Net P/L (profit & loss) for your positions.
Premium Tracking Form (substituted filename used: 8.xls)
This form is used to track the premiums for each commodity. It performs no calculations, but is useful to compare premium activity with price activity for analysis purposes.
Seasonal Chart (available with complete course)
This form displays a 5-year summary (by month) of commodity price activity to help you identify seasonal tendancy. It is a subjective construction from the weekly price charts to identify price action on a monthly basis for the most recent 5 year period. It performs no calculations, but is useful as an additional confirming tool (refer to "Seasonal Tendancy" in Chapter 6 for more details).

The codes used are "U" - price was up, "D" - price was down, and "F" - price was flat. The left-most code in each month column represents the oldest year.

A month with the same codes (4 or more, identified in bold) suggests a predictable seasonal pattern. It is updated every January.

C. Commodity Futures Trading Excel Forms

Resources:

Features:

Products: