›

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Register

Quick Links

Categories

calcs not working

jwengajwenga
in Excel Workbook
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

«1

Comments

  • VidasMatelisVidasMatelis
    Hi,

    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
  • VidasMatelisVidasMatelis
    Hi,

    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.

  • jwengajwenga
    Oh wow, yes I did miss that. Ok, I will work to make the changes based on the directions in the link.
  • jwengajwenga
    Vidas,
    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.
  • VidasMatelisVidasMatelis
    Hi,

    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.
  • jwengajwenga
    Vidas,
    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

  • jwengajwenga
    Btw, these are USA Mutual Funds...
  • VidasMatelisVidasMatelis
    Hi,

    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.
  • jwengajwenga
    I feel as though Im getting closer to restoring functionality. How about this excel error?
  • jwengajwenga

  • VidasMatelisVidasMatelis
    edited August 2017
    This error means:
    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.

  • VidasMatelisVidasMatelis
    Please also check PSData folder (c:\PortfolioSLicer\PSData) - if there is errors.txt file, it will list duplicate quotes you have.
  • jwengajwenga
    All good now, I'm back in business! Thanks VIDAS!
  • jwengajwenga
    I may have spoke too soon My intraday do not seem to be updating? Can you look at my config and tell me? Thanks

    # 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


  • VidasMatelisVidasMatelis
    Please check .bat file and make sure they include call to GetQuotesGoogleIntraday.ps1, like:
    powershell -ExecutionPolicy Bypass .\Scripts\GetQuotesGoogleIntraday.ps1
Sign In or Register to comment.