Saturday, June 10, 2006

Getting Stock Prices into Excel

Yahoo Finance (http://finance.yahoo.com/) has a link on each summary page that allows the user to download the stock information in CSV format. What is less well known is that there is a URL which allows direct access to stock information without having to parse any HTML or even XML.

The format is

http://finance.yahoo.com/d/quotes.csv?s=STOCK_SYMBOL&f=FORMAT_STRING

so to get Vodafone's ask price the URL would be

http://finance.yahoo.com/d/quotes.csv?s=VOD.L&f=a

A full description is available at http://www.gummy-stuff.org/Yahoo-data.htm



YF Quote Excel Addin

The next step is to get this data into Excel via a custom function:

=YF_Price(STOCK SYMBOL, PRICE SIDE)
  1. =YF_Price("VOD.L", "BID") returns the bid price for Vodafone
  2. =YF_Price("VOD.L", "ASK") returns the ask price
  3. =YF_Price("VOD.L", "LAST") returns the last price
  4. =YF_Price("VOD.L", "CLOSE") returns the close price
  5. =YF_Price("VOD.L") defaults to the last price

The download link is broken at the moment - e-mail me at frans 'underscore' king 'at' hotmail 'dot' com if you would like me to send you the zip file. It needs MSXML 4.0 or greater which can be downloaded from Microsoft.

[Update 28/06/09 - Download link is now http://www.sbbsystems.co.uk/downloads/YFQuote/YFQuote_Addin_0.0.1.zip]

36 comments:

Anonymous said...

This addin is password protected. Any idea how to peek under the covers because I don't like running software on my pc without knowing what it does.

Frans King said...

The password is lem0nade

EResourcesForSuccess said...

Honesty is what's important - that and a good Stock tse stock quotes strategy!

Anonymous said...

Frans, did you write this?

Frans King said...

I certainly did. It has some very obvious problems - late binding inefficiency for example - but it just supposed to be a demo.

Anonymous said...

Very nice job. It's a good replacement for Office 2007 beta people because the MSN version doesnt work like it used too!

Thanks for this!

no one said...

how do i get teh data to be updated automatically.. or create a macro to refresh the data ???

Frans King said...

If you wanted real time updates you could use a Timer to register a recalc function and have the sheet recalculate every 15 minutes or so. This is great solution though.

One alternative is to write a real time data server (RTD) in something like C#. I'll try to post an example at some point.

Frans King said...

Well finally I have written an RTD version of the YF_Price function - http://fransking.blogspot.com/2007/03/yfquotertd-real-time-data-service-for.html

jr00000000001 said...

There is a v.good thread on the stock prices addin for excel and getting the thing working here.. http://www.nettechguide.com/forums/showthread.php?p=9193953#post9193953

federico said...

I have a problem when trying to get quotes for symbols larger than 12 characters. Is there a way to make this work?

Frans King said...

Hi frederico can you give me an example ticker to test with.

Thanks.

federico said...

For example, it works with AMXL.MX but not with:
COMERCIUBC.MX
TLEVISACPO.MX
And others that are 13-char long.
Thanks!

Frans King said...

I'll try those out.

Are you using the vba addin (yf_price) or the c# one (yfq_price)?

federico said...

VBA

Frans King said...

The vba version isn't really supported anymore and has been replaced with the c# one.

you can download the c# version by clicking on the links on the most recent post.

I'll take a look at the vba one just to double check though.

Anonymous said...

I am using the CLOSE keyword and it generally does not match the actual close. Am I missing something?

Shan said...

Check out www.greenturtle.us which provides a free Excel yahoo quote addin as a RTD server.

Anonymous said...

???????????

XLConsulting said...

Hello,

you can also use Financial Link for Excel to load updating stock quotes, financial statements, estimates, price histories into Excel. Easy-to-use formulas to load the data and also wizards to help you creating the formulas.

More detailed information here:

http://www.xlconsulting.net/flink.php

ry said...

EVEN by wow gold the standards gold in wow of the worst financial buy wow gold crisis for at least wow gold cheap a generation, the events of Sunday September 14th and the day before were extraordinary. The weekend began with hopes that a deal could be struck,maplestory mesos with or without government backing, to save Lehman Brothers, America''s fourth-largest investment bank.sell wow gold Early Monday buy maplestory mesos morning Lehman maplestory money filed for Chapter 11 bankruptcy protection. It has more than maplestory power leveling $613 billion of debt.Other vulnerable financial giants scrambled maple money to sell themselves or raise enough capital to stave off a similar fate. billig wow gold Merrill Lynch, the third-biggest investment bank, sold itself to Bank of America (BofA), an erstwhile Lehman suitor,wow power leveling in a $50 billion all-stock deal.wow power leveling American International Group (AIG) brought forward a potentially life-saving overhaul and went maple story powerleveling cap-in-hand to the Federal Reserve. But its shares also slumped on Monday.

Anonymous said...

It seems that the add-in cannot work behind my company proxy server

Any solutions?

Terry

ameraussy said...

Frans, Great work! Thanks!

ameraussy said...

Frans, Great work! Thanks!

Supratik said...

Hi Frank,
I could not download add-in. The link is not found.

Please help.

Supratuj

Frans King said...

E-mail me at frans 'underscore' king 'at' hotmail 'dot' com and I'll e-mail you the zip file.

I moved broadband providers so the zen url doesn't exist anymore.

Jack Robins said...

beautiful, this is exactly what I was looking for!

reyt said...

We world of warcraft power leveling always archlord power leveling and flyff power levelingand flyff gold orflyff gold link 2moons gold or2moons dilwow knight online power leveling

Anonymous said...

Frans. You did a good job.I have no problem using the Addins file you created but I can't make the stock quotes updated automatically.
Is there any setting that I missed.Thanks

Mad said...

Auto Update could be done by changing the macro of GetData() as
Application.OnTime Now + TimeValue("0:01:00"), "GetData"


right click on Download Button and edit the macro

Jay said...

Just wanna say thank you for providing this code. I really appreciate it.

Also, just an FYI: I had to update YFStatic with a new Yahoo finance URL:

Public Const QUOTE_URL = "http://download.finance.yahoo.com/d/quotes.csv?s="

Thanks again.

milf said...

dessicant air dryer pediatric asthma asthma specialist
carpet cleaning dallas tx carpet cleaners dallas carpet cleaning dallas
beach vacations your beach vacations
bob hairstyle
bob haircuts bob layered pob hairstyle
bobbed classic bob Care for Curly Hair
Tips for Curly Hair curly hair 12r 22.5 best price
tires truck bus tires 12r 22.5 washington new house
new house Houston new house san Antonio new house ventura
sealy air beds portable portables air beds
antique doorknobs drying desiccant
air drying desiccantlipitor allergic reactionsApple prodam iphone praha

milf said...

new houston house houston house tx stains removal dye
stains removal clothes stains removal teeth whitening
teeth whiteningbright teeth jennifer grey nose
jennifer nose jobs calebrities nose jobs Women with Big Noses
Women hairstyles Big Nose Women, hairstylesdvd player troubleshootingtroubleshooting with the dvd playercheap beach vacationsnight vision binoculars bargainsflorida headache clinic

セフレ 不倫 出会い メル友 said...

デリヘル デリヘル デリヘル デリヘル デリヘル デリヘル デリヘル 新宿 デリヘル セフレ デリヘル デリヘル デリヘル デリヘル デリヘル デリヘル デリヘル デリヘル デリヘル デリヘル デリヘル デリヘル デリヘル デリヘル デリヘル デリヘル デリヘル デリヘル デリヘル デリヘル デリヘル デリヘル デリヘル デリヘル デリヘル デリヘル 渋谷 デリヘル デリヘル デリヘル デリヘル デリヘル 出会い 出会い 出会い 出会い 出会い 完全無料 出会い 完全無料 出会い 無料 出会い系

セフレ 不倫 出会い メル友 said...

出逢い 無料 出逢い 掲示板 出合い系サイト 出会い 掲示板 出会い ランキング 出会い系サイト 無料 出会 人妻 出会い系 出会い系 不倫 出会 無料 無料 出会 無料 出会 出会 無料 出会 無料 出会 無料 無料 出会 無料 出会 完全無料 出会 赤坂 バー 風俗 風俗 風俗 風俗 風俗 風俗 風俗 風俗 風俗 風俗 風俗 風俗 風俗 渋谷 風俗 風俗 渋谷 風俗 池袋 キャバ嬢 0930 風俗

セフレ 不倫 出会い メル友 said...

出会い系 出会い系 出会い系 出会い系 出会い系 出会い系 出会い系 出会い系 出会い系 出会い系 出会い系 出会い系 出会い系 出会い系 出会い系 出会い系 出会い系 出会い系 出会い系 出会い系 出会い系 出会い系 出会い系 出会い系 出会い系 出会い系 出会い系 出会い系 出会い系 出会い系 出会い系 出会い系 出会い系 出会い系 セフレ セフレ セフレ セックスフレンド セックスフレンド セックスフレンド 人妻 人妻 人妻 出会い系 無料 出会い系 無料 出会い系サイト メル友 競馬予想 競馬予想 競馬予想 競馬予想 競馬予想 競馬予想 競馬予想 競馬予想 競馬予想 競馬予想 競馬予想 競馬予想 競馬予想 競馬予想 競馬予想 競馬予想 競馬予想 競馬予想 競馬予想 競馬予想

Post a Comment