Share Prices in Excel - Fast and Easy
Posted: November 4th, 2017, 4:26 pm
If you used the original LivePrice.xls excel spreadsheet from TMF back in the day, it will not work as of 2 November. If you still want to retrieve share prices in excel, there is a solution, at least for now. Although the HYPTUS guys are incorporating some variant of this into their project, I suspect there are a number of users out there who have their own spreadsheet and layouts, and just want to continue to have a very simple function to retrieve the data of interest.
The excel addin at the following link provides exactly that functionality:
- It adds a new function to excel =getdata("ticker", "field") which is basically the same as the old function in the liveprice spreadsheet
- It adds a command to the mouse's right click menu which will update prices
If you open the addin, you won't see anything except a new button on a mouse right click (which will disappear when the addin is closed when excel is closed). You can use the getdata function anywhere on your existing spreadsheets if the addin is running, which is an improvement on the old system.
If this works for you, it would make sense to recommend this post, not for my vanity but (i) to give comfort to other potential users and (ii) visibility on the popular posts list.
The link is: https://www.dropbox.com/s/11z5wycj77rr0 ... n.xla?dl=0
The code is open for inspection. It is saved as a 1997-2003 .xla file - although I use Office 365 this may have maximum compatibility with older versions of excel.
Example usage
=getdata("^FTSE", "last") in cell A1 will update the latest index value every refresh.
it will return ::queued:: while you have not refreshed the data
it will return ::tick_Err:: if the ticker is badly formed
it will return ::no_data:: if the ticker is valid but there is no field data available
Notes on Field Selection (case insensitive) in the function
1, "nm", "name": RETURNS SECURITY NAME
2, "b", "bid": RETURNS BID PRICE
3, "a", "ask", "offer": RETURNS ASK PRICE
4, "l", "last": RETURNS LAST PRICE
5, "c", "chg", "change": RETURNS CHANGE
6, "prvcls", "close", "previousclose": RETURNS PREVIOUS CLOSE
7, "daylow", "dl": RETURNS LOW OF DAY
8, "dayhigh", "dh": RETURNS HIGH OF DAY
9, "52wk_low", "52wl": RETURNS 52 WEEK CLOSING LOW
10, "52wk_high", "52wh" RETURNS 52 WEEK CLOSING HIGH
11, "delay", "exchangedelay": RETURNS EXCHANGE DELAY IN MINUTES
12, "ccy", "currency": RETURNS CCY OF QUOTES (GBP, GBp, USD etc)
13, "type", "quotetype": RETURNS SECURITY TYPE (STOCK, CURRENCY, INDEX, FUTURE ETC)
Anything else (or omitted): defaults to last price
The excel addin at the following link provides exactly that functionality:
- It adds a new function to excel =getdata("ticker", "field") which is basically the same as the old function in the liveprice spreadsheet
- It adds a command to the mouse's right click menu which will update prices
If you open the addin, you won't see anything except a new button on a mouse right click (which will disappear when the addin is closed when excel is closed). You can use the getdata function anywhere on your existing spreadsheets if the addin is running, which is an improvement on the old system.
If this works for you, it would make sense to recommend this post, not for my vanity but (i) to give comfort to other potential users and (ii) visibility on the popular posts list.
The link is: https://www.dropbox.com/s/11z5wycj77rr0 ... n.xla?dl=0
The code is open for inspection. It is saved as a 1997-2003 .xla file - although I use Office 365 this may have maximum compatibility with older versions of excel.
Example usage
=getdata("^FTSE", "last") in cell A1 will update the latest index value every refresh.
it will return ::queued:: while you have not refreshed the data
it will return ::tick_Err:: if the ticker is badly formed
it will return ::no_data:: if the ticker is valid but there is no field data available
Notes on Field Selection (case insensitive) in the function
1, "nm", "name": RETURNS SECURITY NAME
2, "b", "bid": RETURNS BID PRICE
3, "a", "ask", "offer": RETURNS ASK PRICE
4, "l", "last": RETURNS LAST PRICE
5, "c", "chg", "change": RETURNS CHANGE
6, "prvcls", "close", "previousclose": RETURNS PREVIOUS CLOSE
7, "daylow", "dl": RETURNS LOW OF DAY
8, "dayhigh", "dh": RETURNS HIGH OF DAY
9, "52wk_low", "52wl": RETURNS 52 WEEK CLOSING LOW
10, "52wk_high", "52wh" RETURNS 52 WEEK CLOSING HIGH
11, "delay", "exchangedelay": RETURNS EXCHANGE DELAY IN MINUTES
12, "ccy", "currency": RETURNS CCY OF QUOTES (GBP, GBp, USD etc)
13, "type", "quotetype": RETURNS SECURITY TYPE (STOCK, CURRENCY, INDEX, FUTURE ETC)
Anything else (or omitted): defaults to last price