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.

   

3. Change the drop-down operator box to ' = ' (equal to) and then type in 'aapl' in the box to the right and click OK.

   

4. Now let's get our data.

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

   

   The Zacks Database window will pop up.

5. Select the DBZHE - Monthly Historical Database and click OK.

   

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

   

6. 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.

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

   

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

   

8. 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.

   

   The data will now populate the Report Definition table.

   

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.

   

   A Report Column window will then pop up.

3. 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.

   

   That data will now also populate the Report Definition table.

4. Click the Run Query button ...

   

   ... and the Report page will pop up.

   

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.

   

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.

   

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

   

3. 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.

   

4. 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.)

   

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

   

   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.

   

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

   Choose Paste Special.

   

   A Paste Special window will then pop up.

   

3. Click the box next to Transpose and then click OK.

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

   

   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.

2. 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.

3. 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.

   

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.

2. 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.

3. 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.

   

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.

2. 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.

3. 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.

   

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.

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.