Stock Prices 1

[dswitzer]

SQL 2000
I am setting up a database of stock data to play with. Nothing huge.

My current import process is to gather the stock info in Excel through some macros that grab stock data from yahoo. Then I import that into SQL.

Has anyone done anything like this straight through SQL (so I can bypass MSExcel)? Kind of a long shot -- I'm really not sure what would be involved to get that into SQL or if it is even feasible -- but you guys almost always suprise me with your knowledge and creativity. My process works -- but it is not easy (or robust) to automate -- and it is cumbersome.

Thanks.

Dave

 

[gmmastros]

I've heard of this being done. The solution wasn't quite perfect since it causes log files to quickly grow to 46 gigabytes.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[onpnt]

I bet that was from poor administration more than anything. The log file growth that is ;-)

[sub]____________ signature below ______________
Backups are for sissies!!!!

http://www.lessthandot.com

coming to your keyboards soon[/sub]

 

[onpnt]

Dave, based on the version of SQL Server a service based on a simple request to grab the stock data and to insert it into a database would be a good route. There is no magic really to it other than the options you have available on your server OS.

If you can upgrade or use an '05 box then you can do this easily with CLR and a scheduled job as well. I've tested both scenarios and both worked well

[sub]____________ signature below ______________
Backups are for sissies!!!!

http://www.lessthandot.com

coming to your keyboards soon[/sub]

 

[SQLDenis]

>>Has anyone done anything like this straight through SQL

I have done it from C#, I created a GetQuote Class, From the main app I would call the GetQuote which called the yahoo URL, after that I would insert it into the DB

below is the code you can figure out the rest

Here is the GetQuote Class

using System;
using System.Collections;
using System.Data;
using System.Net;
using System.IO;
namespace WindowsApplication1
{
	/// <summary>
	/// Summary description for GetQuote.
	/// </summary>	
    /// 
    public class GetQuote
	{
		public string[] Quote( string symbol )
		{
		
			string url;  //stores url of yahoo quote engine
			string buffer;
			string[] bufferList;
			WebRequest webRequest;
			WebResponse webResponse;
			
			url = "[URL unfurl="true"]http://quote.yahoo.com/d/quotes.csv?s="[/URL] + symbol + "&d=t&f=l1t1c1oj1p2";
				
			webRequest = HttpWebRequest.Create( url );
			
			try 
			{
				webResponse = webRequest.GetResponse();
				using( StreamReader sr = new StreamReader( webResponse.GetResponseStream() ) )
				{
					buffer = sr.ReadToEnd();
					sr.Close();
				}
			
				buffer = buffer.Replace( "\"", "" );
				buffer = buffer.Replace( "\r\n", "" );
				if (buffer.Length ==0)	
				{ 
					buffer ="N/A,00.00,8/3/2005,3:51pm,+0.00,00.00";
				}
			}
			catch {
					buffer ="N/A,00.00,8/3/2005,3:51pm,+0.00,00.00";
					Console.WriteLine("Exception Occured");
			}

				bufferList = buffer.Split( new char[] {','} );
				//Console.WriteLine("we are returning");
		
			

		return bufferList;
			
	}

		
	}
}

Here is the DB insert method

public bool Insert( string Quote  ,decimal Price, decimal PriceDiff, string PriceDiffPercentage)
		{
			bool boolStatus = false;
			
			
			
			

			

	
			try
			{
				SqlCommand cmdNew     = new SqlCommand("QuoteInsert", this.sqlConnection1);
				SqlDataAdapter sdaNew = new SqlDataAdapter(cmdNew);
				//DataSet  dsEmployees  = new DataSet();

				cmdNew.CommandType = CommandType.StoredProcedure;

				// We will need a SQL parameter to carry the arguments
				// Declare its variable
				SqlParameter parNew   = new SqlParameter();
				// Specify the name of the argument
				parNew.ParameterName  = "@chvQuoteSymbol";
				// Specify the SQL data type of the argument
				parNew.SqlDbType      = SqlDbType.VarChar;
				// Specify the value passed as argument
				parNew.Value          = Quote;

				// Once the argument is ready, add it to the list of arguments
				cmdNew.Parameters.Add(parNew);

				parNew   = new SqlParameter();
				parNew.ParameterName  = "@mnyPrice";
				parNew.SqlDbType      = SqlDbType.Money;
				parNew.Value          = Price;
				cmdNew.Parameters.Add(parNew);

				parNew   = new SqlParameter();
				parNew.ParameterName  = "@mnyPriceDiff";
				parNew.SqlDbType      = SqlDbType.Money;
				parNew.Value          = PriceDiff;
				cmdNew.Parameters.Add(parNew);

				parNew   = new SqlParameter();
				parNew.ParameterName  = "@chvPriceDiffPercentage";
				parNew.SqlDbType      = SqlDbType.VarChar;
				parNew.Value          = PriceDiffPercentage;

				cmdNew.Parameters.Add(parNew);
				sqlConnection1.Open();
				cmdNew.ExecuteNonQuery();
				//return the results of the procedure
				//if( (Int32)cmdNew.Parameters["@RETURN_VALUE"].Value == 0 )
					//boolStatus = true;
			}
			catch( SqlException exception )
			{
				
				Console.WriteLine ( exception.Message );
			}
			catch( Exception exception )
			{
				Console.WriteLine ( exception.Message );
			}
			finally
			{
				sqlConnection1.Close();
			}

			return boolStatus;
		}

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[RiverGuy]

Three words/abbreviations for you:

ETL with SSIS.

 

[RiverGuy]

You said SQL 2000, replace SSIS with DTS

 

[chrissie1]

One word webservices. ;-)

Christiaan Baes
Belgium

My Blog

 

[onpnt]

A few words. All part of what I said with research ;-)

[sub]____________ signature below ______________
Backups are for sissies!!!!

http://www.lessthandot.com

coming to your keyboards soon[/sub]

 

[dswitzer]

Thanks to all.

I have been running automated Excel macros and then secondary jobs in SQL2000 -- not too robust but with a little elbow grease it has been running...

Between Dennis' code and this link:

http://www.codeproject.com/KB/vb/webresourceprovidernet.aspx

I understand how to get this...very nice.

Some of these other ways are no doubt quite good too -- but probably over my head since I don't even understand the acronyms...

I think I am close to eliminating the Excel piece!

Thanks.
Dave