Get Security Prices in QIF format to import into Quicken Mac 2007 or Quicken Windows
(See Links below)
I'm not associated with Quicken, I'm just providing a place for users of Quicken to find these patches since Quicken no longer makes all of them available.
Note tools for Excel should work on both Windows and Mac (not fully test yet). The "Numbers" solution is Mac only.
Note tools for Excel should work on both Windows and Mac (not fully test yet). The "Numbers" solution is Mac only.
From @DanaJ
QIF Generator.zip (Numbers)
Usage:
Just put the symbols you want into column A, and the date you want in cell A1. If you need more rows, just add them, making sure the formulas come along. Or delete rows if you need less. Then C2:C25 (in my case) can be copied and pasted into a plain text xxx.qif file for import.
From @kohster
Numbers-quote.zip (Numbers)
Usage:
Wow everyone thanks for sharing. I'd probably withdraw my Numbers submission given that fizzylogic's version is far superior to mine IMO (the most notable being able to do a range of dates, never mind the overall better look and feel).
From @dboltson
QIF Maker.zip (Excel)
Usage:
Just note that the data is not pulled into the spreadsheet automagically
(by Excel, or other)
but needs to be Copied from another source
(e.g., a cvs file exported from Morningstar)
and then Pasted in.
From @fizzylogic
Quicken 7 Stock QIF-Maker.zip (Numbers)
New version (V2.2) information:
Usage:
- Open file in Apple Numbers (minimum version 6.1)
- Enter start and end dates in green blocks provided (maximum of 31 consecutive days)
- Replace sample ticker symbols in green blocks with your own (maximum of 24), ensuring they match the format used by Yahoo Finance at https://finance.yahoo.com
- Alternate symbols may be entered in the adjacent column if those used in your Quicken Portfolio are not recognized by Yahoo Finance
- Numbers 6.1 may require quitting and relaunching to fetch latest quotes from Yahoo Finance
- Numbers 10.3.9 may only require recalculating (e.g., change start or end date) or closing/reopening the spreadsheet without quitting the program.
- Copy & paste all "QIF Report" rows below the header to a text editor and save as a plain text file.
- If the file name ends with “.txt” change it to “.qif” or append .qif if there is no extension already.
- Be sure to include the first row containing “!Type:Prices” and the final row containing the caret (^) symbol.
- In Quicken 2007 for Mac, click on the File menu, then select “Import” and “From QIF…”
- Navigate to the newly created .qif file and click “OPEN.”
From me @Chris_QPW
GetPrices.zip (Excel)
Usage:
You will have to "Enable Contents" (enable macros) to use it. Also your version of Excel must support the STOCKHISTORY function, and it looks that is Excel 2019/365 or later (as in just recently added).
Note that if you put in the "Days Back" that take priority over the Start and End Dates.
To use it put in as many symbols as you like and select Shift+Ctrl+G. That will create a Data tab with the prices (Data tab gets recreated each time). Shift+Ctrl+W will create the QIF file (it will ask where you want it). For "questions" like Auto Import 0 is no, any other number like 1 is yes.
Hopefully these shortcuts are OK on a Mac. If not they can be change by going to View -> Macros -> View Macros, select one for the Macros -> Options...
Note I had to use two Macros (Actual Subroutines) because Excel refuses to fetch the quotes until after it returns from the subroutine that puts in the formulas to get them.
QIF Generator.zip (Numbers)
Usage:
Just put the symbols you want into column A, and the date you want in cell A1. If you need more rows, just add them, making sure the formulas come along. Or delete rows if you need less. Then C2:C25 (in my case) can be copied and pasted into a plain text xxx.qif file for import.
From @kohster
Numbers-quote.zip (Numbers)
Usage:
Wow everyone thanks for sharing. I'd probably withdraw my Numbers submission given that fizzylogic's version is far superior to mine IMO (the most notable being able to do a range of dates, never mind the overall better look and feel).
From @dboltson
QIF Maker.zip (Excel)
Usage:
Just note that the data is not pulled into the spreadsheet automagically
(by Excel, or other)
but needs to be Copied from another source
(e.g., a cvs file exported from Morningstar)
and then Pasted in.
From @fizzylogic
Quicken 7 Stock QIF-Maker.zip (Numbers)
New version (V2.2) information:
Usage:
- Open file in Apple Numbers (minimum version 6.1)
- Enter start and end dates in green blocks provided (maximum of 31 consecutive days)
- Replace sample ticker symbols in green blocks with your own (maximum of 24), ensuring they match the format used by Yahoo Finance at https://finance.yahoo.com
- Alternate symbols may be entered in the adjacent column if those used in your Quicken Portfolio are not recognized by Yahoo Finance
- Numbers 6.1 may require quitting and relaunching to fetch latest quotes from Yahoo Finance
- Numbers 10.3.9 may only require recalculating (e.g., change start or end date) or closing/reopening the spreadsheet without quitting the program.
- Copy & paste all "QIF Report" rows below the header to a text editor and save as a plain text file.
- If the file name ends with “.txt” change it to “.qif” or append .qif if there is no extension already.
- Be sure to include the first row containing “!Type:Prices” and the final row containing the caret (^) symbol.
- In Quicken 2007 for Mac, click on the File menu, then select “Import” and “From QIF…”
- Navigate to the newly created .qif file and click “OPEN.”
From me @Chris_QPW
GetPrices.zip (Excel)
Usage:
You will have to "Enable Contents" (enable macros) to use it. Also your version of Excel must support the STOCKHISTORY function, and it looks that is Excel 2019/365 or later (as in just recently added).
Note that if you put in the "Days Back" that take priority over the Start and End Dates.
To use it put in as many symbols as you like and select Shift+Ctrl+G. That will create a Data tab with the prices (Data tab gets recreated each time). Shift+Ctrl+W will create the QIF file (it will ask where you want it). For "questions" like Auto Import 0 is no, any other number like 1 is yes.
Hopefully these shortcuts are OK on a Mac. If not they can be change by going to View -> Macros -> View Macros, select one for the Macros -> Options...
Note I had to use two Macros (Actual Subroutines) because Excel refuses to fetch the quotes until after it returns from the subroutine that puts in the formulas to get them.