No. of Recommendations: 6
Introduction to the RadiScreen series

RadiScreen is a set of Excel macros that generate “stock picks” or “rankings” for screens, using screen definitions and downloaded stock data. RadiScreen can be downloaded from sites.google.com/site/TheGreatRadish/
It was written with Excel2003. You’ll find version numbers (as dates) on the Instructions sheets.

There are three spreadsheets that contain the macros:

RadInputData.xls is the input pre-processor that imports downloaded data into Excel. For certain file formats, it calls a program named TSIMPORT.EXE which must be saved in the same folder (directory) as RadInputData.

RadiScreen.xls is the actual screener macro.

And RadiStuff.xls contains accessories to do various conversions and other minor tasks.

There are three more spreadsheets that contain data that can be used with the screening macros:

ScreenDefs.xls contains definitions for most of the screens that were being posted weekly at The Motley Fool’s Mechanical Investing board at the time this spreadsheet was last updated. These screens are intended to use data downloaded from Value Line, so a subscription is required to run these screens (the Value Line Investment Survey for Windows Standard Version). (“Value Line” is a trademark of Value Line, Inc.) It may be possible to adapt some of the screens to use data downloaded from other sources.

IBDScreens.xls contains definitions for more of the screens that were being posted weekly at The Motley Fool. These screens were intended to use data downloaded from Investor’s Business Daily (the Daily Graphs Printed Product Company Index Report), which is no longer available, but similar data is available from MarketSmith. Most also use Value Line data.

SIPROScreens.xls contains definitions for still more screens that were being posted weekly. These screens use Stock Investor Pro data (from the American Association of Individual Investors). Again, a subscription is required to download the data.


About importing downloaded data

RadInputData has two macros to import tab-separated data (which can be used to import data downloaded with your Value Line subscription or from any other source that uses tabs to separate fields) and a macro specifically for importing the SIPro data. One of the macros can also import Excel files and comma-separated data. Once the data has been imported into the RadInputData workbook, it can be accessed by RadiScreen (as long as you keep RadInputData open in Excel, of course).

The first macro for tab-separated data can import files containing more than 254 columns of data. In RadInputData.xls, this macro is assigned to the button labeled “Click here to load new input text”. To import such large files, it uses a DOS program named TSIMPORT.EXE, which must be saved in the same folder (directory) as RadInputData.xls. It will create files named TEMPTXT1.TXT though (possibly) TEMPTXT9.TXT in that folder (and erase any existing files named TEMPTXT0.TXT through TEMPTXT9.TXT). If you do not wish to use this DOS program, or don’t want to erase/create those files, you can use the other macro instead (but note that it is limited to 254 columns of data, which is not sufficient for a full download from Value Line 3.0... so you would need to export fewer columns if importing Value Line data).

The second macro for tab-separated data (.txt files) can also import comma-separated data (.csv files) and Excel data (.xls and .xlsx files). It is assigned to the button labeled “Load Excel or Text file”.

The macro to import the SIPro data is labeled “Load SI Pro file”. It is also limited to 254 columns of data.

Both the “Load Excel or Text file” and the “Load SI Pro file” buttons can be used to load additional data from another data source after data has been imported using any of the three buttons. Either macro will detect the previously-imported data and automatically combine the two sets of data.


Step-by-step process to run all screens

Note that the following instructions are for Excel version 2003 and Value Line version 3.0.

Make sure you have downloaded all the appropriate files and have them saved in one folder (directory). The files you need are TSIMPORT.EXE, RadInputData.xls, RadiScreen.xls, ScreenDefs.xls, and IBDScreens.xls and/or SIProScreens.xls (if you have that data). You may also want RadiStuff.xls (especially if you are defining your own screens).

First you need to download data using your subscription(s) to your data sources. See the websites for your data sources for specific download instructions. Value Line data must be downloaded in tab-separated format. MarketSmith can be downloaded in either Excel or comma-separated format, but do not choose their text file format because it is not tab-separated.

The most convenient way to use the RadiScreen series is to create a “workspace” that includes all the workbooks you plan to use. This allows all the workbooks to be opened in a single step. Here’s an example:

1. Double-click on the icon for RadiScreen.xls to start Excel and open the RadiScreen workbook.

2. If you plan to run the IBD screens and/or SIPro screens, use File | Open to open IBDScreens.xls and/or SIProScreens.xls.

3. Use File | Open to open ScreenDefs.xls.

4. Use File | Open to open RadInputData.xls.

5. Use File | Save Workspace to save a workspace which has the workbooks you just opened. When the “Save Workspace” dialog box appears, you’ll need to type an appropriate name for the workspace in the “File name:” text box, then click on Save. Now you can close Excel.

The workspace file you just created will have a file extension of .xlw. Note that a workspace file does not contain the workbooks themselves, it just lists what workbooks to open and how to arrange them on the screen. When you double-click on the workspace’s icon, Excel starts, and all the workbooks that were open when you saved the workspace are automatically opened. Since the workspace file does not contain the workbooks, when you download new versions of workbooks (or make changes in your own workbooks), you do not need to create a new workspace — your workspace will open the latest version of each workbook, provided you save the new workbooks in the same folder (thus replacing the old versions).

OK, now that you’ve got your workspace created, here’s how to run the Value Line screens and, optionally, the IBD screens (skip ahead past the IBD screens for instructions on running the SIPro screens):

1. Double-click on the icon of your workspace to start Excel and open all the workbooks.

2. RadInputData will be “on top” if you followed the steps above. To import the Value Line data, click the button “Click here to load new input text”. The “Open” dialog box appears, and you locate your Value Line download file, and double-click on its icon.

3. Use Window | ScreenDefs.xls to switch to the ScreenDefs workbook (or, click on its lower border if it’s showing “under” RadInputData, or click on ScreenDefs.xls in the Windows task bar).

4. Click on the button “Clear Results” to clear any previous results.

5. Make sure cell A3, the cell containing the first screen in the List of Screens, is selected. Then click on the button “Run RadiScreen”. As RadiScreen evaluates all the screens, Excel’s status line (at the bottom of Excel’s window) will show the progress.

When the macro finishes, the results will be available in the Results worksheet in the ScreenDefs workbook. (Note: do not save RadInputData.xls after you have loaded data into it.)

If you wish to run the IBD screens, continue with these steps:

1. Switch back to RadInputData.xls and click on the Instructions tab to show the Instructions worksheet.

2. Click on the button “Load Excel or Text file”. When the “Open” dialog box appears, locate your MarketSmith download file and double-click on its icon. As the data is reformatted and combined with the Value Line data, the progress will show in Excel’s status line.

3. Switch to IBDScreens.xls.

4. Highlight the entire list of screens (which begins at cell A3), and use Edit | Copy.

5. Switch to ScreenDefs.xls, and find a convenient place to put the list of IBD screens, like cell C14 (do not use column A or B). Click on cell C14, then use Edit | Paste.

6. Click on the first screen in the IBD list (C14), then click on the button “Run RadiScreen”.

The results will be added to the Results worksheet. (Note: the screen T1_TEST should have no stock picks. If stocks are listed for this screen, those stocks were missing from the MarketSmith data. Their data needs to be manually entered into the MarketSmith download file, and the screening process must be redone.)

Here’s how to run the SI Pro screens:

1. Double-click on the icon of your workspace (see instructions on creating a workspace at the beginning of this section) to start Excel and open all the workbooks.

2. RadInputData will be “on top” if you followed the steps above. The SIProScreens.xls spreadsheet was written before there was a button in RadInputData to import SI Pro data. Therefore, its screen list starts with macro calls to clear any previous results and to call RadInputData to import the file. You can use those by using Window | SIProScreens.xls to switch to the SIProScreens sheet, making sure cell A7 is selected, and clicking on the “Run RadiScreen” button. Alternatively, with RadInputData showing you can click on the “Load SI Pro file” button instead.

3. Whichever button you clicked, the “Open” dialog box appears, and you locate your SI Pro download file, and double-click on its icon. SI Pro data is downloaded as two separate files, both of which have the same name except one has “_Key” added to the name. Both files must be saved in the same folder (directory). Double-click on the file that does not have “_Key” in the name.

4. If you didn’t use the “Run RadiScreen” button method in step 2, use Window | SIProScreens.xls now to switch to the SIProScreens sheet, make sure cell A9 is selected (not cell A7 or A8 which contain the macro calls), and click on the button “Clear Results” to clear any previous and then on the “Run RadiScreen” button.

5. As RadiScreen evaluates all the screens, Excel’s status line (at the bottom of Excel’s window) will show the progress. When the macro finishes, the results will be available in the Results worksheet in the SIProScreens workbook. (Note: do not save RadInputData.xls after you have loaded data into it.)


Tips for using RadiScreen


Create your own workbook of screens

You can create a workbook for your own screen definitions. Here’s how:

1. Create a new workbook with at least 3 worksheets. Name the worksheets Main, Definitions, and Translations.

2. Open RadiScreen.xls, select the Main worksheet, and select the entire sheet by clicking on the empty box above row 1 and to the left of column A (or just type ctrl-A). Then do Edit | Copy (or type ctrl-C).

3. In your new workbook, select the Main sheet, select cell A1, and do Edit | Paste (or type ctrl-V).

4. Repeat the copying process for the Translations sheet.

5. On the Main worksheet of your new workbook, highlight the list of screens (which starts at cell A3) and press the Delete key on your keyboard to erase it.

6. Create your own screen definitions on the Definitions sheet of your new workbook, and/or copy in definitions from ScreenDefs.xls or other sources. Put a comment (a line beginning with a semi-colon) giving a brief summary of the screens or any other info you wish in cell A1 (otherwise the CreateScreenList macro, below, will list the first screen last). When you type screen definitions (or copy them in), leave column A blank (that is, start each statement in column B). Column A is used by macros in RadiStuff for URL’s and postable text.

7. Open RadiStuff.xls.

8. In your new workbook on the Main worksheet, select cell A3. Do Tools | Macro | Macros (or type Alt-F8) and run the macro CreateScreenList by double-clicking it. This will list all the screens defined on your Definitions sheet.

The buttons to run RadiScreen appear on the Main worksheet in your new workbook. Note, however, that the actual code is still in RadiScreen.xls, so you must have RadiScreen.xls open in order to use these buttons. Also, RadiScreen needs RadInputData.xls open to function. Of course, you can create a workspace (as described above) to automatically open all the necessary workbooks.

Create your own list of pre-defined screens

You can create your own workbook to run only the screens you are interested in, using the definitions in ScreenDefs.xls. Here’s how:

1. Create a new workbook. Name a worksheet Main.

2. Open RadiScreen.xls, select the Main worksheet, and select the entire sheet by clicking on the empty box above row 1 and to the left of column A (or just type ctrl-A). Then do Edit | Copy (or type ctrl-C).

3. In your new workbook, select the Main sheet, select cell A1, and do Edit | Paste (or type ctrl-V).

4. Highlight the list of screens (which starts at cell A3) and press the Delete key on your keyboard to erase it.

5. Open ScreenDefs.xls, and on its Main worksheet, select a cell containing the name of a screen you wish to use. Do Edit | Copy (or type ctrl-C).

6. On the Main worksheet in your workbook, select cell A3, and do Edit | Paste (or type ctrl-V).

7. Repeat the above two steps for each screen you wish to use, but paste each screen name just below the previously pasted screens.

The buttons to run RadiScreen appear on the Main worksheet in your new workbook. Note, however, that the actual code is still in RadiScreen.xls, so you must have RadiScreen.xls open in order to use these buttons. Similarly, the screen definitions are in ScreenDefs.xls, so it must be open as well. And, RadiScreen needs RadInputData.xls open to function. Of course, you can create a workspace (as described above) to automatically open all the necessary workbooks.

Another way to create your own list of pre-defined screens

In ScreenDefs.xls, place the word “Skip” (without quotes) in column B next to each screen you don’t want to get results for.

Organize the Results worksheet and select the maximum number of picks

You can arrange the screen names in column A of the Results worksheet in any order you like. The “Clear Results” button does not clear the screen names, only the list of ticker symbols selected. You can also format the Results worksheet however you like (within reason), and replace the headings in row 1 with whatever headings you like (except “Limit”, as you will soon see).

To select a maximum number of stocks for all screens, simply place the word “Limit” (without quotes) in row 1 of the Results worksheet. That will limit the results to the columns to the left of the word Limit.

Post your screen definitions, or get definitions from posts

The language used to define screens is called “RadiScript”. In RadiStuff.xls, you’ll find macros to convert RadiScript to text, which can then be posted to message boards. Similarly, you can take textual screen definitions and use a macro from RadiStuff to convert it back to RadiScript. See the Instructions worksheet in RadiStuff for more details.

Make screen definitions from backtester URL’s

RadiStuff.xls contains a macro to convert URL’s for Jamie Gritton’s MI Backtester to RadiScript screen definitions. See the Instructions worksheet in RadiStuff for more details.

“Debugging” your screens

You will have noticed that below the “Run RadiScreen” button is a button “Run RadiScreen and keep details”. If you use the “keep details” button, RadiScreen creates a new workbook (although it may not be immediately obvious, because it may be “behind” other workbooks). It will contain a worksheet for each screen; the name of the screen is in cell A1 and on the worksheet’s tab. Each stock selected by the screen is shown along with all the columns of data used.

Of course, the “keep details” button doesn’t show the data for stocks that weren’t selected. You can get this information for a single screen by changing a variable in RadiScreen’s macro code. In Module 1, near the top, change the variable KeepSteps from False to True. Then run a single screen (this is easily done by copying the screen name from the list in column A to some cell to the right of column B, and selecting that cell before running RadiScreen). Worksheets named “TempResults1”, “TempResults2”, and so on will be created. A new sheet is created before each step with removes or sorts stocks.

If you’re single-stepping the RadiScreen macro code (that is, viewing the code execution in break mode), you may wish to change the variable ShowItAll from False to True. This enables screen-updating at each step.


Questions & Answers


How do I download data from Value Line?

First, you must subscribe to Value Line’s service. The product you need is called the “Value Line Investment Survey for Windows Standard Version”. Fireballs has created a great step-by-step guide called “How to Use RadiScreen” which can be downloaded from the website mentioned at the beginning of this text.

Why do I get “Screen not found”?

If you run, for instance, only the screen SOS_Elan_v2002, you’ll get a message like “Screen ‘EGPLOW_PE_E’ not found on Results sheet. Workbook ‘ScreenDefs.xls’ Worksheet ‘Definitions’ Row 1663”. This is because SOS_Elan_v2002 uses the screen EGPLOW_PE_E (and several others), so these screens must also be run. In fact, they must be run before SOS_Elan_v2002 is run.

If you look closely at the List of Screens on the Main worksheet of ScreenDefs.xls (which starts at cell A3 and goes down), you’ll see the screens are in alphabetical order, except the list starts over at various points. The list contains 4 groups: base screens (presently AssRS13 through YLDYEAR), Screen-of-Screens which are also called SOS’s (KEYEPS through SOS_Plow_RS), Overlap screens (Olap_RS52keystone through RSPEGOL), and Screen-of-SOS’s (presently SOS_Annual through SOSBM_E).

The base screens appear first, because some of them are used in the following SOS’s and in the Overlaps. The Screen-of-SOS’s appear last, because they use SOS screens, Overlaps, and base screens. If you create your own List of Screens, you must be sure to place screens that are used by other screens in your list in the proper order. When a screen uses another screen in its definition, the screen it uses must appear earlier in the List of Screens.

Once you’ve verified that your List of Screens works properly, you can rearrange the screen names on the Results worksheet to any order you like (but don’t rearrange the List of Screens — that’s the list that you select the first screen before clicking on the Run RadiScreen button). As long as the screen names on the Results worksheet start at A2 and continue down (without skipping any rows), RadiScreen will find the right place to put each screen’s results. Clicking on the Clear Results button does not clear the screen names, it just clears the stock picks. (Be careful, though, not to list a screen on the Results worksheet that doesn’t appear in the List of Screens, or rearrange the List of Screens after testing it, because then a screen that uses another screen may find that screen on the Results worksheet before the stocks picks have been placed there.)

Where are the results after I click on the ‘Run RadiScreen’ button?

They are on the Results worksheet. Click on the Results tab (at the bottom), or press the F5 key and type Results!A1 in the “Reference:” text box and click OK.

Lately my car broke down, I caught a cold, I lost my wallet, and I no longer win those scratch-off things. Why?

It’s probably because you’re using RadiScreen but you haven’t made a donation to the author yet. This is easily solved by following the donation instructions found on the Instructions worksheet of RadInputData, RadiScreen, or RadiStuff.


Phil
Print the post  

Announcements

When Life Gives You Lemons
We all have had hardships and made poor decisions. The important thing is how we respond and grow. Read the story of a Fool who started from nothing, and looks to gain everything.
Contact Us
Contact Customer Service and other Fool departments here.
Work for Fools?
Winner of the Washingtonian great places to work, and Glassdoor #1 Company to Work For 2015! Have access to all of TMF's online and email products for FREE, and be paid for your contributions to TMF! Click the link and start your Fool career.