How to shrink your .QPH file (quotes file)

Posted by google on October 14, 2006, 11:55 pm

Please log in for more thread options

This group has helped me on a number of occasions so I thought I'd
return the favor by posting what I learned about how to shrink my qph
(quotes) file. I was driven to explore this because when my qph file
was 3.5m, Quicken 2006 was using 100% CPU for 10 minutes after I
entered each transaction before it would give me back control to enter
another transaction.

After following the process below, I've shrunk my qph file to 358k and
Quicken now processes each transaction immediately again. Hallelujah!
I sure wish Intuit would bring back the QPH file shrinking feature I've
seen mentioned in this group and remove the 1,024 symbols limit.

Here's how I shrunk my qph file in Quicken 2006:
- run Quicken, File/Export/QIF File with <All Accounts> and only
Security Lists selected, exit Quicken
- open the QIF file in Word (you can't go straight to Excel because
Excel has a max of 65,536 lines); delete all the headers until you
reach the first !Type:Prices line; use find/replace to delete all
!Type:Prices lines and all ^ lines
- save as a CSV file (if you aren't under 65,536 lines, you'll need to
save several parts as several CSV files)
- open the CSV file in Excel using only commas as delimiters (deselect
quotes as text delimiters); delete any remaining !Type:Prices lines and
^ lines; delete lines where the price is blank; change price fractions
(including whole numbers with spaces) to decimals, copy the date column
and remove quotes and spaces and change ' to / so Excel recognizes the
new date1 column as a date, create columns that extract the year and
month (or week if you want more frequent quotes) from the new date1
column, write a formula to find duplicate quotes (where the symbol,
year, and month/week match), delete the duplicate quotes, create a new
date2 column where the date is text instead of a number formatted as a
date, delete all columns except the original symbol and price columns
and the new date2 column, ensure that the symbol and date data still
have quotes surrounding them (may not be necessary but I was tired of
trying different options)
- save as a CSV file (if you aren't under 65,536 lines, you'll need to
save several parts as several CSV files)
- as a double check, open the CSV file in WordPad to ensure it looks
correct (e.g. quotes are the basic quote symbol not the fancy left and
right quote symbols, date is a text string not a number)
- rename or delete your original QPH file
- run Quicken, open the portfolio Investing Center, File/Import/Import
Prices the CSV file
- double check everything to ensure you're comfortable moving forward
with the new QPH file

David

Posted by John Pollard on October 15, 2006, 9:11 am

Please log in for more thread options

> This group has helped me on a number of occasions so I thought

> I'd

> return the favor by posting what I learned about how to shrink

> my qph

> (quotes) file. I was driven to explore this because when my

> qph file

> was 3.5m, Quicken 2006 was using 100% CPU for 10 minutes after

> I

> entered each transaction before it would give me back control

> to enter

> another transaction.

> After following the process below, I've shrunk my qph file to

> 358k and

> Quicken now processes each transaction immediately again.

> Hallelujah!

> I sure wish Intuit would bring back the QPH file shrinking

> feature I've

> seen mentioned in this group and remove the 1,024 symbols

> limit.

> Here's how I shrunk my qph file in Quicken 2006:

> - run Quicken, File/Export/QIF File with <All Accounts> and

> only

> Security Lists selected, exit Quicken

> - open the QIF file in Word (you can't go straight to Excel

> because

> Excel has a max of 65,536 lines); delete all the headers until

> you

> reach the first !Type:Prices line; use find/replace to delete

> all

> !Type:Prices lines and all ^ lines

> - save as a CSV file (if you aren't under 65,536 lines, you'll

> need to

> save several parts as several CSV files)

> - open the CSV file in Excel using only commas as delimiters

> (deselect

> quotes as text delimiters); delete any remaining !Type:Prices

> lines and

> ^ lines; delete lines where the price is blank; change price

> fractions

> (including whole numbers with spaces) to decimals, copy the

> date column

> and remove quotes and spaces and change ' to / so Excel

> recognizes the

> new date1 column as a date, create columns that extract the

> year and

> month (or week if you want more frequent quotes) from the new

> date1

> column, write a formula to find duplicate quotes (where the

> symbol,

> year, and month/week match), delete the duplicate quotes,

> create a new

> date2 column where the date is text instead of a number

> formatted as a

> date, delete all columns except the original symbol and price

> columns

> and the new date2 column, ensure that the symbol and date data

> still

> have quotes surrounding them (may not be necessary but I was

> tired of

> trying different options)

> - save as a CSV file (if you aren't under 65,536 lines, you'll

> need to

> save several parts as several CSV files)

> - as a double check, open the CSV file in WordPad to ensure it

> looks

> correct (e.g. quotes are the basic quote symbol not the fancy

> left and

> right quote symbols, date is a text string not a number)

> - rename or delete your original QPH file

> - run Quicken, open the portfolio Investing Center,

> File/Import/Import

> Prices the CSV file

> - double check everything to ensure you're comfortable moving

> forward

> with the new QPH file

Thanks; if I have the energy, I may try this myself.

I have a couple of notes to add.

1.) In versions prior to Q2006, the last price exported for each
security is garbage; you would need to delete that record (or
manually correct it). [Given the reduction in QIF file
"support" by Quicken for versions after Q2004, I was a bit
surprised to see this long standing bug fixed in Q2006.]

2.) If you have prices for securities that do not have tickers
(bonds, non-publicly traded securities, etc.), you will need to
assign a dummy, unique, ticker to those securities if you want
to export/import prices for them. (And you may need to
temporarily change the Quicken security "Type" to assign a
ticker.)

Posted by bjn on October 15, 2006, 9:29 am

Please log in for more thread options

It sure would be a whole lot nicer if Intuit would stop removing features
and charge us for "upgrades".

On 14 Oct 2006 20:55:29 -0700, google@personalfreedom.com wrote:

>This group has helped me on a number of occasions so I thought I'd

>return the favor by posting what I learned about how to shrink my qph

>(quotes) file. I was driven to explore this because when my qph file

>was 3.5m, Quicken 2006 was using 100% CPU for 10 minutes after I

>entered each transaction before it would give me back control to enter

>another transaction.

>After following the process below, I've shrunk my qph file to 358k and

>Quicken now processes each transaction immediately again. Hallelujah!

>I sure wish Intuit would bring back the QPH file shrinking feature I've

>seen mentioned in this group and remove the 1,024 symbols limit.

>Here's how I shrunk my qph file in Quicken 2006:

>- run Quicken, File/Export/QIF File with <All Accounts> and only

>Security Lists selected, exit Quicken

>- open the QIF file in Word (you can't go straight to Excel because

>Excel has a max of 65,536 lines); delete all the headers until you

>reach the first !Type:Prices line; use find/replace to delete all

>!Type:Prices lines and all ^ lines

>- save as a CSV file (if you aren't under 65,536 lines, you'll need to

>save several parts as several CSV files)

>- open the CSV file in Excel using only commas as delimiters (deselect

>quotes as text delimiters); delete any remaining !Type:Prices lines and

>^ lines; delete lines where the price is blank; change price fractions

>(including whole numbers with spaces) to decimals, copy the date column

>and remove quotes and spaces and change ' to / so Excel recognizes the

>new date1 column as a date, create columns that extract the year and

>month (or week if you want more frequent quotes) from the new date1

>column, write a formula to find duplicate quotes (where the symbol,

>year, and month/week match), delete the duplicate quotes, create a new

>date2 column where the date is text instead of a number formatted as a

>date, delete all columns except the original symbol and price columns

>and the new date2 column, ensure that the symbol and date data still

>have quotes surrounding them (may not be necessary but I was tired of

>trying different options)

>- save as a CSV file (if you aren't under 65,536 lines, you'll need to

>save several parts as several CSV files)

>- as a double check, open the CSV file in WordPad to ensure it looks

>correct (e.g. quotes are the basic quote symbol not the fancy left and

>right quote symbols, date is a text string not a number)

>- rename or delete your original QPH file

>- run Quicken, open the portfolio Investing Center, File/Import/Import

>Prices the CSV file

>- double check everything to ensure you're comfortable moving forward

>with the new QPH file

>David