Get Real Time Stock Quote with T-SQL

 

Recently we released an update to EDIS to do a web service call and get the results back to a table in XML or JSON format. I didn’t realize at the time that this feature could evolve to do so much, but basically any website that has an OData API was fair game to try the web service against. As a test, I created a stored proc that leveraged the web service tool to get a stock price quote from yahoo finance. Below is the code. You just pass in the stock ticker symbol, and EDIS does the rest quick and easy. To try this, there is a free demo version of EDIS at http://www.sqletl.com for download.

 

CREATE proc [dbo].[usp_get_stock_price]
@stock_ticker varchar(4)
as
declare @url varchar(max) = ‘http://finance.yahoo.com/webservice/v1/symbols/’+@stock_ticker+’/quote?format=xml’

exec SSISDB.MDDataTech.usp_run_web_request
@url = @url
,@output_tbl_nm = ‘##quote_price’
;

declare @res xml = cast((select * from ##quote_price) as xml);
declare @curr_price varchar(50) = @res.value(‘(list/resources/resource/field[@name=”price”]) [1]’,’varchar(50)’)
print @curr_price

 

 

 

Advertisements
Get Real Time Stock Quote with T-SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s