Switching Quotes source from Yahoo to Google

VidasMatelis

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:

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

Change to:

AAPL
VOO

Non USA traded symbols

1. 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.
2. Edit psConfig.txt file and delete line for symbol in <Yahoo> area and add line with new symbol into <GoogleWeb> area.
3. 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".
4. Run UpdatePSData.bat file. Check if new quotes files was created and confirm that quotes are there as expected
5. Open PS Excel workbook and in srcSymbol worksheet replace old symbol "Symbol" column value with new value - example replace XEI.TO with TSE:XEI.
6. 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.
7. In the "SymbolAlias" table check if you have any alias for old symbol and if you do - change alias (Symbol column) to new symbol.
8. 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.
9. Refresh data

Same steps will have to be also done for index symbols:
^GSPC symbol will become INDEXSP:.INX
^GSPTSE symbol will become INDEXTSI:OSPTX

buddyb

KODOS to Maxim and Vidas (not necessary listed in that order) for all their ongoing work to realign PortfolioSlicer to a reliable Stock Symbol data source.
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).

GBiasini

Hi,
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?

xcerik

Thanks for breaking it down. My refresh is giving an error now, and some symbols are not appearing in holdings. Does this error indicate where I might have made a mistake?

MdxScript(Model) (17, 133) Calculation error in measure 'Report'[Equity Value]:A table of multiple values was supplied where a single value was expected.

VidasMatelis

GBiasini,

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.

VidasMatelis

xcerik,

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

GBiasini

Vidas,

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.

xcerik

Hi Vidas,

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

deepakhemadri

Vidas, After changing the symbols to Google, I get the quotes just fine. However, all my dividend data has disappeared. Google did not download any dividend information. Any thoughts?

Thanks

VidasMatelis

Deepak,

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.

Smith

Unfortunately it looks like Google doesn't support mutual fund symbols the way it does stock symbols. When looking up a mutual fund on Google it lists the symbol as MTFU:Symbol.

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&amp;startdate=Dec+31,+1999&amp;output=csv
MUTF:Symbol - Not Found (web err)

Smith

Found a fix for the mutual funds: Use the GoogleWeb section instead of Google

<GoogleWeb>
MUTF:Symbol
</GoogleWeb>

Smith

After switching everything over to Google I have one issue - aliases doesn't appear to be working.

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.

VidasMatelis

Smith,

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

Gumby

Hi

Have followed the steps. It all works well ..... except I can't get intra day data. It's all from the day before .......

VidasMatelis

Gumby,

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

echo off
powershell -ExecutionPolicy Bypass .\Scripts\GetQuotesYahooIntraday.ps1
powershell -ExecutionPolicy Bypass .\Scripts\GetExchRatesYahooIntraday.ps1
powershell -ExecutionPolicy Bypass .\Scripts\GetQuotesGoogleIntraday.ps1
powershell -ExecutionPolicy Bypass .\Scripts\CreateDatesFile.ps1
powershell -ExecutionPolicy Bypass .\Scripts\MakePSDataFiles.ps1
powershell -ExecutionPolicy Bypass .\Scripts\AppendGeneratedQuotes.ps1
powershell -ExecutionPolicy Bypass .\Scripts\CheckPSFiles.ps1
choice /C Y /T 10 /D Y /M "Waiting 10sec before closing"