 The magazine of the Melbourne PC User Group Importing Share Prices into Quicken When Downloading Won’t Do Deanna Ross |
Think how useful it might be to easily import security closing prices into your version 8 Quicken Personal or Personal Plus Software or into Quicken Cashbook. It is even more useful if you are still running version 7 or earlier where you are no longer able to download prices. You may also want to obtain missed prices after a holiday, or you may have timely access to end of day prices from other charting software that you can easily reformat using Excel ready to import into Quicken. You may need to duplicate a particular share price history and import it into a new security as in the recent case of Coles Myer and Coles Myer Discount Card shares.
I will explain the procedure for importing security prices into Quicken, also how to export them should you wish to. Clearly,
importing is most useful where you may be missing a large number of prices for one or more securities. If you only need a couple of prices, it is simplest to add them individually on the appropriate date in the Portfolio View or by going to the Security Detail View, clicking the Update button and selecting Edit Price History.
Exporting, on the other hand, has more limited uses, but could be used to copy the price history of a security from one Quicken file into another or, as in the case of Coles Myer mentioned above, to duplicate the price history into a new
security in the same file.
My instructions in this article include detailed steps to help you in the use of Excel 2000. However, bear in mind that this may not work in all situations, for various reasons, including that different Web sites present their data in different ways.
Importing Prices Into Quicken
Before importing, you will either have exported prices from a Quicken file (see next section)
or you will have down-loaded prices into an Excel file from one of a few Internet sites that provide free share price history
or you will have copied data from your charting software and pasted it into an Excel file
and
You will have organized the columns into the correct order
and
You will have saved this data to a file in .CSV format (CSV standing for comma separated values).
Follow steps A, B and C below to save your file in .CSV format. Then go straight to step D to import.
A
(i) Download prices from an Internet site into an Excel file.
The following example extracts price history by share for a range of dates from http://www.egoli.com.au. You will need to register, but there is no charge. Once you have registered and are on the site, select
"Stock Quotes" from the choices offered under "Contents" and a
Quotes page will open. Before entering the ASX code, click the cursor over the word
Historical just above the quotes fields. When the "historical
data" page opens, enter the ASX code and click the "View" button. The share price history will appear on your screen. Scroll down the page and click on the
"Save" icon. Now choose "Open this file from its current
location" and click OK. The historical price data for your selected security will open into an Excel spreadsheet.
Now go to step B.
or
(ii) Export prices from a Quicken file to a new file and then open this new file using Excel (see Exporting instructions below).
or
(iii) Open your charting software package, then open a chart of the security whose prices you wish to import into Quicken, now right click on the price chart and select
"Copy" from the choices given then open a blank Excel file and click
B
We are going to organize this Excel file into the correct format for importing. The file needs:
- a column for the ticker symbol, followed by
- a column for the closing price and lastly
- a column for the date which should be in one of the following
formats:
- dd/mm/yyyy
- dd/mm/yy
- ddmmyyyy or
- ddmmyy.
Now delete columns and rows that are not needed such as the columns for open, high, low, volume, trades, value, open interest etc. These fields can't be imported, so delete the columns from your Excel file by highlighting the letter at the top of the column and selecting Edit|Delete. Similarly, delete any rows which are blank, headings or don't contain any price data by highlighting the row number to the left of the row and selecting Edit|Delete.
You should now have an Excel file with two columns, one containing closing prices and the other containing dates.
You may need to insert a blank column for the ticker symbol in front of column A. Click on the A at the top of the column and select Insert|Column.
Now click in square A1 to make it the active cell, and in this cell enter the ticker symbol for the prices to be imported, say WOW.
Click again in cell A1.
Drag the pull-down handle at the bottom right hand corner of the cell down column A, until the whole of column A in the active area of the spreadsheet is filled with this symbol.
Now we will move closing prices to column B if they're not already there. (Do not overwrite dates if they are in column B. If this is the case, first insert a blank column to the left of the date column.) Highlight the column that contains closing prices by clicking on the letter at the top of the column.
Select Edit|Cut
Then click the B at the top of column B and select Edit|Paste.
In the same fashion move the dates column to column C.
The screen shot in Figure 1 shows examples of two different Excel files after columns have been rearranged into the right order.
Note: If all prices you are importing are for a single date, you can optionally omit the date column, because later you can specify the date when you import into Quicken. Be aware that if there are dates in your .CSV file they will override any specified date when importing.
|
|
|
C
Save this Excel file in .CSV format:
Select
File|Save As
from the main menu and a Save as window will open.
In the "Save in" field, enter a location (drive and folder) for this file that you will easily remember. We will choose the A: drive.
Select the "Save as type" to be .CSV (Comma delimited)(*.csv) and choose an easily remembered
"file name". The ticker symbol will do, eg. WOW for Woolworths Ltd. (Don't include the dot and the characters csv here as these will be added automatically.)
Make sure a diskette is in the A: drive and click Save.
You may receive the following warnings:
-
The selected file type does not support workbooks that contain multiple sheets. Click OK in response to this.
-
WOW.CSV may contain features that are not compatible with .CSV (comma delimited). Do you want to keep the workbook in this format? Click Yes to answer this question.
The file will now be saved as A:\WOW.CSV on the diskette.
D
Go back to Quicken and open the file where you want to import security prices. The Portfolio View must be open and visible for you to be able to import.
With the Portfolio View window open Select
File|File Operations|Import Prices
An Import Price Data window appears.
If the .CSV file is on diskette, make sure it is in the A: drive.
Enter the file name A:\WOW.CSV in the "File (full name or full path)" field, as in the screen shot shown in Figure 2.
Click OK, and when the import has finished a message will appear to inform you how many prices have been successfully imported. Click OK again to finish.
Remember that if there are dates in your .CSV file, these dates will override any date in the
Import Price Data window. If you are importing a .CSV file without dates, make sure that the date in the window is correct.
If it is wrong, it could be tedious to fix.
Another useful Internet site is http://www.tradingroom.com.au where you should navigate to Quotes and Charts, then click Charts. Enter an ASX code and click Go. Scroll down below the chart and click on Import Price History. Now choose
"Open this file from the current location" and proceed as described above for the
"egoli" site under A(i).
Another site http://www.hooyaya.com provides prices
by date for all ASX shares. After downloading from this site, however, you will have to reverse the date field in Excel from
yyyy/mm/dd to either dd/mm/yyyy or dd/mm/yy for Quicken. This can be easily done in the date field in Row 1, then fill the whole column with the same date, or alternatively omit the date column and specify the date when you import.
Note that if your csv file contains all the shares in the ASX, you can still use it for importing. In this case the only prices that will be successfully imported will be those whose ticker symbols are already in your Quicken file. Any superfluous ones will be conveniently dropped off.
Exporting Prices
(to an importable file on diskette)
You can export a security's share price history from Quicken so that subsequently it can be
|
a) imported into the same security in another Quicken file, or |
-
From Portfolio View click on a single shareholding, say CML (Coles Myer).
Right click to get a menu and left click on Price History. A Price History window will open.
-
Click on the print button, and a print window will open.
Click the radio button for print to: "Tab delimited disk file" and click OK.
-
A Create Disk File window will open. If you know how to locate new files on your hard drive, you can go ahead and create a new file on C: or D:
Here we will proceed to select the A: drive.
Replace the asterisk in the file name field with a suitable name to identify the file, say CML, which will result in the file being named CML.TXT.
Insert a diskette into the A: drive and click OK. Close the Price History window.
You now have an exported .TXT file which must be converted to a .CSV file before you can import its prices.
-
Open Excel by pointing to
Start|Programs|Microsoft Excel
-
The file is now ready to save as a .CSV file as follows:
Select File|Save As and a
"Save As" window will open.
Open the file A:CML.TXT in Excel as follows:
Select File|Open and enter
A:\CML.TXT in the file name field, then click Open and you will get a Text Import Wizard.
Change the "Start import at row" number from 1 to 2.
Click Next, click Next again, then click Finish. The text file will open in Excel.
If column A is filled with # symbols click on the A at the top of column A to highlight the whole column.
Select Format|Column|AutoFit Selection.
In order to be able to import successfully, the order of the columns in the .CSV file has to be changed. The ticker symbol should be first, the closing price next, and the corresponding date last.
With the A column still highlighted, select Insert|Column twice.
Closing prices are now in column D.
Click on the D at the top of column D to highlight it.
Select Edit|Cut (the selection will be enclosed by "marching ants")
Now click on the B at the top of column B to highlight it.
Select Edit|Paste (the prices column will be placed in the B column ahead of the date column).
Delete columns E, F & G by highlighting them and selecting Edit|Delete - because you can import only closing prices - not highs, lows or volumes.
Now click in square A1 to make it the active cell, and in it enter the ticker symbol. In the example of the new Coles Myer Discount Cardholder share it will be CMLC.
Click in cell A1 again.
Drag the pull-down handle at the bottom right hand corner of the cell down column A until the whole of column A in the active area of the spreadsheet is filled with this symbol.
Leave the "Save in:" location showing 3-1/2 Floppy (A:).
This time enter the "file name" as CMLC (remember, don't add the dot and CSV file extension here)
AND
choose CSV (MS-DOS) (*.CSV) as the "Save as type".
Now click the Save button.
You may receive the warning: CMLC.CSV may contain features that are not compatible with CSV (comma delimited). Do you want to keep the workbook in this format? Click Yes to answer this question.
The file will now be saved as A:\CMLC.CSV on diskette and is ready to be imported into Quicken, as in step D above.
Before you import prices, make sure you already have a security for Coles Myer Discount Card shares with ticker symbol CMLC in your Quicken file.
About the Author
Deanna Ross, deanna@mpx.com.au is a Melb PC member and an Accredited Quicken Cashbook Trainer.
Reprinted from the October 2001 issue of PC Update, the magazine of Melbourne PC User Group, Australia
