程序员学炒股(6) 让我们来看一下7月份A股的表现 - 小钊^^
小钊^^发表于 2015-08-06 08:19:00
有了前几天的数据准备，那让我们看一下7月份的股市是牛市还是熊市呢？
牛市和熊市主要就看股票的涨跌分布情况，虽然我们看大盘数据，但是不过中石油占的A股市值太大了，并且中石油95%的股票都是大股东控制的相当于非流通的股票。
我们这里看一下每天的股票涨跌数量占总的可交易股票数量的百分比。我们按照每涨跌1%为一个区间进行划分。
首先我们写一个存储过程，计算每天的股票涨跌情况。
CREATE PROCEDURE [dbo].[GetDistribution]
-- Add the parameters for the stored procedure here
@date smalldatetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @days int
set @days=(select COUNT(*) from DayData Where 日期=@date)
INSERT INTO dbo.Distribution
select @date AS 日期,* from
(select COUNT(*)*1.0/@days 幅度_9 from dbo.DayData
where 日期=@date AND 涨跌幅<-9) a,
(select COUNT(*)*1.0/@days 幅度_9_8 from dbo.DayData
where 日期=@date AND 涨跌幅>-9 AND 涨跌幅<-8) b,
(select COUNT(*)*1.0/@days 幅度_8_7 from dbo.DayData
where 日期=@date AND 涨跌幅>-8 AND 涨跌幅<-7) c,
(select COUNT(*)*1.0/@days 幅度_7_6 from dbo.DayData
where 日期=@date AND 涨跌幅>-7 AND 涨跌幅<-6) d,
(select COUNT(*)*1.0/@days 幅度_6_5 from dbo.DayData
where 日期=@date AND 涨跌幅>-6 AND 涨跌幅<-5) e,
(select COUNT(*)*1.0/@days 幅度_5_4 from dbo.DayData
where 日期=@date AND 涨跌幅>-5 AND 涨跌幅<-4) f,
(select COUNT(*)*1.0/@days 幅度_4_3 from dbo.DayData
where 日期=@date AND 涨跌幅>-4 AND 涨跌幅<-3)g,
(select COUNT(*)*1.0/@days 幅度_3_2 from dbo.DayData
where 日期=@date AND 涨跌幅>-3 AND 涨跌幅<-2)h,
(select COUNT(*)*1.0/@days 幅度_2_1 from dbo.DayData
where 日期=@date AND 涨跌幅>-2 AND 涨跌幅<-1)i,
(select COUNT(*)*1.0/@days 幅度_10 from dbo.DayData
where 日期=@date AND 涨跌幅>-1 AND 涨跌幅<0)j,
(select COUNT(*)*1.0/@days 幅度01 from dbo.DayData
where 日期=@date AND 涨跌幅>0 AND 涨跌幅<1)k,
(select COUNT(*)*1.0/@days 幅度12 from dbo.DayData
where 日期=@date AND 涨跌幅>1 AND 涨跌幅<2)l,
(select COUNT(*)*1.0/@days 幅度23 from dbo.DayData
where 日期=@date AND 涨跌幅>2 AND 涨跌幅<3)m,
(select COUNT(*)*1.0/@days 幅度34 from dbo.DayData
where 日期=@date AND 涨跌幅>3 AND 涨跌幅<4)n,
(select COUNT(*)*1.0/@days 幅度45 from dbo.DayData
where 日期=@date AND 涨跌幅>4 AND 涨跌幅<5)o,
(select COUNT(*)*1.0/@days 幅度56 from dbo.DayData
where 日期=@date AND 涨跌幅>5 AND 涨跌幅<6)p,
(select COUNT(*)*1.0/@days 幅度67 from dbo.DayData
where 日期=@date AND 涨跌幅>6 AND 涨跌幅<7)q,
(select COUNT(*)*1.0/@days 幅度78 from dbo.DayData
where 日期=@date AND 涨跌幅>7 AND 涨跌幅<8)r,
(select COUNT(*)*1.0/@days 幅度89 from dbo.DayData
where 日期=@date AND 涨跌幅>8 AND 涨跌幅<9)s,
(select COUNT(*)*1.0/@days 幅度9 from dbo.DayData
where 日期=@date AND 涨跌幅>9)t
END
-- Add the parameters for the stored procedure here
@date smalldatetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @days int
set @days=(select COUNT(*) from DayData Where 日期=@date)
INSERT INTO dbo.Distribution
select @date AS 日期,* from
(select COUNT(*)*1.0/@days 幅度_9 from dbo.DayData
where 日期=@date AND 涨跌幅<-9) a,
(select COUNT(*)*1.0/@days 幅度_9_8 from dbo.DayData
where 日期=@date AND 涨跌幅>-9 AND 涨跌幅<-8) b,
(select COUNT(*)*1.0/@days 幅度_8_7 from dbo.DayData
where 日期=@date AND 涨跌幅>-8 AND 涨跌幅<-7) c,
(select COUNT(*)*1.0/@days 幅度_7_6 from dbo.DayData
where 日期=@date AND 涨跌幅>-7 AND 涨跌幅<-6) d,
(select COUNT(*)*1.0/@days 幅度_6_5 from dbo.DayData
where 日期=@date AND 涨跌幅>-6 AND 涨跌幅<-5) e,
(select COUNT(*)*1.0/@days 幅度_5_4 from dbo.DayData
where 日期=@date AND 涨跌幅>-5 AND 涨跌幅<-4) f,
(select COUNT(*)*1.0/@days 幅度_4_3 from dbo.DayData
where 日期=@date AND 涨跌幅>-4 AND 涨跌幅<-3)g,
(select COUNT(*)*1.0/@days 幅度_3_2 from dbo.DayData
where 日期=@date AND 涨跌幅>-3 AND 涨跌幅<-2)h,
(select COUNT(*)*1.0/@days 幅度_2_1 from dbo.DayData
where 日期=@date AND 涨跌幅>-2 AND 涨跌幅<-1)i,
(select COUNT(*)*1.0/@days 幅度_10 from dbo.DayData
where 日期=@date AND 涨跌幅>-1 AND 涨跌幅<0)j,
(select COUNT(*)*1.0/@days 幅度01 from dbo.DayData
where 日期=@date AND 涨跌幅>0 AND 涨跌幅<1)k,
(select COUNT(*)*1.0/@days 幅度12 from dbo.DayData
where 日期=@date AND 涨跌幅>1 AND 涨跌幅<2)l,
(select COUNT(*)*1.0/@days 幅度23 from dbo.DayData
where 日期=@date AND 涨跌幅>2 AND 涨跌幅<3)m,
(select COUNT(*)*1.0/@days 幅度34 from dbo.DayData
where 日期=@date AND 涨跌幅>3 AND 涨跌幅<4)n,
(select COUNT(*)*1.0/@days 幅度45 from dbo.DayData
where 日期=@date AND 涨跌幅>4 AND 涨跌幅<5)o,
(select COUNT(*)*1.0/@days 幅度56 from dbo.DayData
where 日期=@date AND 涨跌幅>5 AND 涨跌幅<6)p,
(select COUNT(*)*1.0/@days 幅度67 from dbo.DayData
where 日期=@date AND 涨跌幅>6 AND 涨跌幅<7)q,
(select COUNT(*)*1.0/@days 幅度78 from dbo.DayData
where 日期=@date AND 涨跌幅>7 AND 涨跌幅<8)r,
(select COUNT(*)*1.0/@days 幅度89 from dbo.DayData
where 日期=@date AND 涨跌幅>8 AND 涨跌幅<9)s,
(select COUNT(*)*1.0/@days 幅度9 from dbo.DayData
where 日期=@date AND 涨跌幅>9)t
END
然后我们统计一下今年的股票的分布情况，写入一个表中，代码如下:
declare cur Cursor scroll for
SELECT distinct 日期 FROM [dbo].[DayData]
open cur;
declare @date smalldatetime;
while @@FETCH_STATUS=0
begin
fetch next from cur into @date
exec dbo.GetDistribution @date
end
close cur
deallocate cur
SELECT distinct 日期 FROM [dbo].[DayData]
open cur;
declare @date smalldatetime;
while @@FETCH_STATUS=0
begin
fetch next from cur into @date
exec dbo.GetDistribution @date
end
close cur
deallocate cur
接下来我们就要编写一个绘制柱状图的小工具啦，这里我们直接用.Net 的Chart控件，在VisualStudio 数据控件组中就有。
var xName = new String[] { "-9", "-8", "-7", "-6", "-5", "-4", "-3", "-2", "-1", "0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10" };
var yValue=new Double[20];
string connString = "Data Source=.;Initial Catalog=Stock;Integrated Security=True";
var list = new List<String>();
using (SqlConnection connection = new SqlConnection(connString))
{
connection.Open();
String SQL = "select distinct CONVERT(varchar(10),日期,23) from dbo.DayData";
SqlCommand CMD = new SqlCommand(SQL, connection);
SqlDataReader reader = CMD.ExecuteReader();
while (reader.Read())
{
list.Add(reader[0].ToString().Trim());
}
}
foreach (var date in list)
{
using (SqlConnection connection = new SqlConnection(connString))
{
connection.Open();
String SQL = "select * from dbo.Distribution where 日期='"+date+"'";
SqlCommand CMD = new SqlCommand(SQL, connection);
SqlDataReader reader = CMD.ExecuteReader();
while (reader.Read())
{
for (int i = 0; i < 20;i++ )
{
yValue[i] = Convert.ToDouble(reader[i+1]);
}
}
}
Chart1.Series["Series1"].Points.DataBindXY(xName, yValue);
Chart1.SaveImage(@"C:\Users\20060737\Documents\Visual Studio 2013\WebSites\Distribution\"+date+".png");
}
var yValue=new Double[20];
string connString = "Data Source=.;Initial Catalog=Stock;Integrated Security=True";
var list = new List<String>();
using (SqlConnection connection = new SqlConnection(connString))
{
connection.Open();
String SQL = "select distinct CONVERT(varchar(10),日期,23) from dbo.DayData";
SqlCommand CMD = new SqlCommand(SQL, connection);
SqlDataReader reader = CMD.ExecuteReader();
while (reader.Read())
{
list.Add(reader[0].ToString().Trim());
}
}
foreach (var date in list)
{
using (SqlConnection connection = new SqlConnection(connString))
{
connection.Open();
String SQL = "select * from dbo.Distribution where 日期='"+date+"'";
SqlCommand CMD = new SqlCommand(SQL, connection);
SqlDataReader reader = CMD.ExecuteReader();
while (reader.Read())
{
for (int i = 0; i < 20;i++ )
{
yValue[i] = Convert.ToDouble(reader[i+1]);
}
}
}
Chart1.Series["Series1"].Points.DataBindXY(xName, yValue);
Chart1.SaveImage(@"C:\Users\20060737\Documents\Visual Studio 2013\WebSites\Distribution\"+date+".png");
}
这样我们就得到了每天的股票分布图，大家来看一下7月份的股票涨跌分布吧。
最左边代表的是下跌超9%的股票数占A股可交易股票数的比例，接下来是-8%,-7%直至上涨超9%的股票数量。
大家看了这张图，肯定和我一样不由得感叹，A股真是太刺激了！
本文链接：程序员学炒股(6) 让我们来看一下7月份A股的表现，转载请注明。