ETF Constituent Lists and Data 
Over 1900 ETF Constituent Lists
Updated Daily 
For more info click here
By MasterDATA
MasterDATACSV Historical Breadth Datafiles


Historical breadth datafiles on 130 major stock indexes and all US traded ETFs in .csv format.  
Home Introduction Forum
Data Status For information on being automatically notified when data is delayed and/or in error, click here.
Contact Us    

  Using MasterDATACSV Composite Breadth Data In Excel

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.

Let's try it:
►     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 web site by clicking on the following link
  Download Spreadsheet Templatee (for Reuters symbol _DJI.csv datafile)
In the dialog that appears, click on "Save"
►     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.

Make sure you are in your new "Temp" directory.
Click on "Save".
In a few seconds, the "Download Complete" dialog will appear.
Click on "Open".
Assuming you are using WinZip, this dialog window will appear:
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.
►     Save your updated spreadsheet (hold down the "Ctrl" key and then press the "s" key).
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.
►     Switch to the open spreadsheet, "SPY.xls" and click on cell "A1" (top left corner).
►     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  .
►     Save your new "SPY.xls" spreadsheet.

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.
Search MasterDATA  


MasterDATA - Index and ETF Component Analysis - Home

MasterDATACSV Home  |  MasterDATAlink Download  |  Using Our Breadth Data In Excel
Breadth Data Downloads  |  "Plus" Breadth Data Downloads  |  Demo Breadth Data Downloads
To review MasterDATA's Privacy Statement, click here.
Disclaimer: This material is for your private information. We are not soliciting any action based upon it. Opinions expressed are present opinions only. The material is based upon information considered reliable, but we do not represent that is accurate or complete, and it should not be relied upon as such. We, or persons involved in the preparation or issuance of this material may, from time to time, have long or short positions in, and buy or sell the securities or options of companies mentioned herein.