Category Archives: stocks

Downloading Stock Prices into Google Spreadsheet

Posted on by
1

This is a sister post for “Downloading Stock Prices into Excel Spreadsheet” http://amourtan.com/2011/06/downloading-stock-prices-into-excel-spreadsheet/

Google Spreadsheet used to be a joke in the spreadsheet arena – it’s slow, unreliable and not that feature rich. However, Google Spreadsheet has now come of age; it has become a bad concept done well! Nowadays, if you want to do heavy numerical crunching, the natural choice would be to use Excel. OTOH, if you’ve structured data which you want to share and collaborate with colleagues or friends, GS is an excellent choice.

GS has means to download stock/ETF/mutual funds data into the spreadsheet. I shall introduce 2 approaches. If your investments are all USA based, you can import it from Google Finance. For example typing =GoogleFinance(“GOOG”; “price”) in a cell will retrieve the price of the ticker ‘goog’ into the cell. Typing =GoogleFinance(“GOOG”; “volume”) will likewise download the volume of Google. You can view the documentation from Google here http://support.google.com/docs/bin/answer.py?hl=en&answer=155178

If you’ve foreign (outside USA) investments such as Singapore stocks, you can download them from Yahoo! Finance. Try typing in the string =ImportData(“http://finance.yahoo.com/d/quotes.csv?s=XOM+BBDb.TO+JNJ+MSFT&f=snd1l1yr”). The cells will import the ticker, stock name, date, last trade price, dividend yield and PE ratio for the symbols XOM, BBDb.TO, JNJ and MSFT. The structure of the data is encoded in the string ‘snd1l1yr’. As for the symbol list, your can check with Yahoo! Finance at http://finance.yahoo.com/ . The documentation of the stock quotation URL is listed here http://www.gummy-stuff.org/Yahoo-data.htm .

On some browsers, you might discover that the cells contain stale data upon subsequent reloads. A quick and dirty fix is to change the URL in the function, e.g. to =ImportData(“http://finance.yahoo.com/d/quotes.csv?s=XOM+BBDb.TO+JNJ+MSFT&f=snd1l1yr&var”) where the string ‘var’ is a random number or text. You can automate the process of changing the random string by referencing the random string in the URL to another cell and change the content of that cell. GS will be smart enough to detect the change and update the quotes accordingly.

If you want to download historical quotes into your spreadsheet, try the following instead, i.e. =ImportData(“http://chart.yahoo.com/table.csv?s=MSFT&a=0&b=1&c=2010&d=0&e=1&f=2013&g=w&q=q&y=0&x=.csv”).

  • s=MSFT specifies the symbol MSFT
  • a=0 specifies the start month (0 for Jan, 1 for Feb, 2 for Mar, etc.)
  • b=1 specifies the start day of the month (1st of the day in this case)
  • c=2010 specifies the start year
  • d=0 specifies the end month (0 for Jan, 1 for Feb, etc.)
  • e=1 specifies the end day of the month (1st of the day in this case)
  • f=2013 specifies the end year
  • g=w specifies the granularity of the data (d for daily, m for monthly, w for weekly)

Enjoy managing your finance!

Why is ETF Management Fee/Expense Ratio Lower?

Posted on by
Reply

The 21st century has seen a phenomenal rise in the popularity of ETFs as an investment tool. From virtually nowhere, it has become a platform that can rival mutual funds/Unit Trust in terms of assets under management (AUM) and diversity of the asset classes it covers. One of the key feature (most will say its benefit) of ETFs is that ETFs, on the whole, usually sport a lower management fee compared to mutual funds given the same asset class and AUM. There are, I think, 4 main reasons why this is so, which I shall explain shortly.

Firstly, ETFs are Index Funds. While there might be some ETFs that are managed funds (are there?), most ETFs are index funds, i.e. the underlying portfolio/prices follow that of published stock indexes. Index funds are in general cheaper to manage as there are fewer trades to deal with. They are, in investment speak, passive in nature. This lowers their brokerage fees and trading slippage. Also, they do not need the service of an army of security analyst to dissect the individual stocks and try to beat the market. Index funds do not beat the market, they’re the market! Given the academia overwhelming evidences that markets are efficient and market timings and security selections are a fool’s errand, index funds in general and ETFs in particular have become the investment tools of choice with more converts flocking to its open arms by the moments.

Secondly, even after accounting for its index fund nature, ETFs are still cheaper in terms of management fees compared to its index mutual funds brethren. One reason could be because in the setup of ETFs, there is division of labour which makes it cheaper to operate compared to a mutual fund. For example, mutual fund companies are required to setup and operate a sales channel. It needs employees and office space to man the phone calls and take in orders. Even if it’s done through the internet via electronic means, mutual funds still need to hire IT staff and expansive computer software and hardware to operate. ETFs do not have such a drawback as it’s traded via brokers. So, you’ll have to consider brokerage fees (if there’s one) as a sale charges on your ETFs you trade. However, brokerages and stock exchanges (the platform on which ETFs are traded) are optimized for such a task and can do it efficiently given the volume they handles. ETF companies can further save money on the accounting and upkeep of customers’ records as it’s offloaded to the exchanges and stock brokers.

Thirdly, ETF management companies can act as authorized participants. Authorized participants are empowered to create or redeem ETF creation units. They serve a useful purpose of bringing the ETF prices in line with the underlying stocks prices that comprise the ETFs. They create ETF shares when ETF prices exceed the underlying stock prices and vice versus. Through this act of arbitrage they generate liquidity and a fairer market where the prices of ETFs are based on the underlying securities. Along, the way, they also generate a tiny arbitrage profit for themselves. Given the depth and breadth of the ETF market, I reckon that this arbitrage profit is fairly size-able if the inflow/outflow of funds to/from the ETFs are huge.

Fourthly, there exists a huge ETF derivative market in the synthetic ETF arena. Synthetic ETFs do not buy the underlying securities with its funds. Instead, synthetic ETFs are swaps that use the fundings as collateral and the ETF sponsors promise you the performance of the indexes. I shall dealt with the actual risk of being synthetic in another post but suffice to say, this arrangement increases the flexibility of the fund management companies and allows them to offer the product at reduced management fees since they can earn profits elsewhere as a swap counter-party and sell financial equity-linked products.

Considering these factors, I expect the management fees of ETFs to continue at its current lower level. It’s even plausible that the expense ratios might fall to zero if the ETFs have a really large AUM and profits from businesses such as arbitrage are huge.

Downloading Stock Prices into Excel Spreadsheet

Posted on by
3

With the advent of fast and affordable computing power on the one hand and the availability of powerful, feature-complete spreadsheet on the other hand, financial calculation and modeling have increasingly been performed on Microsoft Excel, the industry-leading spreadsheet of choice.

Investors and investment professionals alike now massage investment-related information on Excel. A function of Excel is to download stock information. There are chiefly 2 ways to download stock/ETF/mutual fund prices into your spreadsheet. One way is via MSN MoneyCentral database connection. The other is through Yahoo! Finance Excel macro.

You can view the MSN MoneyCentral way through this hyperlink.

However, the MSN MoneyCentral approach might not be suitable for some people as it doesn’t list a number of foreign stocks outside USA. For example, Singapore stocks are not included by MSN MoneyCentral. Fortunately, Yahoo! Finance has most of such data and thus the Yahoo! Finance Excel macro is my preferred tool to download stock quotes. You can download the sample Excel spreadsheet here.

The spreadsheet contains sample macros and stock tickers which you can modify to suit your needs. You’ve to remember to enable macros in Excel in order for it to work. As the data source is from Yahoo! Finance, you’ve to check with Yahoo! Finance for the stock ticker information, it might differ from MSN MoneyCentral. For advance users, the Yahoo! Finance Excel macro is the preferred way as Excel macro in infinitely customisable.

You can browse the author’s documentation page at this link.

If you want to download historical quotes, you can download a sample Excel macro spreadsheet for viewing here.  As always, remember to enable the macros.  This particular macro uses Yahoo! Finance symbols and data.