Security Closing Price import

Emanuel Freitas
Emanuel Freitas Member ✭✭
Hello,

I'm trying to import a price history for a custom Security from a CSV file.
For some reason the Closing Price is always set to zero.

The content of the CSV file is:

Date,Open,High,Low,Close,Volume
2021/04/15,0.101112,0.156464,0.100755,0.152227,14968637584
2021/04/16,0.151709,0.365184,0.150763,0.305341,57927447083
2021/04/17,0.305531,0.312516,0.197400,0.237168,26217281487
2021/04/18,0.242250,0.273968,0.210184,0.255941,19310157824

But the result is always zero on Closing Price. All the other columns are OK.

Can you please help me identify what I'm doing wrong?

Thank you!

Answers

  • Quicken_Tyka
    Quicken_Tyka Alumni ✭✭✭✭
    Hello @Emanuel Freitas

    Thank you for taking the time to visit the Community to report this issue, although I apologize that you have not received a response.

    What region is the computer set in? Where are you getting this CSV or are you creating it manually?

    Please let us know!

    -Quicken Tyka
    ~~~***~~~
  • Quicken Francisco
    Quicken Francisco Alumni ✭✭✭✭

    Hello @Emanuel Freitas

    Thank you for reaching out on the community and telling us about your issue. I was taking a look to see what could possibly be wrong. In this case I'm wondering if the formatting might be slightly off. I would recommend taking a look at the article down below for information on formatting to see if something may be off.

    https://www.quicken.com/support/how-do-i-import-data-quicken-windows

    Once you've had a chance to check let us know what you're able to find out.

    Thanks,
    Quicken Francisco


  • jacobs
    jacobs SuperUser, Mac Beta Beta
    edited April 2021
    @Quicken Francisco the article you linked is for importing price data for Quicken Windows. Since this is for Quicken Mac, that information is incorrect. Quicken Windows can import multiple securities in one CSV file, but Quicken Mac cannot.

    This is the format for Quicken Mac:

         Date,High,Low,Open,Volume,Close

    I have read other users say that you need to provide all the header fields, but the data can be zeros if you don't have data like high, low, open or volume. All you really need is Date, 4 zero fields, and the closing price. The first line of the CSV file must be the field names, exactly as shown. I don't know if you can omit any of the fields, or put them in a different order but I just conducted this simple test for a dummy security:

    Date,High,Low,Open,Volume,Close
    4/1/21,0,0,0,10.1
    4/2/21,0,0,0,10.2
    4/3/21,0,0,0,10.25
    4/4/21,0,0,0,10.3
    4/6/21,0,0,0,10.35

    This CSV file imported correctly:



    Then, I took the data provided by @Emanuel Freitas, which is in a different order, and which has dates formatted differently, but all the required data and headers, and imported it to the same security.

    Date,Open,High,Low,Close,Volume
    2021/04/15,0.101112,0.156464,0.100755,0.152227,14968637584
    2021/04/16,0.151709,0.365184,0.150763,0.305341,57927447083
    2021/04/17,0.305531,0.312516,0.197400,0.237168,26217281487
    2021/04/18,0.242250,0.273968,0.210184,0.255941,19310157824

    It appears to have worked equally well:



    So the data is fine and imported fine for me.

    I would zero in on a question @Quicken_Tyka asked about your Mac's Region setting. It appears from the screen shot you posted that you are using commas as the decimal separator rather than periods. I'm not familiar with using US notation for a CSV file importing on a computer using non-US notation. You should be able to verify that the data is correct if you temporarily with the Mac to the United State region. If you change the separator character in the CSV file to a semi-colon (is it then called a SCSV file?), does the import work? Sorry, I don't have any experience with CSVs where the comma can't be used as the separator. 
    Quicken Mac Subscription • Quicken user since 1993
  • Emanuel Freitas
    Emanuel Freitas Member ✭✭
    Hi guys, thanks for all the answers!

    My Mac's Region is set to Europe/Portugal and yes, we use commas as the decimal separator. If I set the region, as @jacobs said, to US, the import is successful with all the values, including the Closing Price.

    Other thing that I noticed was that if I import (using region Portugal) a value like "10.337938" the imported Closing Price will be set to "10.00".
  • jacobs
    jacobs SuperUser, Mac Beta Beta
    If you change all the commas to semi-colons and periods to commas, does it import correctly?

    Quicken Mac relies on the macOS settings for numbers, so I would expect that if you input numbers that matched your region, it might work.
    Quicken Mac Subscription • Quicken user since 1993
  • Emanuel Freitas
    Emanuel Freitas Member ✭✭
    Hi jacobs,

    Thanks for the suggestion but it doesn't work. I get an invalid format error:
  • Chris_QPW
    Chris_QPW Member ✭✭✭✭
    Frankly it makes no sense that Quicken or any program would use the regional settings to determine how to separate out the fields.  At best it would be a "guess".

    Depending on who you ask or the CSV parser used some take CSV "literally", as in Comma Separated Values.
    Others are more flexible, and call it Character Separated Values, and then the separating character might be a tab or a semi-colon.  But to assume that it a given character based on the region settings of the user is just asking for trouble.

    Basically for the most part the CSV parser has to know upfront what the separator is going to be (or it has to guess, which is of course prone to errors).

    Even past this, people think of CSV as a standard, but it isn't really fully standardized, and what's more what is standardized talks more to how the fields are separated, and what is a "row" of data.  For instance data can certainly have a comma in it, how does it not take that as a field separator?  In general you quote it. 
    "Yes, I'm one field",4343.34

    So maybe something like this would work:
    "Yes, I'm one field","4.343,34"

    Note though that the CSV format say nothing about the interpretation of the fields.  Quicken Mac might use the regional settings for how to interpret the numbers, or it may not.

    But given that Quicken is billed as a US/Canadian product I would expect no attempt to try to make this parser flexible like this (on the other hand most likely they didn't right it is some built in function, but even so it would most likely have to be told to use a different separator).  The number interpretation by region is most likely built right into the Mac libraries and as such takes very little effort and as such is just "happy consequence". 
    Signature:
    This is my website: http://www.quicknperlwiz.com/
  • Emanuel Freitas
    Emanuel Freitas Member ✭✭
    edited April 2021
    > @Chris_QPW said:

    > So maybe something like this would work:
    > "Yes, I'm one field","4.343,34"

    Hi Chris, yes! It worked!

    I have something like this and all values are now OK:

    2021-02-22,0.046222,0.049940,0.038796,"0,044167",0.044167,2767774162
  • jacobs
    jacobs SuperUser, Mac Beta Beta
    Chris, all I was saying is that Quicken Mac depends on the macOS for its interpretation of numbers and dates and their formats, so the Quicken engineers don't have to deal with different standards used in different areas. I was questioning  whether using semi-colons as delimiters would have worked, but I'm glad to see that surrounding values with quote marks worked. 
    Quicken Mac Subscription • Quicken user since 1993
This discussion has been closed.