Howdy, Stranger!
It looks like you're new here. If you want to get involved, click one of these buttons!
Quick Links
calcs not working
Hi Vidas,
Please take a look at these two pics. It appears that some calcs are not working correctly -Top 10 winners and losers plus the Profit % on the holdings sheet? Any idea why they may not be updating correctly?
Thanks,
Joe
Please take a look at these two pics. It appears that some calcs are not working correctly -Top 10 winners and losers plus the Profit % on the holdings sheet? Any idea why they may not be updating correctly?
Thanks,
Joe
Comments
So identify this issue - do following tests in Holdings worksheet:
1. Switch to "Original" currency - if that fixes issue, then you should review currency exchange data.
2. Does "Total Value" and "Book Value" for symbols make sense? If Total Value is not right, then you have issue with Quotes file - you do not have latest quotes.
3. If above is not an issue, take 1 symbol and create separate pivot table filtering that symbol and see monthly data: Cost Basis, Total Value and Profit %. You should see one of few months where data seems not right - then for that symbol for that month investigate - symbol transactions and available quotes.
If you have problems doing above, you can send me your workbook and I will look into this. My email is posted on this page: http://www.portfolioslicer.com/contact.html
Thank you for sending me your workbook.
In your workbook you have data up to 2017-05-10 and after that there are no quotes until last day.
So it appears you missed the fact that Yahoo Financial stopped providing quotes and you have to switch to Google Financial. More info in this thread: http://support.portfolioslicer.com/discussion/185/switching-quotes-source-from-yahoo-to-google#latest
So you should:
1. Copy existing psConfig.txt to safe location.
2. Download updated scripts
3. Move data from old psConfig.txt to new psConfig.txt, but at the same time move symbols from Yahoo section to Google or GoogleWeb section. You could also use SymbolRename section to rename symbols from in Quotes.csv file to match symbols in Symbol table in Excel.
4. Review Quotes subfolder and delete any duplicate symbol files.
5. Delete all data from QuotesIntraDay folder.
I am close but now it appears as though my Mutual Funds do not want to update? I checked a few on Google and they are listed. Any idea what the issue may be? All regular stocks are updating now as expected.
First, could you please check if you properly specified mutual fund symbol, that is MUTF_CA:TDB900 instead of TDB900. If that seems to be OK, then could you please post here example of such mutual fund and I'll try to reproduce the problem.
Where does that get specified? This is how my psconfig looks:
# MinDate: Minimum date for Portfolio Slicer.
2003-12-31
# DetailOutput: Values Yes or No. If value is Yes, then script outputs detail execution information, otherwise just summary
Yes
# PSDataFolder: Folder were to create files for Portfolio Slicer external files. Default "..\PSData\"
c:\PortfolioSlicer\PSData\
# GeneratedQuotes: to create static price quotes. Format: Symbol,MinDate,MaxDate,Price
#TDB166C.TO,2012-10-01,,10.00
# DataRootFolder: Root folder for data. Default: "..\"
# ArchiveQuotes: values Yes or No. If value is Yes, Archive script will run
Yes
# End of psConfig.txt file
# ###############################
# ###############################
# Following config parameters were added by Maxim T
# ColumnSeparator values could be : Comma, Tab, VerticalBar
Tab
# DecimalSeparator: Default value is . (dot), in some coutries this could be , (comma)
.
# Currency: list of currencies that you need exchange rates between (from Bank of Canada website). Example: USD,CAD,EUR
USD
# Google: list of symbols from Google Finance website (link to download csv file available). Format: Symbol,MinDate,MaxDate,IntraDayFlag[Y|N],[DividendFlag[Y|N],FactorHistory,FactorIntraDay,FactorDividend
^GSPTSE
^GSPC
AMP
AXP
BA
EEM
EXC
GE
JNJ
MCD
MMM
NFLX
PEG
PG
SCHD
SICNX
SPY
SWHFX
SWERX
T
VBINX
VBMFX
VDIGX
VFINX
VOO
VTI
XOM
# GoogleWeb: list of symbols from Google Finance website (link to download csv file not available). Format: Symbol,MinDate,MaxDate,IntraDayFlag[Y|N],[DividendFlag[Y|N],FactorHistory,FactorIntraDay,FactorDividend
INDEXSP:.INX
# IncludeQuoteArchiveFolder: Values Yes or No. If value is Yes, then when creating Quotes.csv file will include files with name patter _Archive.txt, otherwise these files will be excluded
No
# Symbol rename section. Format: OldSymbol,NewSymbol. OldSymbol - what is generated in Quotes/Dividends by scripts, NewSymbol - what it is used in Excel workbook.
# All symbols in file Quotes.csv and Dividends.csv will be renamed to what is specified in NewSymbol.
#TSE:XIU,XIU
INDEXTSI:OSPTX,^GSPTSE
INDEXSP:.INX,^GSPC
Please check video at the bottom here - around 4min 30sec mark. This explains if you can place symbol under google or google web. So you will see that most of the mutual funds should have symbol like:
MUTF:SWHFX
and they should go to googleweb section.
Then, assuming in Excel you defined symbol SWHFX, in the symbol rename section you can rename:
MUTF:SWHFX,SWHFX
So now your Excel definition of symbol SWHFX will match quotes in Quotes.csv file.
1. You have duplicate quotes in quotes.csv file. Best way to resolve this - backup all quotes from existing Quotes subolder, then delete all files from there and re-run quote extract. Before run make sure to delete all files from QuotesIntraDay folder too. Please also check PSData folder (c:\PortfolioSLicer\PSData) - if there is errors.txt file, it will list duplicate quotes you have.
2. OR - You have quotes for symbols that are not defined in Excel Symbol table. If so, please remove from psConfig.txt and from Quotes subfolder symbol files that are not defined in Excel Symbol table and re-run UpdatePSData.bat file.
# MinDate: Minimum date for Portfolio Slicer.
2003-12-31
# DetailOutput: Values Yes or No. If value is Yes, then script outputs detail execution information, otherwise just summary
Yes
# PSDataFolder: Folder were to create files for Portfolio Slicer external files. Default "..\PSData\"
c:\PortfolioSlicer\PSData\
# GeneratedQuotes: to create static price quotes. Format: Symbol,MinDate,MaxDate,Price
#TDB166C.TO,2012-10-01,,10.00
# DataRootFolder: Root folder for data. Default: "..\"
# ArchiveQuotes: values Yes or No. If value is Yes, Archive script will run
Yes
# End of psConfig.txt file
# ###############################
# ###############################
# Following config parameters were added by Maxim T
# ColumnSeparator values could be : Comma, Tab, VerticalBar
Tab
# DecimalSeparator: Default value is . (dot), in some coutries this could be , (comma)
.
# Currency: list of currencies that you need exchange rates between (from Bank of Canada website). Example: USD,CAD,EUR
USD
# Google: list of symbols from Google Finance website (link to download csv file available). Format: Symbol,MinDate,MaxDate,IntraDayFlag[Y|N],[DividendFlag[Y|N],FactorHistory,FactorIntraDay,FactorDividend
^GSPTSE
^GSPC
AMP
AXP
BA
EEM
EXC
GE
JNJ
MCD
MMM
NFLX
PEG
PG
SCHD
SPY
T
VOO
VTI
XOM
# GoogleWeb: list of symbols from Google Finance website (link to download csv file not available). Format: Symbol,MinDate,MaxDate,IntraDayFlag[Y|N],[DividendFlag[Y|N],FactorHistory,FactorIntraDay,FactorDividend
INDEXSP:.INX
MUTF:SICNX
MUTF:SWHFX
MUTF:SWERX
MUTF:VBINX
MUTF:VBMFX
MUTF:VDIGX
MUTF:VFINX
# IncludeQuoteArchiveFolder: Values Yes or No. If value is Yes, then when creating Quotes.csv file will include files with name patter _Archive.txt, otherwise these files will be excluded
No
# Symbol rename section. Format: OldSymbol,NewSymbol. OldSymbol - what is generated in Quotes/Dividends by scripts, NewSymbol - what it is used in Excel workbook.
# All symbols in file Quotes.csv and Dividends.csv will be renamed to what is specified in NewSymbol.
#TSE:XIU,XIU
INDEXTSI:OSPTX,^GSPTSE
INDEXSP:.INX,^GSPC
MUTF:SICNX, SICNX
MUTF:SWHFX, SWHFX
MUTF:SWERX, SWERX
MUTF:VBINX, VBINX
MUTF:VBMFX, VBMFX
MUTF:VDIGX, VDIGX
MUTF:VFINX, VFINX
powershell -ExecutionPolicy Bypass .\Scripts\GetQuotesGoogleIntraday.ps1