Howdy, Stranger!
It looks like you're new here. If you want to get involved, click one of these buttons!
Quick Links
PowerPivot Quotes Table Close Price
I'm using Portfolio Slicer 2.3 for Excel 2013. I created a .txt file to import my price history for a non-symbol item. The price history is always 1.00 and the letters I chose for the symbol cause it to be listed first in the quotes.csv file. After importing the prices I noticed that my calculations were suddenly incorrect. I reviewed the "Close" column in the PowerPivot Quotes table and noticed all the numbers were whole numbers, no decimal. I then removed the newly imported price history and noticed everything returned to normal. Adding it back and it was messed up again, showing nothing but whole numbers.
My first attempt to correct this, after removing the newly assigned symbol, was to change the first price in the quotes.csv file to a number with two decimals (1.23) and import the data. I was wondering if the "Close" column would default to two decimal points. It did not and everything in the column displayed correctly, showing multiple decimal points.
I looked at the column properties for the "Close" column and noted the "Data Type:" set to Decimal Number but the "Format" was set to "General" I changed the "Format" to "Decimal Number" and extended the decimal format to six positions (1.000000). I then imported all my price history, to included the newly defined symbol, and everything worked correctly. Would it work correctly if I had only defined one decimal? I didn't test it but may later.
My first attempt to correct this, after removing the newly assigned symbol, was to change the first price in the quotes.csv file to a number with two decimals (1.23) and import the data. I was wondering if the "Close" column would default to two decimal points. It did not and everything in the column displayed correctly, showing multiple decimal points.
I looked at the column properties for the "Close" column and noted the "Data Type:" set to Decimal Number but the "Format" was set to "General" I changed the "Format" to "Decimal Number" and extended the decimal format to six positions (1.000000). I then imported all my price history, to included the newly defined symbol, and everything worked correctly. Would it work correctly if I had only defined one decimal? I didn't test it but may later.
Comments
Excel and PowerPivot assign data type from first 8 (I believe) records. That is why I either create a first "dummy" record with data (Dividends.csv, CurrConv.csv) or have a "special" record in some tables (Transactions, Symbol) with values that enforce data type.
Even then there is a possibility for issues, as for example you might re-order transactions table and this "special" record would not be in the first 8 rows.
I believe (not tested, but from my general experience), one of the solutions would be to tell Excel use ALL rows when determining data type. This can be done by starting regedit.exe and editing TypeGuessRows value from 8 (default) to 0 (means all) in following node:
HKEY_LOCAL_MACHINE > SOFTWARE > Wow6432Node > Microsoft > Jet > 4.0 >Engines > Excel > TypeGuessRowsVery few PS users would be comfortable editing registry, so I'll look into other solutions. So far you are the first user reporting an issue with a file "Quotes.csv" loading and I see that I will need to address this. For now, I would suggest that in your manual file you put first quotes value as 1.00000001. This should have minimal impact on your results, but should make Excel choose the right data type.
Before testing I changed the Quotes table "Close" column format setting from "decimal number" back to its original setting of "general"
Condition:
User created external data file (example: _AAAAA_.txt) uses a symbol (example: AAAAA) that causes it to be first in the sorted Quotes.csv file.
Quote prices are only whole numbers (no decimals)
Test:
When data file _AAAAA_.txt is imported into Quotes.csv file all the prices appear to be whole numbers, and in fact they are.
After Quotes.csv file is imported into PowerPivot Quotes table, all the numbers in the Close column are rounded to whole numbers. Not good.
Same test but change the price in _AAAAA_.txt so all numbers are decimal. Example: 1.0 or even more zeros. The key here is the number HAS to contain at least one decimal.
When _AAAAA_.txt is imported into Quotes.csv the prices still "appear" to be whole numbers as there are no decimals shown, but they're actually decimal numbers.
This time, when Quotes.csv is imported into the Quotes table, it has no impact on the formatting of the column. All the numbers in the column are displayed with how ever many decimal places they have.
If in your manual file you put first quote value as 1.000001 - does that fix your issue?
I do not recommend most users editing the registry. Your comment above also states as such.