Added multiple currency support to dividend portfolio spreadsheet
While not the most exciting of titles, I did just release version 5.1 of the dividend portfolio spreadsheet. Since the loss of the Yahoo Finance API, I had to switch to a combination of IEX and Alpha Vantage to look up stocks.
While I’m still trying to figure out all the exchanges that Alpha Vantage supports, it does appear to have some international support. (I’m going to be compiling an FAQ with a list of the known ones.)
Quite a few people have asked when I’ll be updating the international version of the spreadsheet to support the Alpha Vantage API. I could still do that if desired, but thought it would be easier to just support a single version of the spreadsheet. So, I’ve merged the currency conversion features of the international version into the dividend portfolio spreadsheet.
In cell E4 you’ll choose the currency you want the Totals converted to. In the example above, I picked US Dollars. The calculated market value of all the stocks will be converted into whatever currency you choose. If you don’t desire for any currency conversions, then just pick US Dollars in column E. The spreadsheet should otherwise work as it has always.
1) The Dividend Calendar and Summary page’s dividend tables default to US Dollars. I’m working on a way to remedy this, but it is not yet ready.
Nice addition Scott. You are very tech savvy. I wish I had a little more of that in me. Tom
Tom from Dividends Diversify recently posted…Blogroll Please
Have you tried google finance api?
I used to use importXML and importHTML to access Google Finance data from the website. Is that what you mean or is there a documented API now? The other method tended to struggle with higher numbers of stocks and did not return consistent data when stocks from different exchanges were referenced.
Do you have an example of how use access the Google Finance data?
Thanks for the spreadsheet, it is great to have I have the heavy lifting done! I have customized it to my liking, and I did find it is possible to use Google Finance for prices, i.e. =googlefinance(L5,”price”). If you enter “=go” in a cell, a popup will appear, listing Google Translate and Google Finance. Clicking on Google Finance will bring up a second popup listing all the finance parameters available.
The only wrinkle is that the symbol has to have a dot instead of a dash, i.e. INO.UN rather than INO-UN that AlphaVantage uses. There are occasions as well when the exchange is needed, i.e. TSE:CJ is needed to return Cardinal Energy from the TSE rather than CJ from the NYSE.
I modified the Reference Data sheet to pull from GoogleFinance in the manual entry column as a backup.
Good points. That does work well for looking up most prices. However, Google Finance integration into Google Sheets does not work with looking up dividends and other finance data.
Yes, they do have a parameter for “incomedividend” and “incomedividenddate”, but the documentation indicates they are only for mutual funds. For the time being, they are leaving dividend investors to go looking for other options. At least others like 52 wk high and low work.
For now I just enter the dividend manually. Even if I have to manually update them once a year, I can live with that. 🙂
I haven’t been able to get AV to work for the Toronto Venture Exchange, but Google Finance does using the exchange prefix symbol CVE, at least for price oriented metrics.
Perhaps someday Google will support dividend data.
Part of me just wants to switch back to Google Finance and have people be forced to manually type in the dividend information. Do you think people would be opposed to that? It would certainly simplify things for me.
Scott, for the financial spreadsheets that I distribute within an investment group, the preference is for automatic retrieval of dividend yield. Members are ok with manually entering a few that IEX doesn’t provide, but getting the yield updated on auto-pilot is very useful.
Dumb question. I’ve been using your spreadsheet for about a month now. I did the whole download a copy thing. Is there a way to merge the updates into my current spreadsheet? The spreadsheet is great by the way!
Thanks! You’ll just have to paste in the data you entered in the orange cells on the Portfolio and the Transactions sheets. Everything should then start working. Let me know if you run into trouble.
Thanks for the for the spreadsheet and have been using for the past few months. I updated to the recent version and am trying to see the breakdown of accounts on the Summary page. Since the “Account” is missing on the Portfolio page, where do I select the Account (Brokerage/IRA) ?
Offers free cryptocurrency tools to use including live crypto prices, market cap, volume and calculator.
A platform to buy/sell Bitcoins in india. Much similar to unocoin- https://www.coinslab.com
#Bitcoin #India #coinslab #Bitcoin buy and sell #btc to inr
if you want to know more about Bitcoin just visit Bitcoin Daily Post. Latest news covering everything about bitcoin and other cryptocurrencies.
I’ve copied and set up yourspreadsheets with my stocks…Unfortunately none of my french (euronext paris) stocks are recognized.
I entered my API key and my london stocks are ok but not the french one.
Can you tell me what ticker symbol should i use for Sanofi (SAN:FP) for exemple ?
Ok…I finally realised that only Sanofi (SAN.PA) doesn’t work…. everything else work for Alpha vantage.
Although the dividend part does not work since it relies on IEX….
HI just downloaded this looks really good. I notice you don’t retrieve the ticker’s company name from GoogleFinance (ie googlefinance(“AAPL”,”name”). Any reason you prefer to type the name? Its undocumented, but widely known. I haven’t had any issues with it.
You can definitely look up the name that way. However, I left that feature out because it might pull up a more “complicated” version of the name that I wanted to simplify instead. Also, since the spreadsheet uses some different APIs than Google Finance to look up stock data, the name lookup might fail if the ticker symbol is different than what Google Finance wants or if it is unavailable on Google Finance.
Thanks for posting. You definitely made my day. I used to surf over the internet and found an article post which interesting and full of knowledge. Visit me at https://www.warriorforum.com/members/marywinchester.html.
Is there an option for MEXICAN PESO (MXN)?
Thanks for your great support men!
I found your blog to be quite informative and comprehensive ,And also recently I came across this token exchange, Crypto and Block chain solutions Website called Monetos (https://home.monetasglobal.com/) and i want your perspective on it.
I think it would be fun to read.
bhagya recently posted…A brief description of DeFi
It is good to have offers about free cryptocurrency tools. keep sharing technical posts. Such content is rarely found on web easily.
Cryptocurrency conversion is not and easy job. still in some countries it is ban.