Get data from net

Hi,

I need to get data (stock price, etc) from a web site, then save it in MS sql table.

Any help will be appreciated.
Thank you.

samprgAsked:
Who is Participating?
 
AmmarRConnect With a Mentor Commented:
To get Stock prices for free you have Yahoo & Google APIs

http://stackoverflow.com/questions/281263/where-can-i-get-free-real-time-stock-data

http://stackoverflow.com/questions/527703/google-financial-api-how-get-stock-quotes

how to use Yahoo
http://www.suite101.com/content/using-yahoo-financial-a71884
http://jarloo.com/tutorials/get-yahoo-finance-api-data-via-yql/    (using C#)
http://www.gummy-stuff.org/Yahoo-data.htm

how to use Google
http://code.google.com/apis/finance/docs/finance-gadgets.html

But it wont be real time.

Now to automate the process you have many options.

either use a .net application to
1. Download the file & insert in SQL and then use a scheduled task to run it daily
or use SSIS, in SSIS you have the ability to download the file. and direclty insert it in SQL
and then a sql job will help you run it on scheduled basis


download a file with SSIS
http://www.sqlis.com/post/Downloading-a-file-over-HTTP-the-SSIS-way.aspx

import an Excel file into SQL using SSIS
http://www.builderau.com.au/program/sqlserver/soa/How-to-import-an-Excel-file-into-SQL-Server-2005-using-Integration-Services/0,339028455,339285948,00.htm


hope it helps
0
 
Alpesh PatelAssistant ConsultantCommented:
Please elaborate your need.
0
 
malikirfan28Commented:
From which service provider you need to get data of stock price. Kindly note that I have used Xignite for live stock prices in my application. They have provided an API for C# and by using their API I gave a call to their web services and get back data.

So you can check your service provider that does they have given any API for it. If they have given it then you can use their API for getting data. Once you get data then you can use ADO.Net to insert data into database.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
AmmarRCommented:
hi

check out these websites

http://www.stockpricedownloads.com/services.asp

http://www.eoddata.com/products/default.aspx

you can download the data and then import it into SQL, or if you want to automate the process you can use SSIS to create a package and then use a sql job to run it on scheduled basis.

Regards
0
 
AmmarRCommented:
there are ready webservices that you can use as well.

http://www.webservicex.net/ws/WSDetails.aspx?WSID=9&CATID=2
0
 
samprgAuthor Commented:
Hi Ammar,

Yes,I want to automate the process, can you give me more detail

Thank you so much
0
 
samprgAuthor Commented:
Hi Ammar,

Yes,I want to automate the process, can you give me more detail
I want to download the data but free

Thanks & regards.
0
 
samprgAuthor Commented:
Hi Ammar,

Thanks a lot,
I have other way, I can pull the data by Excell,
now I need to open Excell from SQL, I have already code for that but dosent work,
can you tel my why dosent work?!

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
GRANT EXECUTE ON [sys].[sp_OASetProperty] TO [public]
GRANT EXECUTE ON [sys].[sp_OAMethod] TO [public]
GRANT EXECUTE ON [sys].[sp_OAGetErrorInfo] TO [public]
GRANT EXECUTE ON [sys].[sp_OADestroy] TO [public]
GRANT EXECUTE ON [sys].[sp_OAStop] TO [public]
GRANT EXECUTE ON [sys].[sp_OACreate] TO [public]
GRANT EXECUTE ON [sys].[sp_OAGetProperty] TO [public]
exec sp_configure 'Ole Automation Procedures', 1
go
reconfigure
go

declare @xlApp integer
declare @rs integer
execute @rs = master.dbo.sp_OASetProperty @xlApp, 'ScreenUpdating', 'False'
execute @rs = master.dbo.sp_OASetProperty @xlApp, 'DisplayAlerts', 'False'

declare @xlWorkbooks integer
execute @rs = master.dbo.sp_OAMethod @xlApp, 'Workbooks', @xlWorkbooks OUTUT

declare @xlWorkbook integer
execute @rs = master.dbo.sp_OAMethod @xlWorkbooks, 'Open', @xlWorkBook OUTPUT, 'd:\M1.xlsx'

declare @xlWorkSheet integer
execute @rs = master.dbo.sp_OAMethod @xlWorkBook, 'ActiveSheet', @xlWorkSheet OUTPUT

---- Give me this error
Msg 10019, Level 16, State 0, Line 0
sp_OACreate has not yet been called successfully for this command batch.
0
 
samprgAuthor Commented:
Thanks a lot
0
 
AmmarRCommented:
hi

you need to add the following to your script

declare @xlApp integer, @rs integer
execute @rs = dbo.sp_OACreate 'Excel.Application', @xlApp OUTPUT

check your code below after i added the line, it works now.
-----
check out this article it will help you alot with your Excel
http://www.sqlservercentral.com/articles/Excel/64838/
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
GRANT EXECUTE ON [sys].[sp_OASetProperty] TO [public]
GRANT EXECUTE ON [sys].[sp_OAMethod] TO [public]
GRANT EXECUTE ON [sys].[sp_OAGetErrorInfo] TO [public]
GRANT EXECUTE ON [sys].[sp_OADestroy] TO [public]
GRANT EXECUTE ON [sys].[sp_OAStop] TO [public]
GRANT EXECUTE ON [sys].[sp_OACreate] TO [public]
GRANT EXECUTE ON [sys].[sp_OAGetProperty] TO [public]
exec sp_configure 'Ole Automation Procedures', 1
go
reconfigure
go

declare @xlApp integer, @rs integer
execute @rs = dbo.sp_OACreate 'Excel.Application', @xlApp OUTPUT

execute @rs = master.dbo.sp_OASetProperty @xlApp, 'ScreenUpdating', 'False'
execute @rs = master.dbo.sp_OASetProperty @xlApp, 'DisplayAlerts', 'False'

declare @xlWorkbooks integer
execute @rs = master.dbo.sp_OAMethod @xlApp, 'Workbooks', @xlWorkbooks OUTUT

declare @xlWorkbook integer
execute @rs = master.dbo.sp_OAMethod @xlWorkbooks, 'Open', @xlWorkBook OUTPUT, 'd:\M1.xlsx'

declare @xlWorkSheet integer
execute @rs = master.dbo.sp_OAMethod @xlWorkBook, 'ActiveSheet', @xlWorkSheet OUTPUT

Open in new window

0
 
AmmarRCommented:
by the way here is another article that compares Writing to a File Using the sp_OACreate Stored Procedure and OSQL

check it for better performance

http://www.sqlservercentral.com/articles/Miscellaneous/writingtoafileusingthesp_oacreatestoredprocedurean/1694/
0
 
samprgAuthor Commented:
Thanks Ammar,

Last question, I need to take some rows from excell, do you have command for that in sql or excell?

Thanks & regards
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.