Where do you get your data feeds from?

Wolverine · August 22, 2020, 1:48pm

For swiss data, I’m extracting them directly from SIX: https://www.six-group.com/exchanges/index_en.html

I like to cut the middleman and I don’t mind dealing with the 15 min delay (I’m mostly using closing prices anyway).

Neville · August 22, 2020, 5:57pm

Yahoo Finance or Finnhub

rolandinho · August 22, 2020, 10:03pm

Really don’t know, I think unlikely. I have Viac, but include that in my “annual check” only, i.e. I get those numbers “manually” from the Viac report.

As a workaround, for mutual funds following indices one could use the =GOOGLEFINANCE(“indexswx:smi”) for SMI and =GOOGLEFINANCE(“INDEXSP:.INX”) for S&P500 etc.

wapiti · September 6, 2020, 8:56am

How do you get VIAC funds (CSIF ?) ?

mabi · September 6, 2020, 10:56am

Any one knows by chance if something similar is possible with LibreOffice for those who are less fan of Google?

Wolverine · September 6, 2020, 3:58pm

Morningstar has at least some of them:

Searching with the ISIN seems to yield results. I’m quite a bit disappointed to have to go that length to get it, both VIAC and CS should make it easily available, if you ask me (they don’t as far as I know).

Cortana · September 6, 2020, 4:01pm

It wasn’t easy to be honest. Some funds didn’t work at the beginning in Office365. I had to try different names (ISIN didn’t work) till I got the right one.

Cortana · September 6, 2020, 4:45pm

Well I found all VIAC funds, VT, VTI, VIOV, VEA and VWO. So everything what I need actually.

wapiti · September 6, 2020, 6:20pm

Most of the funds are available on Bloomberg, but I was speaking about Office365 or Google sheet

Could you share the Viac funds ISIN which are working?

Cortana · September 7, 2020, 5:02pm

CSIF (CH) III Equity World ex CH Pension Fund ZB
CSIF (CH) III Equity World ex CH Pension Fund ZBH
CSIF (CH) III Equity US Blue - Pension Fund ZB
iS NASDAQ 100 UCITS ETF (XSWX:CSNDX)
CSIF (CH) Equity Switzerland Large Cap Blue ZB
CSIF (CH) Equity Switzerland Small & Mid Cap ZB
CSIF (CH) I Equity Europe ex CH ZB
CSIF (CH) Equity Japan ZB
CSIF (CH) Equity Pacific ex Japan Blue ZB
CSIF (CH) Equity Canada ZB
CSIF (CH) Equity Emerging Markets Blue DB

I entered those as name then went to Data -> Stocks.

Cortana · September 7, 2020, 5:30pm

I looked them up on Credit Suisse and tried different names till I got the right one. Like you said, if only one letter is wrong or something is missing, you won’t get any results at all. Office365 really sucks regarding this! It was a pain in the ass to get all of them.

Looks like this now.

baldur · September 10, 2020, 1:01pm

Speaking about Google Spreadsheets - you can quite easily scrap the information from the webpages. For example:

=IMPORTHTML(“https://finviz.com/quote.ashx?t=TSLA,"table”,11)

Or when you get the source providing CSV file:

=IMPORTDATA(“https://stooq.com/q/d/l/?i=d&s=KGH&d1=20200901&d2=20200910”)

Having this imported, you can do whatever calculations/checks you want. It is also easy to take the parameters from other cells.

But I have another question (not sure if I should open different topic for it) - can you recommend any webpage/data feed allowing to quickly do something like for example:

Get the list of all tickers from specific market (hopefully Polish stock market too) for which the volume during last N sessions was bigger than X? I probably can use above formulas to get all the data for all tickers I’m interested in and do the math afterwards, but I hope that maybe something like this already exists?

Polo · September 10, 2020, 5:31pm

For dealing with single stock price evolution I use https://themarket.nzz.ch/
I’ve once tried the demo account for free and it still works months after. To refresh the page automatically, I use a Chrome autorefresh plugin.
Was tired of not being able to sell on time with the 15minutes delay on Degiro

Cortana · September 17, 2020, 4:46pm

I just realized that Office365 has a 72-96 hours delay! What the hell? How didn’t I notice this before? I thought it was 15min, lol. It’s basically useless!

Trying to remake to whole thing in Google sheets now. Hopefully I get it working.

rolandinho · September 17, 2020, 5:41pm

Google FTW!

Disclaimer: I own 2 GOOGL (Alphabet Inc Class A) shares!

Cortana · September 17, 2020, 5:45pm

It works like a charm!

=IMPORTHTML(“https://www.morningstar.ch/ch/funds/snapshot/snapshot.aspx?id=F000000OPB","table”,4)

Using this formula for example to get all the VIAC/VP funds closing prices. They are all listed on the Swiss Morningstar webpage, so it’s easy to import the data.

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: https://docs.google.com/spreadsheets/d/1nNX2GTAJdeOxcIDBUKEb8FAZOIy6Mn-0mUyY6sc8JPI/

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