Converter for ASX price data in CSV format to QIF File
I found what looked like a good, flexible utility (CSV2QIF for PC) to convert bulk share price and volume data downloaded from the ASX from CSV format to Quicken QIF format.
But when I open Quicken and import the QIF file, it asks if I want to create new securities, I say 'yes' and the 1900+ securities appear in my securities list, but none of the data appears in my prices.
I am wondering if there some problem with security names in the converter. The ASX data is in the format, eg: "BHP", not "BHP:AU". I have not manually edited the CSV file to add the ":AU"
If anyone has time to try the utility to see if you can replicate my experience, I would appreciate it.
Failing that, I would like to know about any other reliable CSV to QIF share price converter.
Bob
Comments
-
Bob,
There is a lot of information available in other posts on this forum.
My brief recommendations are:
You do not want the 1900+ companies in the security list, so don't create them, else go to a backup and start again !!
I would import .csv rather than .qif format files. The extended .csv format are in earlier posts.
I would only import the company history data you want and not the 1900+ companies you have in your data source. Generate the required codes in QPP before importing, rather than letting QPP generate them directly.
Given you have a .csv file, the best approach is to import this data into Excel and reformat it in a .csv file format that QPP recognises. If you have Excel skills this can be the best approach. via look up tables for filters and mapping for code changes etc.
The company code in the .csv must match the company code you have defined in QPP. For BHP it should be BHP and NOT BHP:AU, unless you still have that format, which QPP/RPP used in about 2004.
One of the problems with importing historical data is that ASX company codes change from time to time, eg AGL, Star Entertainment and lots of others. You need to convert the old codes in the .csv data to the latest code in QPP.
Where companies have done a restructure/consolidation, I am not sure what price to use. You can tell QPP there was a restructure at a certain date. I am not sure what price to import before that date, eg FMG and Westfield trusts (which were a nightmare to restructure in QPP)
Based on experience, only import codes that are defined in QPP, especially if importing .qph data or the long format of .csv (date, price, high, low, volume), as it will generate new company codes. I think the short format .csv file (date,price) will ignore codes it does not store.
Once data is in the .qph file, it can not be deleted, so can slow down QPP, especially if you have those new 1900+ companies. You may want to go back to a backup, before the new companies were generated.
I hope this is a good range of info to provide.
John
0 -
Thanks to Dave and John for their helpful replies.
BTW, I am doing my experimenting on a 'Test file' not my live file, to avoid the nightmare of trying to delete unwanted securities, which is a multi-step process because QPP automatically creates one transaction for each imported security that has to be deleted before the security itself can be deleted.
I have a question about the csv file structure required by QPP.
1. My file has a header row: ' Security Code' 'Closing Price' 'Date '.
The data after the header row has prices as DD.CC and Date as DD/MM/YYYY.
Should I delete the header row before importing into QPP?
Bob0 -
Bob,
1) if it is just text, it should be skipped. This is a good idea as the first record.
You can inspect the file with notepad.
The following link has some more information.
for example:
AMC,14.55,---,23/03/2015,---,14.7,14.47,22149,*You can place the --- and * in a separate columns in excel, as '--- and '* and then save as a .csv file
so:
column A : code ( no trailing blanks, just 3 character code, as you should have in QPP)
column B : closing price
column C : separator '---
column D : European date as dd/mm/yyyy
column E : separator '---
column F : high price
column G : low price
column H : =round(volume/100,0)
column I : end of line '*
You can have thousands of lines, but check the code is valid first as this extended format will add new companies if they do not exist.
You can use a pivot table to summarise the count of records for each company code.
An example of the shorter format text for date and price is (no separator fields):
AMC,14.55,23/03/2015
John
0 -
Thx again to Dave and John. I think you have both helped me zero in on something that seems to work (at least when Murphy is not watching).
I stripped the header from my CSV file (I am using John's 'shorter format') and manually deleted all except the 30 stocks that I need for Quicken. It all looks good in Notepad (another good tip I had forgotten for file inspection).
26 prices were successfully imported; I think the failures are due to wrong codes, but I have not checked that out fully yet.
BTW, my test file is called 'Prices 16sep16.csv' and the long file name worked, but I remember from earlier versions of Quicken that it does not really like long file names. It prefers the old 8-alphanumeric file names. Is that still good practice? I go back to DOS, guys, and I often wish I had stayed there!
Bob
0
Categories
- All Categories
- 6.4K Accounts Hosted
- 10 📢 Reckon Accounts Hosted - Announcements
- 5.9K Reckon Accounts (Desktop)
- 3 📢 Reckon Accounts Desktop - Announcements
- 1.3K Reckon Payroll 🚀
- 21 📢 Reckon Payroll - Announcements
- 21 Reckon Payroll Help Videos 🎥
- 21 Reckon Mate App
- 3K Reckon One
- 7 📢 Reckon One - Announcements
- 10 Reckon Invoices App
- 14 Reckon Insights
- 107 Reckon API
- 822 Payroll Premier
- 307 Point of Sale
- 1.9K Personal Plus and Home & Business
- 63 About Reckon Community