Home Download Software Advanced Backtesting Download Databases Tips and Tricks Community

Did You Know? Did You Know? Did You Know?
Printable View
   
 
  If you have questions or an idea you'd like to see demonstrated here, please send your comments to: RWmailbag@zacks.com
 

The Research Wizard contains a tremendous amount of data. Over 650 different items on over 8,200 stocks and up to 12 years of history.

You can access all of this data with a click of a button or create your own customized calculations, quickly and easily, to create specialized stock reports.

And of course, you can export all of this data to other programs such as Excel.

In this issue of Tips and Tricks, I want to show you a neat little Ďtrickí in organizing your data in Excel to give you more flexibility with all of the data you have at your fingertips.

Letís start off by building a screen and a report

Letís say I wanted to see data on a ticker (letís use AAPL) and I wanted to export its monthly price and its monthly P/E ratio to an Excel spreadsheet for further analysis.

  1. In the Categories window, select Company and then the Sub-category Descriptive.

  2. In the Items window, select Ticker.

    Double-click it with your left mouse and the Screen By window will pop up.

    015_102
  1. Change the drop-down operator box to Ď = Ď (equal to) and then type in Ďaaplí in the box to the right and click OK.

    015_103
  1. Now letís get our data.

    First weíll change the database. Go to File on your Menu Bar and select Open Database.

    015_104

    The Zacks Database window will pop up.

  2. Select the DBZHE Ė Monthly Historical Database and click OK.

    015_105a

    Note: Youíll know youíve selected the right database by looking at the Database and its acronym above the Items window.

    015_105b

  3. Now letís get our data items.

    In the Categories window, select Price and Returns.

    And then in the Items window, select Month-end Price.
  1. Then right-click it and a Menu will appear. Left click the Add Database ††Item to Report option.

    015_107a

    A Report Column window will then pop up. (See below.)

    015_107b

  2. Click the History box and a new matrix will appear.

    Weíre going to grab the monthly data for the last 5 years, so letís keep the first box on the left set to 03/2009. And letís set the next box on the right to 3/2004. (See below.) Then click OK.

    015_108a

    The data will now populate the Report Definition table.

    015_108b

Now letís grab the P/E data.

Youíll follow the exact same steps, except this time youíll be using the P/E ratio.

  1. In the Categories window, select Ratios and then the Sub-category P/E.

    And then in the Items window, select P/E using 12 month EPS.

  2. Then right-click it and a Menu will appear. Left click the Add Database†† Item to Report option.

    015_202

    A Report Column window will then pop up.
  1. Click the History box and a new matrix will appear.

    Like before, letís grab the monthly data for the last 5 years, so letís keep the first box on the left set to 03/2009. And letís set the next box on the right to 3/2004. (See below.) Then click OK.

    015_203

    That data will now also populate the Report Definition table.
  1. Click the Run Query button ...

    015_204a

    ... and the Report page will pop up.

    015_204b

In the Research Wizard, all of the data for each ticker is organized in columns in one row.

When you export it to Excel for instance, the data will also export the same way into the spreadsheet Ė all in one row for each ticker.

But you can quickly reorganize the data however you choose once in Excel.

First, letís export the data.

  1. On your Menu Bar, go to Data and then select Export Data to Excel.

    015_301

  2. Once in Excel, Iím going to Ďclean-upí my view by first deleting one of the ĎTickerí columns that appears in my report. (As a default, a Ticker column appears in every report. But when I specifically called upon the ticker AAPL, that item was added to my report once again.)

    So left click on Clolumn C and itíll be highlighted. Then right-click it and select delete from the menu that pops up.

    015_302a

    Once you do, that Column will be deleted, leaving you with just one Ticker
    column

    015_302b
  1. No letís automatically resize each column so we can read each column header.

    Above Row 1 and to the left of Column A, left click your mouse. Your whole spreadsheet will be selected, i.e., itíll turn a light blue-ish.

    015_303
  1. Now, with your mouse, hover between Columns A and B. you see your mouse pointer turn into a cross with arrows on the sides. (Made white in the image below to see more easily.)

    015_304
  1. Double-left click the cross and each row will be resized so the full text on the columns can be seen.

    015_305

    You can see how the entire Column headers can now be read, unlike the image prior to the one above.

Next, letís grab our Price and P/E data and organize it in multiple rows so each periodís Monthly Price is in the same row and its Monthly P/E Ratio.

This will make the data easier to read (no need to keep scrolling from left to right to see all of the data) and it can now more easily be readied to create a chart or do additional analysis on.

  1. First Iím going to grab my Dates for the Prices.

    With your left mouse, left click on the cell under Column C, Row 1 and keep it depressed. Then drag it to the right until you reach Column BK and let go. It will still be highlighted.

    Now, position your mouse over any part of the highlighted area and then right-click it. A menu will pop up and youíll then select the Copy option with your left mouse.

    015_401
  1. Then, go to the cell in Column C, Row 6 and right-click it. A menu will pop up.

    Choose Paste Special.

    015_402a

    A Paste Special window will then pop up.

    015_402b
  1. Click the box next to Transpose and then click OK.

    Note: The ĎTransposeí button converts Horizontal data into Vertical ranges and vice versa.

    015_403

    Now the data which was once organized horizontally, is now organized vertically.

I can repeat these steps for each series of data items.

  1. Now letís get our Prices.

    With your left mouse, left click on the cell under Column C, Row 2 and keep it depressed. Then drag it to the right until you reach Column BK and let go. It will still be highlighted.

    Now, position your mouse over any part of the highlighted area and then right-click it. A menu will pop up and youíll then select the Copy option with your left mouse.
  1. Then, go to the cell in Column D, Row 6 and right-click it. A menu will pop up.

    Choose Paste Special.

    A Paste Special window will then pop up.
  1. Click the box next to Transpose and then click OK.

    The Price data will now be organized in a vertical manner just like the Dates.

    015_503
  1. Now letís get the Dates for the P/E Ratios.

    With your left mouse, left click on the cell under Column BL, Row 1 and keep it depressed. Then drag it to the right until you reach Column DT and let go. It will still be highlighted.

    Now, position your mouse over any part of the highlighted area and then right-click it. A menu will pop up and youíll then select the Copy option with your left mouse.
  1. Then, go to the cell in Column E, Row 6 and right-click it. A menu will pop up.

    Choose Paste Special.

    A Paste Special window will then pop up.
  1. Click the box next to Transpose and then click OK.

    The P/E Dates data will now be organized in a vertical manner just like the other data.

    015_603
  1. And lastly, letís get the P/E data.

    With your left mouse, left click on the cell under Column BL, Row 2 and keep it depressed. Then drag it to the right until you reach Column DT and let go. It will still be highlighted.

    Now, position your mouse over any part of the highlighted area and then right-click it. A menu will pop up and youíll then select the Copy option with your left mouse.
  1. Then, go to the cell in Column F, Row 6 and right-click it. A menu will pop up.

    Choose Paste Special.

    A Paste Special window will then pop up.
  1. Click the box next to Transpose and then click OK.

    The P/E Ratio data will now be organized in a vertical manner as well.

    015_703

Now your data is all laid out in columns and ready to be organized further if you choose.


Summary

There are so many other things you can do within Excel, so continue to explore all of its capabilities.

015_sumshot

And with the Research Wizardís vast databases of items and history, the power of both of these programs together can give you the edge you need to beat the market.

If you have other questions on how to organize reports within the Research Wizard program and/or Excel, please contact your Research Wizard representative as they can help you unleash the power and synergy of these two programs.


We welcome your feedback! If you have questions or an idea you'd like to see us demonstrate here, we'll show this in the next letter. Send your comments to: RWmailbag@zacks.com