Troubleshooting Tips 12-18
This is the list of Tips that are referred to in our various other guides. We recommend that users read through these.
If you are not able to figure out an issue on your own, please feel free to contact us at email@example.com
Details about Choosing and Entering Tickers
Enter the tickers carefully. Spaces before or after the ticker symbols will not work. Similarly, any other typo will prevent QPP from running successfully.
You need to make sure that all of the tickers that you enter are currently traded publicly (and listed at Yahoo!Finance.) You can check tickers individually at Yahoo!Finance. At Yahoo!Finance you can look at the historical period of each ticker and make sure that the data that you're asking for is available. The graphs available at the lower, right side of the Y!F screen for each ticker are useful in this quest. (QPP will give you an error message of "short record" if the problem is just the historical period, but this is a handy tool.)
If what you are trying to put in is not a valid ticker, you may need to use a proxy. For example, for money market funds that are mere cash repositories, you could use an ultra short bond fund such as PNCIX, or a publicly traded money market fund. To further illustrate this, let’s consider an example. VMMXX is not publicly traded; it is essentially a cash repository; QPP will not be able to use it. It does, however, show up at Y!F if you type in that ticker and hit “Get Quote”. The reason that it will not work in QPP is that the historical data is not available to QPP. The easiest way to identify this at Y!F is by the graph on the right side of the screen. This is the link to the screen for PNCIX, an ultra short bond fund that is publicly traded:
If you follow this link to Y!F you will note that there is a graph on the right side of the PNCIX page. (On the VMMXX screen there is no graph.) Under the graph, buttons are given for graphs of greater detail. The historical periods listed as graphing options (the buttons) already give you a sense of the historical period that Y!F has, but if you click on MAX, than you will be able to see how far back Y!F's data goes.
Individual bonds will also require the use of a proxy. You can use a bond fund, such as AGG, as a proxy when trying to model an individual bond. --or use an ETF that invests in TIPS (TIP). --or, you could look on Yahoo!Finance for a bond fund that suits your purposes.
If you have not yet run QPP successfully and encounter difficulties upon hitting the GET DATA button, please refer to Tip 14.
If you have been using QPP successfully and then encounter an error message after hitting GET DATA, then check the tickers. A test that you can perform to check whether the issue is being caused by one or more of the tickers, is to fill every ticker slot with SPY. To perform this test, please fill Start Date with 4/30/2008 (in that format) and fill End Date with 4/30/2011. Hit GET DATA. If this test results in every slot in the “check” column filling with OK, then you know that the tickers and/or dates that you were initially using need inspection. (Tip 16 is helpful in performing this test as it discusses how to cut and paste your portfolios into and out of Excel.)
For inspecting the required formatting for dates, please see Tip 13.
Number and Date Formatting
The formatting of numbers and dates must be in US English format. This includes what you enter, as well as your Excel’s reading of the entries. People speaking languages other than US English commonly have their Excel set up to read other number/date formats.
Please note that the dates entered by the user just above the GET DATA button, must be in U.S. format. For example: May 31, 2008 needs to be entered as 5/31/2008
The dates must be put in in the correct order. The earlier date goes on the left (Start Date), the more recent, on the right (End Date).
Also, numbers must be entered in U.S. format. For example: One hundred thousand dollars and 50 cents must be entered as 100000.50 or 100,000.50 (you do not need to enter the $.) The following European format will not work: 100.000,50
Changing Excel’s Format Setting:
Here are some indicators to look for if you are having issues, and how to remedy the problem:
On Page 1 of QPP check that all of the entries are in US format. If some of the numbers appear to be formatted European style -or other- (with a comma where US format would have a period) please check the Locale Setting (described below.)
On Page 3 of QPP, even if you DO have the dates typed in in US format, check that your Excel is able to read them. To do this, look directly below the GET DATA button. If Excel can read the US date formatting, it will instantly calculate and fill the "months" and "years" from that information. (Hit F9 if you are not certain that Calculations are set to Automatic as described in the Troubleshooting Guide, Tip 3.)
How to change the Excel settingExcel 2002-2006:
Please look under Format > Cells > Number. Then look for the box marked Locale. Please choose English US. Select OK. Then SAVE the whole file. This will do it.
For Excel 2007-2010:
Go to the Windows Icon at the upper left corner of the Excel screen. Click on this to pull down a menu. Go to 'Popular' from left most list. At bottom, you will see Language Settings. Choose' English (United States)'.
If this does not work, close QPP (and all other Excel files) but leave Excel itself open. Make the above change to the number formatting. Save. Then open QPP from within Excel (File > Open > . . . .> Quantext Portfolio Planner v4.0)
Troubleshooting "Run Time Error 1004"
Run Time Error '1004'
Unable to open
http://chart.yahoo.com/table. . .
Internet site reports that the item you requested cannot be found.
This error message can be caused by several different things. The following is a checklist designed to help users rectify the situation:
Trial users:Did you unzip/extract all of the files after downloading? Please see Tip 1 in the Troubleshooting Guide.
- Licensed users:Did you copy and save the files from the CD onto your computer (and NOT directly onto the Desktop)? This is also covered in Tip 1 of the Troubleshooting Guide.
- Did you enter the username and password correctly? Please see Tip 4 -and note that there is a 'check' for this issue.
- The issues discussed in Tips 3, 10, 11,13, 17 and 18 are important.
- If you continue having issues with Run Time Error messages, even after you have checked these Tips, do them in the following order. (For a very few users the settings will not remain set; the sequence of events has made all the difference.) (a)open Excel without opening QPP; (b) set "automatic" calculations (Tip 3); (c) disable auto-recovery; (d) WHILE STILL IN EXCEL, open QPP using File > Open > . . .QPP.
- Once you are up and running without issue, this error message simply means that one or more of the tickers is not acceptable. Regarding this, Tips 2 and 12 are the important ones.
- If you are unsure if the program is "up and running", try entering SPY into EVERY ticker slot and hitting GET DATA. Please make sure that there are no extra spaces after the ticker symbols.
- If QPP should get stuck on the green and yellow GETTING DATA page, press the tab labeled Portfolio Report at the bottom left of your screen to return to the planner.
Running QPP on a Macintosh:
Yes, QPP runs on Macs! (Please read carefully, however.)
As QPP is an Excel based program, Mac users have historically had more hurdles than other users. In Office 2011 (for Macs) the issue has apparently been resolved!! We have confirmed that QPP runs smoothly (and accurately) on a MacPro 1.1 (2006) as well as an iMac (2010), both running Office 2011 (for Macs). For this reason, we believe that QPP will run on any Mac that supports Office 2011.
If you do not have Office 2011, a workable solution is the following: If you have an Intel-based Mac you can install Parallels *see below* (which gives you the ability to switch to a whole different operating system on the fly.) Once you have Parallels with Windows with MS Office’s Excel, it is easy. You just start Parallels and make sure the networking part is working (e.g. by opening a browser in the Windows session and making sure you can get somewhere). Then open Excel, open QPP, and follow the directions. If you have an older Mac (Motorola processor) this solution will, unfortunately, not work.
*An option for Mac users besides Parallels is to use VirtualBox, which is free emulation software (http://www.virtualbox.org). As with Parallels, users must own a licensed copy of Windows, but with VirtualBox, you save the cost of Parallels.
Preserving a Portfolio and Allocations
In running different scenarios, there are several ways to preserve work so that reentering data by hand is not necessary:
- You can save multiple copies of QPP in differently named folders. You can only have one open at a time. (As noted in Tip 11, you cannot rename the QPP Excel file.)
- Another option is to open a fresh Excel file and paste your portfolios and allocations in there. Then you can grab them and paste them back into QPP when you want to return to a particular scenario. When pasting in allocation percentages, be sure to use “paste special” > “values”. You need to re-run “Get Data” whenever you paste different tickers into QPP. (However, you do not need to “Get Data” if you are merely changing the allocation percentages.)
You can paste QPP’s results into a different Excel file, also, which is a nice way to preserve/compare your results. To copy and paste, say page 3 of QPP, into a different Excel file, here are directions. The directions vary slightly for Excel 2007 (see below.)
For Excel 2002-2006:
Highlight and copy area of interest in QPP
Go to other Excel file, place cursor at the top/left portion of the grid that you want to paste the copied material into
From the Edit menu at the top of your screen, choose "paste special", then select "Formats", OK.
Go to the Edit menu again, (leaving the cursor where it is), choose "paste special" again, this time choosing "Values and number formats", the last option on the right, hit OK. That should do it.
For Excel 2007 and 2010:
Highlight and Copy area of interest in QPP. Open a different Excel file. (If you would like to create one now, go to the Windows Icon in the upper left corner of your Excel screen and select OPEN. Choose Blank Workbook. Hit Create. Later you can call this Excel file whatever you want, and keep it wherever you want, using Save As.)
Place the cursor at the top/left most portion of the grid to which you would like to paste the copied material.
From the Home Menu at the top of the Excel screen, choose the Paste button with the tiny arrow under it. "Paste Special", then select "Formats, OK.
Without moving the cursor, choose "Paste Special" again. This time, choose "column widths", hit OK.
Again, leaving the cursor where it is, choose "paste special". This time choose "values and number formats" (the last one on the right) and hit OK. That should do it!
- The third option is specifically for saving QPP reports -but it does not allow you to easily re-enter the portfolio back into QPP. If you would like to save an entire QPP report, you can create a pdf. If you do not have a pdf writer, CutePdf offers a free download. Once you have downloaded CutePdf, it will appear as an option when you select Print in Excel. If you select CutePdf, it will create a pdf and ask you where you'd like to save it. You can save it wherever you like, and call it whatever you like.
Firewalls on Company Networks
Other than the information given in Tip 8 about McAffee Security Systems, we have not heard of users having difficulties with security systems on their personal machines. However, we have had users run into trouble with firewalls when the computer is part of a company’s network. In these cases, the system administrators have had firewalls up that prevent QPP from going out the web to retrieve data.
In this case, the Run Time Error message pops up almost immediately upon the user hitting GET DATA. If you are experiencing a very quick Run Time Error response, please look at Tip 18. If that is not the issue, please talk to your system administrator about granting QPP permission to breach your company’s firewall.
You must be connected to the internet for QPP to be able to go out onto the web and retrieve data.
Saving QPP During Use or As Closing
If you save QPP, all of the Excel settings, your name, password, input from page 1, tickers and allocations will be saved.
For Excel 2007 or newer
At the top of the Excel screen, click on the Windows icon in the left corner > SAVE. A pop-up box will appear and ask "Do you want to save the changes...?" > YES. A second pop-up box will appear and give you a "Minor Loss of Fidelity" error message > choose CONTINUE. (This will not damage QPP.)
For Excel 2002-2006
At the top of the Excel screen, choose File > Save. Do you want to save the changes? > YES.