I've broken something.....?

ChrisBoar

Hi Vidas,

Not sure what mistake I've made here....just made some major changes to my portfolio and attempted to enter those in the spreadsheet.

So I've update the PSconfig file with the new stocks under the yahoo section....but this UpdatePSdata script doesn't auto-close now when run....although it appear to update all the quotes.....??

The Powerpivot update appears to work....apart from one stock that looks like it's missing under the quotes table, even though it is present in the quotes folder.

And when I attempt to refresh my dashboard it comes back with the following error....

'Data could not be retrieved from external data source.......A table of multiple values was supplied where a single value was expected'...


Any ideas?

ChrisBoar

And an update to this...the update quotes script is now failing....reporting that attempts to update all my stock quotes failed....and still not auto-closing the window. Is this a Yahoo issue??

VidasMatelis

Did you downloaded latest script files from download location? There are many changes to external data sources.

ChrisBoar

Yep, I updated my spreadsheets about a month ago and downloaded the latest scripts. Is there a way I can double check I'm using the correct scripts?

It's just the latest stock changes that everything has stopped working.

ChrisBoar

Ahhh, Ijust checked, you've updated the scripts at the start of this month, I don't have those. I'll update and see what happens.

ChrisBoar

Ok so I've updated to the latest scripts and it's still giving the same error when I attempt to refresh my dashboard.

The updatePS is pulling quotes via intraday. But I notice that when I refresh-all on the PowerPivot table, that the new stock symbols haven't appeared on the Symbol table....?

VidasMatelis

1. Pick symbol to investigate.
2. Check PSData\quotes.csv file and make sure there are quotes for that symbol.
3. Refresh Excel pivot table. Then refresh Pivot table again.
4. Do you have that symbol info in Holdings? Is that symbol in any Symbol slicer - for example check "DivGen" sheet. Any errors during refresh? What Excel version are you using?

ChrisBoar

Hi Vidas,

I've got nothing in the PSData folder....completely empty? All my quotes are in the Quotes folder.....

VidasMatelis

You create files in PSData folder after EACH RUN of .bat files.
So please check psConfig.txt file for section - this will tell you where script puts all final files that were created. By default this should be in c:\PortfolioSlicer\PSData. If you changed location, make sure you also change location in Excel.

ChrisBoar

Hi Vidas,

Ok I was looking in the wrong place.....the quotes are there, but all in one column....e.g.

2017-12-1343.400002CNQ.TO

I get no errors on a powerpivot refresh. But none of the new symbols appear in the powepivot tables, i.e. not on the symbol table,

VidasMatelis

Chris,

Please check psConfig.txt file. Do you have section that specifies delimiters:

Tab


# DecimalSeparator: Default value is . (dot), in some coutries this could be , (comma)

.

ChrisBoar

Yes I have those.

This is the quote section in my psconfig file.



Should I be using dots instead of commas?

----------------------

# Yahoo: list of symbols from Yahoo Finance website (link to download csv file available). Format: Symbol,MinDate,MaxDate,IntraDayFlag[Y|N],[DividendFlag[Y|N],FactorHistory,FactorIntraDay,FactorDividend

NA.TO,,,Y,N,,,
BNS.TO,,,Y,N,,,
RY.TO,,,Y,N,,,
FTS.TO,,,Y,N,,,
EMA.TO,,,Y,N,,,
AQN.TO,,,Y,N,,,
CPX.TO,,,Y,N,,,
CU.TO,,,Y,N,,,
ENB.TO,,,Y,N,,,
IPL.TO,,,Y,N,,,
CNQ.TO,,,Y,N,,,
BIP-UN.TO,,,Y,N,,,
BCE.TO,,,Y,N,,,
T.TO,,,Y,N,,,
HIVE.V,,,Y,N,,,
HCG.TO,,,Y,N,,,
BMO.TO,,,Y,N,,,
CHB.TO,,,Y,N,,,
DOL.TO,,,Y,N,,,
SU.TO,,,Y,N,,,
TD.TO,,,Y,N,,,
VAB.TO,,,Y,N,,,
VFV.TO,,,Y,N,,,
VRE.TO,,,Y,N,,,
VSB.TO,,,Y,N,,,
VSC.TO,,,Y,N,,,
VXC.TO,,,Y,N,,,
XBB.TO,,,Y,N,,,
XIU.TO,,,Y,N,,,
XRB.TO,,,Y,N,,,
XSU.TO,,,Y,N,,,
ZCN.TO,,,Y,N,,,
ZEB.TO,,,Y,N,,,
ZPR.TO,,,Y,N,,,
ZRE.TO,,,Y,N,,,
CJR-B.TO,,,Y,N,,,
,

VidasMatelis

That all looks good. I need to look at your files. Can you send me all files from PSData folder. It is also very likely I will need your Excel file, but lets start from files in PSData folder. My email is posted on this page: http://www.portfolioslicer.com/contact.html

ChrisBoar

Ok thanks Vidas, I've just emailed them to you.

VidasMatelis

Chris,

In Excel workbook Symbol table you have one record (row 30) in the middle with no data. Select all cells in that table (A30:J30), right mouse click, then "Delete", then "Table Rows". After that you should be able to refresh.

ChrisBoar

Okkk. that's a bit better. It's refreshing now. But all the data is wrong. It's now showing most of my holdings, and the allocation categories haven't been updated to the new ones I created. I guess I've set up these new categories wrong?