Where do you get your data feeds from?

rolandinho · September 17, 2020, 5:48pm

Oh wow good to know, I haven’t included my Viac holdings in my Google Sheets up till now, because annual (manual) inclusion in the overall picture is enough for me. But if it’s this easy, I may change my mind.

Cortana · September 17, 2020, 5:58pm

I made a separate Tab because it imports the whole table. Index 1 gives you the title and index 4 the table with the values.

I actually had to do more things to get it right, as the imported price is in text format. You can view the whole spreadsheet here: Assets - Google Sheets

lowyield · September 30, 2020, 6:06pm

Since most of my portfolio sits on IB, and they provide almost the same level of detail as their TWS, I’m in the process of implementing their APIs directly in python (easiest way? not by far, but a learning experience ).

When it comes to Sheets and Excel, they were useful at the beginning but have limitations, usually have only delayed quotes, perhaps OHLC, etc. but I found myself in the need to get the C price at a certain date (e.g. every EOM, or historical) or even some fundamental ratios and dividend schedules, and for that I see two solutions:

Build your own web scraper (e.g. in Sheets but also manually via py), you may need more than one if you have multi-currency multi-country multi-instruments portfolios
Pre-built modules (which may be nothing else than shortcuts for web scrapers, or may connect to some real APIs - register and get the key), there are a bunch out there but choice of source feed is quite limited.

I would love to hear from someone if they have already implemented an IBKR integration (beyond the spaghetti-code provided as sample), or if they are programmatically using any other reliable source. Or projects around these lines .

Wish I still had access to my old client env. with full Bloomberg licence, etc.

tarioch · May 22, 2021, 1:28pm

I’ve done it

github.com

tarioch/beancounttools/blob/master/src/tariochbctools/plugins/prices/ibkr.py

from beancount.prices import source
from dateutil import tz
from beancount.core.number import D
from os import environ
from ibflex import client, parser
from datetime import datetime
from time import sleep


class Source(source.Source):
    def get_latest_price(self, ticker):
        token = environ['IBKR_TOKEN']
        queryId = environ['IBKR_QUERY_ID']

        try:
            response = client.download(token, queryId)
        except client.ResponseCodeError as e:
            if e.code == '1018':
                sleep(10)
                response = client.download(token, queryId)

This file has been truncated. show original

Is for prices

github.com

tarioch/beancounttools/blob/master/src/tariochbctools/importers/ibkr/importer.py

import yaml
import re
from os import path
from ibflex import client, parser, Types
from ibflex.enums import CashAction

from beancount.query import query
from beancount.parser import options
from beancount.ingest import importer
from beancount.core import data, amount
from beancount.core.number import D

from tariochbctools.importers.general.priceLookup import PriceLookup


class Importer(importer.ImporterProtocol):
    """An importer for Interactive Broker using the flex query service."""

    def identify(self, file):
        return 'ibkr.yaml' == path.basename(file.name)

This file has been truncated. show original

For dividends

TRSP · May 8, 2024, 9:15am

So I check my stock portfolio using a Google spreadsheet (with the ‘googlefinance’ function). This worked pretty well for quite some time until about a year ago when googlefinance (or rather the Google Finance website from which googlefinance takes its data) started not to show quite a few of the ETFs I own. This got worse and worse, and now 35% of my ETFs are no longer shown (for example, just last week Google Finance stopped showing the iShares Swiss Dividend ETF, CHDVD). That’s quite annoying, as many of them are not that small either… So, my question: Has anyone experienced the same and found a reliable solution with Google Spreadsheet (I’m not interested in another form, as I also work directly with the data there), i.e. one that has worked for a longer period of time? I am thinking of getting the data (I literally only need the price) from other websites with an importxml function or something. Any recommendations/experience?

Burningstone · May 8, 2024, 9:45am

Check Yahoo Finance, should be relatively easy to get their data into Google Sheets. Otherwise Morningstar has many funds as well.

xmj · May 8, 2024, 12:17pm

Alternatively, download the spreadsheet and use the Financials Extension with LibreOffice

e.g. using Yahoo Finance, has historic and realtime data.

TRSP · May 9, 2024, 2:54am

This was my original idea. But somehow with my limited knowledge I can’t manage to get the data from Yahoo and Morningstar. For example, to get the price with the importxml function of this here Yahooist Teil der Yahoo Markenfamilie and this here https://www.morningstar.com/etfs/xswx/chdvd/quote, do you know the Xpaths I have to enter in the google sheet formula? I haven’t managed to find it out (I know how to do it from other websites, but Yahoo doesn’t work…).

TRSP · May 9, 2024, 2:54am

Thanks, I might have a look at it. Although I definitely prefer google spreadsheet (for a few reasons).

Burningstone · May 9, 2024, 6:51am

I’m not using google sheets, but I’m sure that you’ll easily find a solution on Google

Dago · May 9, 2024, 8:25am

I noticed the same. I use the following formula to pull from finanzen.ch

=INDEX(IMPORTXML("https://www.finanzen.ch/etf/ishares-msci-world-etf-ie00b8bvck12","//span[@class='push-data ']"), 1, 1)

Just find the ETF you need on the website and update the URL.

TRSP · May 9, 2024, 10:24am

Thank you. It seems that these websites have changed and all the old descriptions do not work anymore. But I’ll check @Dago’s solution.

TRSP · May 9, 2024, 10:26am

Thank you very much! This actually works, except that some ETFs I have on finanzen.ch are in CHF and not in USD/EUR. But this can be adjusted by multiplying it with the exchange rate.

dom.swiss · March 10, 2025, 5:29pm

Yahoo Finance can be accessed after adding a function to Google Sheet’s AppScript

function yahooF(ticker)
{
  const url = `https://query1.finance.yahoo.com/v8/finance/chart/${ticker}`; 
  const res = UrlFetchApp.fetch(url, {muteHttpExceptions: true}); 
  const contentText = res.getContentText(); 
  const data = JSON.parse(contentText); 
  if (data && data.chart && data.chart.result && data.chart.result.length > 0) 
  { const regularMarketPrice = data.chart.result[0].meta.regularMarketPrice; console.log(regularMarketPrice); 
  return regularMarketPrice; 
  } 
  else 
  { console.log("Error: Unable to retrieve market price."); 
  return null; 
  } }

Adding it to Extensions - AppScript will allow you to simply call this function from a cell analogous to Googlefinance:

=yahooF("BTC-USD")

mabi · March 11, 2025, 9:58am

Nice piece of code can you also use this to query the price of VIAC funds? For example the “CSIF (CH) III Equity World ex CH Quality - Pension Fund DB” one ? I tried with the ISIN number of this fund but it does not work.

dom.swiss · March 11, 2025, 11:17am

You need to look up the corresponding Yahoo Finance code: its 0P00019908.SW and the market data will be provided.

It’s not my code, I just borrowed it

mabi · March 11, 2025, 12:48pm

I see, thank you very much. And for those wondering how to get that code, you simply search with the name of the fund on finance.yahoo.com

PhilMongoose · March 17, 2025, 8:47pm

Yahoo finance has been getting worse and worse. Has anyone pulled data from IBKR (I mean free data, not paid data subscription)? Using the IBKR API costs money, but I guess if you pretend to be their mobile app, you can pull data that way.

PhilMongoose · March 24, 2025, 9:20am

After Google Finance killed its portfolio feature, following its trading of killing anything that is marginally useful and stable, I migrated to Yahoo Finance.

For an old dotcom company that might as well be dead, YF has a remarkable number of overhauls. I count at least 4 and each time they make it worse and since the recent updates this has been almost unusuable.

I’m not using only YF to store portfolio data and pull the live prices. The portfolio data I can easily transfer into a database.

Now for the prices, does anyone know of a good source of price data (even 20 minute delayed prices) which can be easily pulled using a python script?

dom.swiss · March 25, 2025, 11:52am

I am bracing myself to use different techniques and different data sources to get the relevant data: Google Finance itself, Coin Market Cap API, Yahoo Finance, currently experimenting with screen scraping for Gold price.

Just wondering: I am still using Google Finance Portfolio for the majority of my stocks and ETFs. Why do you mention that it has been killed?