Howdy, Stranger!
It looks like you're new here. If you want to get involved, click one of these buttons!
Quick Links
Switching Quotes source from Yahoo to Google
As Yahoo financial suddenly stopped providing Quotes and Dividends information, here is the guide on how to switch Portfolio Slicer external data scripts (provided by Maxim) from Yahoo Financial website to Google Financial website quotes:
Example
Was:
^GSPC symbol will become INDEXSP:.INX
^GSPTSE symbol will become INDEXTSI:OSPTX
USA traded symbols
For symbols that are traded in USA markets simply edit psConfig.txt file and move symbol line from <Yahoo> area into area. Run UpdatePSData.bat file and then in Quotes subfolder file symbol file and make sure that latest quotes were added. If you have any issues, you might try moving symbol line from <Google> area to <GoogleWeb> area.Example
Was:
AAPL
VOO
AAPL
VOO
Non USA traded symbols
- Find how the symbol is identified in Google Finance website. Go to http://finance.google.com and enter Ticker or company name and then from the drop down list select company based on the ticker, name and market (Toronto, London, etc). In the new page in the search bar you will see new "symbol". For example with Yahoo I was tracking XEI.TO exchange traded fund. In Google Finance I entered XEI and choose company in TSE market and after selection new symbol that is used by Google was "XEI:TO". Confirm that price matches what is your investment institution is reporting to you.
- Edit psConfig.txt file and delete line for symbol in <Yahoo> area and add line with new symbol into <GoogleWeb> area.
- In Quotes subfolder find files associated with old symbol and move them out into another folder (do not create subfolder in Quotes folder as every subfolder is used when creating final Quotes.csv file). For example, at the same level where Quotes subfolder exists - I created another subfolder "Quotes-Old" for XIU.TO you will have to move out files "_XIU.TO_.txt" and "_XIU.TO__Archive.txt".
- Run UpdatePSData.bat file. Check if new quotes files was created and confirm that quotes are there as expected
- Open PS Excel workbook and in srcSymbol worksheet replace old symbol "Symbol" column value with new value - example replace XEI.TO with TSE:XEI.
- Edit Symbol Sector table and replace old symbol with new symbol - example filter table for symbol "XEI.TO" and then replace values "XEI.TO" with "TSE:XEI". After finishing remove filter for "XEI.TO" so other tables in the same worksheet show unfiltered data.
- In the "SymbolAlias" table check if you have any alias for old symbol and if you do - change alias (Symbol column) to new symbol.
- In the "SymbolAlias" table add alias for old symbol to map into new symbol. Example: XEI.TO, TSE:XEI. This way you will not need to make any changes to Transactions table. Or you could replace old Symbol value with new value in Transactions table.
- Refresh data
^GSPC symbol will become INDEXSP:.INX
^GSPTSE symbol will become INDEXTSI:OSPTX
Comments
What is/was Yahoo.com thinking????
Step-by-step everyone can get through this .....
(Ps: Remember to backup your PS files often during these transition steps).
Thanks for the detailed procedure. I followed the procedures but all my Canadian stocks that I moved show as red cells in both tables in srcSymbol in each case, under the column "Symbol"). The quotes are received but, on refresh, none of the symbols that show as red in srcSymbol show up in the list of owned stocks or in the summary information.
Any thoughts?
MdxScript(Model) (17, 133) Calculation error in measure 'Report'[Equity Value]:A table of multiple values was supplied where a single value was expected.
Steps 5 and after indicate that you need to change data in Symbol table, SymbolSector table and Symbol Alias table.
So what you have to do, for example if you used to have symbol XEI.TO
1. In worksheet srcSymbol for table Symbol I changed Symbol column from XEI.TO to TSE:XEI
2. In worksheet srcSymbol for table SymbolSector I filtered by Symbol for XEI.TO and changed that column value to TSE:XEI. You can also filter by color "Red" to show all symbols without matching record in Symbol table.
3. In worksheet srcSymbol for table SymbolAlias I filtered by Symbol for XEI.TO and changed that column value to TSE:XEI. Again, you can filter by color "Red".
4. In worksheet srcSymbol for table SymbolAlias add record with "SymbolAlias" value XEI.TO and Symbol value TSE:XEI.
That should do it.
Things to check:
1. Do you have any red cells in srcSymbol or srcTrans table - that is the first thing to check.
2. have you deleted old files from Quotes subfolder?
If you cannot figure out, you can send me:
1. Your Excel workbook.
2. Your files in Quotes folder
3. Your files in PSData folder
My email is posted on this page: http://www.portfolioslicer.com/contact.html
Thanks. This is twice now that I only saw the view of the srcSymbol tab starting at Tab J and didn't realize that I had to scroll left to column A. That fixed my problem. Thanks again.
Thanks for the quick reply. I checked for the red cells. I deleted old files from Quotes subfolder. Then, I tried deleting all files in Quotes folder, and re-ran UpdatePSData.bat. Now I can refresh without the error. Then I just had an issue where some symbols, which are in Quotes.csv, but do not appear in the reports. I had to refresh Transactions again, likely due to using symbol aliases, even though I had no new transactions. Refresh seems a bit slower now, but all my holdings are showing up.
Thanks for the great programming and support!
Erik
Thanks
Sorry, but without Yahoo providing data, there is no way to automatically get dividend information. Right now best option I see is to manually edit files in Dividends folder and add dividend payment information.
Example: MUTF:AWSHX
When trying to download EOD prices for mutual funds the following is recorded in the GetQuotesGoogle.txt log file for each fund. Without MUTF: in front of the symbol the results are the same.
Symbol: MUTF:Symbol
Requesting url: http://www.google.com/finance/historical?q=MUTF:Symbol&startdate=Dec+31,+1999&output=csv
MUTF:Symbol - Not Found (web err)
<GoogleWeb>
MUTF:Symbol
</GoogleWeb>
On srcTrans tab SymbolName column the alias is correct but Symbol column shows the symbol name.
Dashboard, Daily, Monthly, Yearly, Holdings, TransInfo, Sales, and DivGen taps display the symbol name instead of the alias.
I have aliases defined for everything on the srcSymbol tab.
SymbolAlias table is used just for one purpose - convert SymbolName in srcTrans table to Symbol and Symbol column is used to join to Symbol table, Quotes table, etc.
Please very carefully review SymbolAlias table - make sure there are no duplicate alias values.
Then please check formula for srcTrans table column Symbol. It should be:
= IF(ISNA(VLOOKUP([@SymbolName], SymbolAlias[#All],2,FALSE)), [@SymbolName], VLOOKUP([@SymbolName], SymbolAlias[#All],2,FALSE) )
Formula says - take second column from symbolAlias table if value is found, otherwise use SymbolName from srcTrans Table. Make sure formula has not changed by accident.
If you still have problem, you can send me your workbook and let me know which symbol for you does not work. My email posted on this page: http://www.portfolioslicer.com/contact.html
Have followed the steps. It all works well ..... except I can't get intra day data. It's all from the day before .......
You can download script that gets intraday quotes from here: http://support.portfolioslicer.com/index.php?p=/discussion/5/scripts-to-get-data-for-portfolio-slicer-from-yahoo-google-and-boc#latest
Copy this to Scripts folder and then update file UpdatePSDataIntraday.bat with