Import prices for multiple securities through csv

qazwsx
qazwsx Member
edited November 2019 in Investments (Mac)
In Quicken for Mac, I really need to be able to update prices (for international securities that won't update automatically) through a simpler process, ideally import through CSV like on the windows version, but also being able to enter on the portfolio screen would be a big improvement. Note I do NOT mean import price history for a single security in a CSV.
Update MULTIPLE securities through ONE csv.
Please let me know if this will be implemented within the next few weeks as if it is not I would like to get a refund instead.
Thanks.
6
6 votes

New · Last Updated

Comments

  • jacobs
    jacobs SuperUser, Mac Beta Beta
    @qazwsx There is an existing "idea thread" for the request to be able to enter security prices quickly and easily in the portfolio screen: go here and then click on the little arrow under the vote count in the blue box to add your vote. This idea currently has 102 votes (95 "legacy", which refers to the previous platform this site was on, plus 7 since moving to this platform). Getting to over 100 votes is usually an important benchmark, but that doesn't guarantee it will get acted on. You'll note that the status in the blue box shows "no status", which means the developers haven't said this is definitely going to get done or definitely not going to get done.

    I'm not sure I recall the idea of importing a CSV file with prices for multiple securities being a request before. I'm not saying it's not a good suggestion, only that I don't remember others asking for this previously.

    There are probably more than 500 pending idea requests, so expecting quick action on any one particular feature request is unlikely. In terms of whether it will happen within a couple of weeks, the answer is definitely no… unless it's already been created and scheduled for the next update. That seems unlikely since the status on that older idea thread isn't marked as "planned." You might want to wait until version 5.12 is released -- probably sometime in the next couple weeks -- just to make sure, but it seems highly unlikely this will be implemented in your refund timeframe.
    Quicken Mac Subscription • Quicken user since 1993
  • smayer97
    smayer97 SuperUser, Mac Beta, Canada Beta ✭✭✭✭✭
    This is the first request for QMac I have seen for using CSV to import multiple securities together.
    Have Questions? Help Guide for Quicken for Mac
    FAQs: Quicken Mac Quicken Windows Quicken Mobile

    Add your VOTE to Quicken for Mac Product Ideas

    Object to Quicken's business model, using up 25% of your screen? Add your vote here:
    Quicken should eliminate the LARGE Ad space when a subscription expires

    (Canadian user since '92, STILL using QM2007)
  • Quicken Anja
    Quicken Anja Moderator mod
    Hello All,

    The Community Support team regularly reviews long-standing posts and Ideas for relevancy and current interest. This Idea seems to have stalled and we would like to gauge the current interest in this request. 

    If you would like to see this idea implemented, please add your vote and a comment explaining how this idea would be beneficial for you. More information, including steps to vote and how to submit your own Ideas for future product features/improvements, is also available here.

    Thank you,

    Quicken Community Support Team
    -Quicken Anja
  • dipe_c
    dipe_c Mac Beta Beta
    edited April 26
    qazwsx said:
    In Quicken for Mac, I really need to be able to update prices for MULTIPLE securities through ONE csv.
    You have my support and vote. If you're still interested in a solution here's how I work it currently:

    You'll need:
    1. Parallels
    2. Windows 10 installed as a virtual machine
    3. Excel 365 (note: not the standalone version) installed within Windows
    4. Excel for Mac (avoid Apple Numbers for this exercise)
    Shortcut: Get access to a Windows 10 with Office 365 installed.

    What you'll need to do:
    In Excel 365 (Win), you'll need to set up a Power Query for each of your securities in the same file. Don't worry, results will populate in separate tabs; each security to a tab.
    For e.g.:
    1. In Excel, click Data Tab. Select 'From Web' > Advanced. Put in your source url (lets say for Apple's historical prices from Yahoo Finance): https://finance.yahoo.com/quote/AAPL/history?p=AAPL
    2. "Access Web content" > Anonymous > Connect > wait for connection
    3. Navigator > Select Table 2 > Load.
    4. You'll now have the data downloaded in a new tab, as a table, and a side panel of Queries & Connections will load on the left. You'll now have to format the data for Quicken to accept it. So, mouse over to "Table 2" in Queries & Connections and select Edit.
    5. Select Column "Adj Close**" > Remove Column
    6. Select Column "Volume" > Change "Data Type" to "Whole Number" (Quicken import won't allow commas)
    7. Double Click "Close*" column and delete the asterisk
    8. In "Properties" Change name from Table 2 to Apple Inc
    9. Now "Close & Load"
      Your file will now have the format and headers that Quicken will require.
      Now, for adding your other securities:
    10. Under "Queries & Connections", Mouseover (don't click!) to "Apple Inc" and click on "Edit"
    11. Your Power Query Editor will open up again. 
    12. On the left margin, click the arrow to expand Navigator Pane 
    13. Under Queries, right-click Apple Inc and Duplicate
    14. Rename duplicate (under Properties) to Tesla
    15. Now click Advanced Editor. You'll see a box with your previous Apple query source. Just carefully edit the symbols to TSLA in the argument.
    16. Click Done. Give it a few moments, and the data for Tesla will download.
    17. Close & Load. Your new data will now populate a fresh tab in your Excel file.
    18. Repeat this procedure for each of your securities.

    Finally, you'll have an Excel (xlsx) file with all your Securities' price data. This is your master file. You can have all your prices perpetually updated (as long as your source is operational and on the same path) by simply going to the Data tab, and clicking "Refresh All". 

    When you want to import into Quicken (after a full data refresh), save a copy of the xlsx file as .csv: This is your source file for import into Quicken Mac. Import this .csv file over to your Mac.

    With Excel for Mac, open the CSV file: you'll see neat tabs with each one bearing the name of your chosen securities. Click on the tab of the security you want to import prices of. Save file (the selected tab is the active page: though csv shows you multiple pages, only the tab you save is the active page).

    Open Quicken Mac > Portfolio > Click on security > Edit Security > Options > Import History from CSV > point to the open csv file (ensure the tab security name matches your Quicken security) > Import > Done.

    Repeat for each of your securities, ensuring you've selected and saved the corresponding tab in your open csv file.

    Phew!

    Typing daily prices directly into Quicken Mac Portfolio would be so much easier... Better still, if data could be scraped from a more exhaustive repository. Delayed, or EOD data is usually free and would serve the purpose of Quicken users.
Sign In or Register to comment.