ABOUT XLTRADER ADD-INS for OFFICE 2007

Add-Ins are invisible workbooks that contain software (VBA progamming). When you install an add-in you are making the functionallity of a hidden workbook available. XLTrader is a combination of add-ins, a special template: HLC_Chart.xltm and some other essential files (xlls, dlls etc.). Theses files are designed to work together in Excel as a "Technical-Analysis Charting Tool". All XLTRADER ADD-IN files must reside in the same folder. Every time Excel is opened, the installed add-ins are also opened. If you are not using your add-ins, uninstall them to free up system resources. Uninstalling and reinstalling add-ins is easy.

BEFORE INSTALLING ADD-INS

One of the features that is new to Office 2007 Excel is the "quick access toolbar" or QAT. There are some Items you should install in your QAT that make it easier to use XLTrader Add-ins. They are the Chart Elements drop box, the Format Selection button and the Add-ins... button. Before we get started with XLTrader, start Excel and lets add these items to the QAT.

Click on the Microsoft Office button, the big round fancy button in the top left corner. Next, right click in the pop-up window to bring up the menu shown above. Select the Customize Quick Access Toolbar option.

From the Chart Tools: Format Tab, click on Chart Elements and add it to your toolbar, click on Format Selection and add it to your toolbar, lastly select All Commands from the "Choose commands from:" drop box, scroll down the list until you come to: Add-ins..., add it to your toolbar then press OK to close the dialog box.

INSTALLING ADD-INS

Installing an Add-In is not complicated but can be confusing the first time. You can use any directory you want but if you accepted the default location, the install program copied all files into a directory on your hard drive at C:\Program Files\XLTraderOffice2007. In that folder you will find a Folder: XLTraderv2.00. All of the files needed to run XLTRADER are in that folder.

There are two ways to install the add-ins: the correct way and the easy way. The correct way is more work but seems to be more reliable. The easy way to install the add-ins it to double click on each of the ten add-in files in the XLTraderv2.00 folder. (Add-in files are the ones with a .xlam extension). This should install them but if it does not work follow the steps below. It is a good idea to get familiar with the stardard procedure regardless.

Harder Way (i.e. Standard Procedure). Although it is not necessary, I suggest you copy all files in the XLTRADER ADD-INS folder (or just copy the entire folder to make it easier) into (one of) the Excel default add-ins directories: C:\Program Files\Microsoft Office\Office12\Library . This way you have a back-up copy. Another possible location is: C:\Documents and Settings\UserName\Application Data\Microsoft\AddIns (where UserName is the user’s Windows account name.)

Click on the Add-ins... button you installed on the QAT. If the XLTrader files are saved in one of the default add-ins directories cited above, the add-ins (might be.. don't ask me why all are not) listed in the dialog. Put a checkmark next to each of the add-ins. If an add-in is not showing in the list, click browse and navigate to the folder and select the add-in (either in the XLTraderV2.00 folder in the C:\Program Files\XLTraderOffice2007 directory if you chose not to copy the add-ins or in the C:\Program Files\Microsoft Office\Office12\Library if you did copy them), and it will then be added to the list. Put checkmarks next to the following items in the add-ins... dialog box as shown below:

Below the list of add-ins, you will see the name and description of the selected add-in. Click the OK button, and the add-ins will be installed and XLTrader is ready to go. To uninstall an add-in, simply click on the add-ins button in the QAT and remove the checkmark next to the add-in you are not going to use. Installing an add-in creates an "Add-Ins" tab in Excel 2007.

SAVING FILES

Files (spreadsheets) created using XLTRader Add-ins need to be saved in the macro enabled format ( i.e. as .xlsm files)

CREATING A TRUSTED FOLDER

The security settings in Excel 2007 can be major source of frustration with Excel always asking if you want to trust macros etc. The best way around this annoyance is to create a "TRUSTED" folder in which you will store all of your XLTRader spreadsheets. Files that are stored in trusted locations are not subject to the scrutiny that files in NOT-TRUSTED locations are. Lets make a trusted location in which to store our spreadsheets.

Click on the Office Button and choose Excel options at the bottom. In the "Excel Options" dialog box select choose Trust Center. Click on the Trust Center Settings button and in the "Trust Center" dialog box choose trusted locations. Click the Add New Location button and choose a place where you will store your XLTrader spreadsheets.

You might also want to change the default file location where spreadsheets are saved to the folder you specified above. If you do not do this you must navigated to your Trusted File folder every time you save a new spreadsheet.

Using the HLC_Chart.xltm Template

When using XLTrader you always start with the HLC_Chart.xltm template. The HLC_Chart template has embeded information the add-ins need to function properly and it is "event-enabled" which allows you to select points in the charts using your mouse. "Templates" is the first option in XLTrader menu (which can be found in the ADD-INS tab). Begin each new session by launching a new HLC_Chart template and replacing the default data that is stored in the first six columns with the data for your security (Note* you must supply the data.... XLTrader does not currently fetch historical data off of a server for you). The HLC_Chart template creates a custom toolbar with seven buttons on it. These are used for the following:

If you choose to copy and paste you data into the template, the data should be formated exactly like the data in the template. (Date accending order with dates formated as shown.) Do your data formating on another spreadsheet then copy and paste it into the newly created "clean" HLC_Chart.xltm template. After you have put your data into the first six columns of the Data sheet, You are ready to begin working.

Decide if you're going to work in Calender Days (which includes gaps for week-ends and holidays...s nice if you are working with planetary indicators) or Trading Days and select either one of the chart sheets. There are no X axis labels on the TD CHart. If you need dates, use the CD chart instead. Click on the auto-scale HLC_Chart toolbar buttons to make your data visible. If you do not see you data, Its probably because the chart axis' are not scaled properly. Use the zoom control to get chart scaling perfect. Use the Chart Object selection drop box you put on the QAT to select items (or click on them with your mouse) and the Format Selection button to make them exactly how you want them. Experiment! Have fun... You can't break anything. When you're ready ... save your new spreadsheet file (preferably in a Trusted Folder), make sure you choose macro enabled (.xlsm) format. Your spreadsheet will be just like you left it the next time you open it.

Selecting Points

Most of the subroutines require you to select points on a chart. When you are asked to select a point, usually you will need to click twice, once to select the series (High, Low Close etc.) and a second time to select the actual point you are interested in. If you don't get the correct point the first time just click "no" in the message box and try again. Sometimes it is helpful to zoom in on the point you want to select. Office 2007 makes this easy by providing a zoom control in the bottom right corner of each chart sheet.

Fetching Data from Yahoo Finance

The template can now be set up to download data automatically. However if you want download data manually, follow these instructions. You can use XLYahoo to define custom portfolios and download massive quantities of historical data from the Yahoo server. It gets put into a workbook containing a tab for each ticker symbol you specify. But sometimes that's over-kill (in-fact I hardly ever use XLYahoo anymore). What I prefer to do is put a hotlink to the data I need on my spreadsheet. You can do this by adding a text box to your chart that contains a valid URL such as the one shown:

which downloads S&P 500 (symbol ^GSPC) historical daily (g=d alternatively w for weely or m for monthly) data starting April 21st 2007 to October 10, 2009. Note* Puting the end date way off in the future ensures that you will always get the most recent data. You can also make a text link. I like to click on cell A1 on the Data sheet (which contains the word Date). Right click to bring up the menu and choose the hyperlink option. Paste a URL to your data and say OK. Now when ever you click on cell A1, it will fetch the data that applies to that spreadsheet off of the Yahoo Server. If you update your charts in the evening you will get the historical data including that days's data in a new spreadsheet. Formatting it for the template is easy... Just select Parse Data from the XLTools menu (and ignore any errors). Copy and past into your spreadsheet and you're done! Sounds messy but its not... its easy with practice.

Take your time! XLTrader is the result of years of effort on my part. There is a lot to be learned and its going to take you some time to explore. Watch the QuickTime videos in the Files area of the group. Have fun. Experiment. You cannot break anything. Ask questions on the forum. Share your Charts. Tell a friend.

Thank you and best wishes with your TA,

Sincerely, Rick Gibbs (aka XLTrader)