Once downloaded, the most efficient way to use our .csv composite datafiles on a regular basis is to leave them as is and to instead link them to an Excel (.xls) spreadsheet.  With both the .csv datafile and .xls spreadsheet opened from within the same directory (open the .csv file first then the .xls file), each cell in the spreadsheet links directly to a cell in the downloaded .csv datafile.

This .xls spreadsheet is where you will do your calculations.  On subsequent downloads, therefore, your work will not be overwritten when you update your data.  Save each of your own .xls spreadsheets with its own unique name (usually just the symbol) in the same directory that you download the .csv datafiles.  The supplied .xls spreadsheet may be used as a template for all your "working" spreadsheets  (save this downloaded spreadsheet in the same directory that contains your downloaded .csv files.

We will assume that you have already set up the MasterDATAlink program and downloaded daily data setting either  eSignal or Reuters as your data vendor in your download list settings.  It is also assumed that Microsoft Excel is installed on your computer.

One  of the datafiles downloaded should be the Dow Jones Industrial Average daily data.  The eSignal symbol for this index is $INDU.  The Reuters symbol is .DJI.  For purposes of this illustration, we shall use the Reuters symbol, .DJI.  The "Demo" MasterDATAlink downloads this datafile also.  So we are all on the same page.

Download the Spreadsheet Template from the MasterDATACSV.com web site by clicking on the following link:

Download Spreadsheet Template (for Reuters symbol _DJI.csv datafile)

In the next dialog window that appears ("Save as"), for this example, create a new folder called "Temp" in your "My Documents" folder.

In actual practice, you will want this Excel spreadsheet residing in the same directory as your downloaded .csv datafiles.

Click on "Save".

Click on "Open".

Click on "Extract".

Browse to the new "Temp" folder in your "My Documents".

Click on "Extract".

Close the WinZip program window.

Using Windows Explorer, go to the "C:\Documents and Settings\xxx\My Documents\Temp" directory (where "xxx" is your username):

First open the file "_DJI.csv".  Once that file is open, then open the file "_DJI.xls".  It is important to open the files in this order (.csv first, then .xls).

You should now have two files opened in Microsoft Excel, "_DJI.csv" and "_DJI.xls".  The values displayed in the .xls file are simply the values it is reading from your downloaded "_DJI.csv" file.  Every time you download in the future the Excel .xls file will reflect the new values of composite breadth data it reads from the newly updated or downloaded  .csv file (assuming you always download to the same directory containing your .xls spreadsheet).

Well, that is almost true.  There is one more important step that must be performed in the .xls spreadsheet file to allow it to properly display subsequent days of new composite data.  Since your .xls file is already open, switch to that file and go to the bottom row of the spreadsheet (a fast way to get there is to click somewhere in the spreadsheet, then while holding down the "ctrl" key, press the down arrow key).

For purposes of this tutorial, we have purposely omitted one row from the bottom of the .xls spreadsheet.  We are now going to add a new row so that all of the data contained in the .csv file will display properly.

In the bottom row of the opened spreadsheet, click in the column "A" cell.  While holding down both the "Shift" and "Ctrl" keys, press the right arrow key.  The entire bottom row of the spreadsheet should now be highlighted.

While holding down the "Ctrl" key press the "c" key (this copies whatever is highlighted to the clipboard).

Now, press the down arrow key one time making sure that the cell highlighted is one row below the last spreadsheet row and in column "A".  Then press the "Enter" key.

You have added a new line (row) of code to the bottom of your .xls spreadsheet that will display the data in the related row of data in the .csv datafile.  As new days of data are added to the .csv datafile in the future, similar to the above, add more rows to the .xls spreadsheet to properly display that data.

You can easily "clone" your .xls spreadsheet to display data and create "working" spreadsheets for all the indexes and ETFs you download.

First, make a copy of the original .xls spreadsheet and name it something recognizable (we use the index or ETF symbol).  For this example lets use "SPY".  So you should now have a newly copied spreadsheet named "SPY.xls".

Using Windows Explorer, locate and open the file "SPY.csv" in your "C:\MasterDATA\CSV\Daily\" directory.  Now open the new "SPY.xls" spreadsheet.

A Microsoft Excel dialog will appear.  Click on "Don't Update".  We want our newly created spreadsheet to link to the "SPY.csv" datafile, not "_DJI.csv", so we need to make some changes first before worrying about updating the new spreadsheet.

In addition to anything else you may have opened, you should also now have open "SPY.xls" and "SPY.csv".  We are now going to link them up.

Press the "F2" key (function key not two letters).  This will allow you to see the underlying formula of the cell.  Identify the symbol that was utilized in the prior spreadsheet formulas.  In this case the symbol is "_DJI".  So we now know we need to replace this symbol with the new symbol "SPY".  Press the "Esc" key.

Now, while holding down the "Ctrl" key press the "h" key (this activates the find and replace Excel function within the spreadsheet).  In this example, enter "_DJI" in the "Find What" text box and "SPY" in the "Replace With" text box.  Click on the "Replace All" button.  In a few seconds all the .xls links have been modified to point to the downloaded "SPY.csv" datafile  .

The first time through, this process may seem a bit mind-boggling, but stick with it.  Once you get the hang of it, the process is really very straightforward.  The more experience you have with Excel, the more you will see that it can do.

Work in your Excel .xls spreadsheets, not in the downloaded .csv datafiles.  Your calculations will thereby be preserved and applied to subsequent new data as it occurs.  The downloaded .csv datafiles contain the historical composite data.  The .xls spreadsheet contains and protects your work.  Don't forget to back it up regularly.