Script and Excel update issue

0neinthisworld

Hi, everything was working well until just several work days ago. I noticed that the script hung at the Google Historical line item and wouldn't continue on. I thought nothing of it and then tried again today but same thing.
To problem solve, I jumped onto this forum and read there was a new script available. So I renamed the following files by adding "_old" at the end of their names: Scripts folder, and both the UpdatePSData.bat and UpdatePSDataIntraday.bat files. Then I extracted the scripts to my PS folder. I reran the scripts and it has shown no errors. Next I refreshed the Excel file and it updated as well, but it is missing all the Capital Gains values specifically in the Daily tab. All zeros from March 15th onward.
What is interesting is that the Daily Start Value's for each day shows different numbers.

Is there any suggestions you can provide to help me fix this?

Thank you!

VidasMatelis

Hi,

You have to confirm that you have quotes up to date. Here is info on how quotes are stored localy: http://www.portfolioslicer.com/scripts/quotes-data.html. So choose one symbol to investigate and find what is the latest quote date for that symbol. If you used to have Google as your quote source, then you will have to switch to another source. At the bottom of this page http://www.portfolioslicer.com/scripts/quotes-datasource.html there is info on what are your options for switching quote source.

0neinthisworld

I have followed your suggestions and seem to have ran into another issue, but maybe not related to the original problem although I'm not completely sure. I noticed that my TDB900 (originally GoogleWeb in my psconfig.txt) is now TDCANADIANIN.TO. However looking at the Yahoo finance site, their historical data stopped in Dec '17. Thus the values in my Excel is reporting wrong for the various TD mutual funds. Would you know how to fix this or am I out of luck?

Thanks Vidas

hy42

Hi,

I am having the same issues as Oneinthisworld. I switched my data source away from Google and tried both Yahoo and AlphaVantage. Neither of them seem to have updated mutual fund data for 2018 and they don't have quotes from a select few of my funds (eg. mutf_ca:mdm600 previously available from Google).

Thanks!

VidasMatelis

Unfortunately, if quotes for mutual funds are not available from scripted sources, there is not much I can do. Your option now is to add quotes to files manually, for Portfolio Slicer to work properly you will need to have at least one quote per month.

0neinthisworld

I understand. If I am able to find all the missing prices from TD, what files do I need to update to ensure the data refresh is accurate?

VidasMatelis

You just need to manually maintain quote files In Quotes subfolder. More info about that is posted here: http://portfolioslicer.com/scripts/quotes-data.html

buddyb

Hi all,
I thought I might add a post to the discussion of how I make and handle a "manual quote file" within PortfolioSlicer. What you need to do is create and maintain a "comma separated text" file using the exact filename format, and stock quote information format as any of the data files that are stored within the c:\PortfolioSlicer\Quotes folder. For Bookkeeping purposes, I keep the "manual quote files" in a separate folder called "Manual". The path to this manual Quote folder would be c:\PortfolioSlicer\Quotes\Manual.

(The text below is not intended to overwrite the information contained in Vidas's description of quotes are collected and stored within PS. ( http://portfolioslicer.com/scripts/quotes-data.html )

Here is what I do to create and track a symbol that is not part of the current "automatic download" data sequence:

1. I make a "working folder" totally separate from my active c:\PortfolioSlicer folder.
2. Inside this "working folder", I create a MS Excel *.CSV File with the filename that follows the required PS quote naming format. (For example, using Yahoo format for Stock Symbol TDCanadatrust, The symbol TD.TO would have the Excel filename _TD.TO_.CSV.)
3. Enter your Stock Quote data in the required format: Date, Quote Price, Symbol Name. Add how many quote entries you wish, but as a minimum, you need at least one quote value per month. (Note: for past or obsolete mutual fund "manual files", I entered the quarterly paper statement data, and if there were months that were missing data, I simply repeated the previous month's Quote data. It's not perfect, but works well for general record keeping).
4. You can convince Excel that you want to use the YYY-MM-DD date format by selecting the date column and performing a "custom date format" format. Once set up, the EXCEL.CSV file should maintain this format the next time you open and update this file.
5. Close your EXCEL.CSV file and open Windows Explorer, and copy and paste a duplicate copy of your quote file still inside your "working folder".
6. Using Windows Explorer, rename this new file copy from having the *.CSV extension to *.TXT extension. (Using the " _TD.TO_.CSV " example, your copied file would become " _TD.TO_-Copy.CSV ". You will rename the second file to " _TD.TO_.TXT ". )
7. The " _TD.TO_.TXT " file is in the format as specified by PortfolioSlicer requirements. This file is then copied and pasted within C:\PortfolioSlicer\Quote\Manual folder. All the current data scrips will work as normal, and will allow PortfolioSlicer to use this manual Quote datafile.
8. Note: The above sequence is using the Yahoo.com Quote format, so the Stock symbol request still shows up in the " psConfig.txt " as usual.
9. With the manual quote file in place, the last step is to run the PortfolioSlicer quote update sequence as usual. (ie: UpdatePSData.bat within Excel 2010) If you run the update sequence, and you get an error that reports something like "Duplicate entries are encountered" (or something similar), then you need to remove the existing symbol quote file that likely esists in the C:\PortfolioSlicer\Quotes folder. (I suggest not deleting this file, but just move it to your "Working Folder". It may contain historical data you may want to add to your "Manual Quote File" later). If you are modifying an existing PS configuration, you may need to remove or "Comment out" the active Quote request within the psConfigure.txt script file in the C:\PortfolioSlicer\Scripts folder.

Regards
buddyb

PS: I'm feeling this lack of Mutual fund quote datafeed from Yahoo will eventually be fixed. The Mutual Fund Industry is still to big for them not to be advertising their products to the general public. One way or another, I'm sure we'll find a solution to this, and get back to business. Special thanks (As always) to Vidas and MaxT for all their work and time they are putting into this incredible project).

0neinthisworld

I have four TD mutual funds that I needed to update as mentioned earlier, so my attempt to try updating two of the four gave mixed results. I think I added the necessary line items following the date, price and symbol format and updating both the symbol and archive txt files. Then I reran the script, and see it update the quotes.csv. The results I got when I refreshed the data on the PS Excel file shows a bunch of zeros with the last trading date with a dollar amount. This is on the Daily sheet.

Any ideas what I could be missing?



Edit:
I just noticed on the Monthly sheet that each month has a value not zero.

buddyb

0neinthisworld,
PS targets the Quotes.csv file for one of it's input files. If you can carefully open this file up, search and find at least 1 entry per month (Date, Quote and Symbol Name) that you entered in your original "manual quote file", Then you are almost finished. Check and ensure the symbol name format inside the Quote.csv file matches the symbol name format you are using within the PS Excel File.

(odd ... The Yahoo Quote graph for TDCANADIANIN.TO is up to date, but the download file data stops at the end of 2017 - as you originally stated. Maybe there is hope yet for Mutual Funds over in Yahoo - with a little time)

VidasMatelis

Hi, For the daily sheet to see daily capital gain changes, for last 20 business days you need to have quote. If you will have just one quote per month - and that is OK too, but then daily report will not work.

0neinthisworld

I entered in all the daily prices in the file before my previous message so not sure what is happening.

By the way, I found price history on the globe and mail site for my TD funds. Is it possible to include in the script to pull date from globe and mail?

VidasMatelis

Please check psConfig.txt file configuration for PSDataFolder parameter. It should be like this:

# PSDataFolder: Folder were to create files for Portfolio Slicer external files. Default "..\PSData\"

C:\PortfolioSlicer\PSData

0neinthisworld

I have 24 other symbols which are updating fine, just the TD funds. All the quotes are in the same location, I believe my config file is OK and it hasn't been changed for the last year. Do you think what else I should look at?

buddyb

I wonder if we could could ask MaximT to consider adding to his script collection, an additional option to import Quote information from a specific *.csv file (which is downloaded by the user from other sources), and is located on their local PC's. Of course, the format of this additional resident *.csv file would have to be carefully defined.

VidasMatelis

0neinthisworld - can you send me your quotes.csv file and tell me one symbol that has this behaviour and I'll investigate. My email is posted on this page: http://www.portfolioslicer.com/contact.html