Share Prices in Excel
tlfAddin.xla
Eventide created an Excel add-in which allows easy download of share prices and other data from Yahoo. He no longer plans on supporting the tool for further development but it's well-used so we've added it here so that it does not get lost.
The original release is here: https://www.lemonfool.co.uk/viewtopic.php?f=27&t=8229
A subsequent update is described here: https://www.lemonfool.co.uk/viewtopic.php?f=27&t=8332
The original release is here: https://www.lemonfool.co.uk/viewtopic.php?f=27&t=8229
A subsequent update is described here: https://www.lemonfool.co.uk/viewtopic.php?f=27&t=8332
This is the latest version of the add-in
|
|
Download the file to any convenient location on your PC. The normal default location for Excel add-ins is C:\Users\YourAccountAppData\Roaming\Microsoft\AddIns
Open Excel, select Developer menu, then Add-ins. Click the Browse button and locate the downloaded file, then click OK, and the add-in is then installed
To use the add-in, open a new Excel workbook, then
That is a very simple example. You could have multiple Yahoo share symbols in a column, and multiple getdata formulas in another column. Or you could replace the cell reference A1 in the getdata formula by using =getdata("VOD,L")
As a default, getdata retrieves the latest price from Yahoo, but other data is available. This is implemented by a second parameter after the stock symbol:
=getdata(stock symbol, parameter)
where parameter is:
Open Excel, select Developer menu, then Add-ins. Click the Browse button and locate the downloaded file, then click OK, and the add-in is then installed
To use the add-in, open a new Excel workbook, then
- in cell A1, type VOD.L
- in cell B1, type the formula =getdata(A1)
- Typically, cell B1 displays ::queued:: Right-click on B1 and select Refresh Prices Once, and the latest price from Yahoo for VOD.L will be displayed in B1
That is a very simple example. You could have multiple Yahoo share symbols in a column, and multiple getdata formulas in another column. Or you could replace the cell reference A1 in the getdata formula by using =getdata("VOD,L")
As a default, getdata retrieves the latest price from Yahoo, but other data is available. This is implemented by a second parameter after the stock symbol:
=getdata(stock symbol, parameter)
where parameter is:
Company name....................................
Bid price............................................... Ask price.............................................. Last price.............................................. Change from previous close................. Previous close price............................. Day low price....................................... Day high price..................................... 52 week low price............................... 52 week high price.............................. Delay................................................... Currency.............................................. Quote Type.......................................... Market Cap.......................................... Trailing Annual Dividend Yield............. Trailing Annual Dividend Rate............. EPS Trailing 12mth.............................. 50 Day Average................................... 200 Day Average................................. Volume................................................ Exchange name.................................. |
1 or "n", or "nm" or "name" or "longname" or "shortname"
2 or "b" or "bid" 3 or "a", or "ask", or "offer" 4 or "l" or "l1", or "last" 5 or "c" or "c1" or "chg" or "change" 6 or "prv", or "prvcls" or "close" or "previousclose" 7 or "dl" or "daylow" 8 or "dh" or "dayhigh" 9 or "52wl" or "52wk_low" 10 or "52wh" or "52wk_high" 11 or "delay" or "exchangedelay" 12 or "ccy" or "currency" 13 or "type",or "quotetype" 14 or "mktcap" or "mc" or "marketCap" 15 or "divyld" or "dy" or "dividendyield" 16 or "d" or "div" or "ltmdivs" 17 or "e" or "eps" 18 or "50dma" 19 or "200dma" 20 or "v" or "vol" or "volume" 21 or "exchg" or "exchange" |
So in the simple example above, a formula in cell B1 of =getdata(A1, "name") will show the name of the company.
Note that Yahoo does not normally provide all of the above data for UK shares
Note that Yahoo does not normally provide all of the above data for UK shares