|
It means that you cannot attach it to an SQL Server 2005 instance. Why don't you try SQL Server 2008 Express. Its free 
|
|
|
|
|
Use Joint Venture Booster And Gain More With Less Effort. Easily Win JV-Partners And Let Them Promote Your Products On Their Thank-You-Pages. Simple To Install And Admin, Yet Highly Customizable. Unlimited JV/Ads. Works With Any Affiliate Program. Visit us at: http://www.clicknearn.net/3152-72.html
|
|
|
|
|
I am trying to decide on how to implement the following database for a stock market program for historical data, using SQL Server.
Originally my thoughts were to have data columns with adjusted data (for stock splits) as follows:
Example 1:
DateTime,
DayOpen, DayHigh, DayLow, DayClose, DayVolume,
WeekOpen, WeekHigh, WeekLow, WeekClose, WeekVolume,
MonthOpen, MonthHigh, MonthLow, MonthClose, MonthVolume
But more recently my thoughts are to load the database with only historically real information, with a variable to calculate the split info as follows
Example 2:
DateTime, Open, High, Low, Close, Volume, AdjustmentVariable
I will be storing current needed data in C# datasets and only access historical data when needed (changes such as stock splits, new symbols to track, etc.). Everyday I will update the database with the current data and change the AdustmentVariable as needed when the stock splits, then reload new data to the datasets.
Considering speed, ease of use, memory, etc. what would be the better way to proceed with this.
1. Use the second example using stored procedures, calculate and retrieve data when requested per stock.
2. Use a combination of the two above and calculate data weekly, monthly and retrieve data as needed, will this be memory inefficient with all the unfilled dates during the middle of the week, months.
3. Setup a Data Warehouse which will calculate the data daily after updating and I retrieve as needed.
Any advice will be greatly appreciated,
Michael
|
|
|
|
|
As a general rule, you shouldn't store any value that you can calculate... HOWEVER... the one main instance for breaking this rule is where the volume of data is large and the calculation needs to be performed frequently, therefore making it a better option to store the calculated values for speed and efficiency purposes (even if doing so adds a level of complexity - ie: maintaining the stored calculated values to ensure they are always correct)
If the volume of data is low, or the rate you are expecting to calculate the information is infrequent, then go with the first rule of not storing values that you can calculate and then calculate them in real time as you need them.
HTH...
|
|
|
|
|
I will have a database setup for every exchange this adds up in the US to 30,000+ securities. I think example 1 does not seem like a very efficient way to handle the problem. What are your thoughts about using a data warehouse to calculate/accumulate the weekly, monthly, etc. data, this seems like it will be a much easier way. to gather this info. Using a stored procedure to get this info would be much more difficult. Though I would only need to retrieve the info when needed.
Michael
|
|
|
|
|
MAW30 wrote: I will have a database setup for every exchange
Why would you do this rather than simply have some kind of relationship to a table of exchange information? If the data is in the same format, this would save you some effort - adding a new exchange would be as simple as adding a single record into the exchange table and then populating the data - no code changes required.
MAW30 wrote: using a data warehouse to calculate/accumulate the weekly, monthly, etc. data
For historical data (that is presumably never going to change) this is an excellent idea. See Mycroft Holmes' post below.
|
|
|
|
|
I am not sure what you mean by the statement "Why would you do this rather than simply have some kind of relationship to a table of exchange information".
How would I go about doing this.
Michael
|
|
|
|
|
You said that you would have a separate table for each exchange. That's one option.
Another option would be to have a single table for the stock data you were speaking about (daily high, low etc), and (assuming that the data structure is the same for each exchange - which I'm sure it would have to be?) a foreign key that links the data to a second table that holds the exchange information.
eg:
Table 1 - tblStockData
StockDataID
StockDataStockCode
StockDataDailyHigh
StockDataDailyLow
etc etc
StockDataExchangeID -- Link this to ExchangeID in table tblExchange
Table 2 - tblExchange
ExchangeID
ExchangeName
etc etc
|
|
|
|
|
If you have a long time series, you can have hundreds of million or even billions of rows if you have multiple sources (e.g. > 100 GB size). How much memory will your database have? It likely is not a good idea to store weekly or monthly data on the database for performance reasons.
You should write stored procedures and functions to return the data that you want.
|
|
|
|
|
Btw, your main concern setting up the database should be dealing with the issues around ticker changes, cusip changes, stock splits, dividends, calculating cumulative returns, etc.
|
|
|
|
|
As Damian suggested, storing the calc values is technically incorrect but may be valid for performance reasons, this will depend greatly on your usage of the data.
I would probably set up 2 structures, the current data in a normal relational database using views to supply the calcs and a data warehouse with a properly designed cube to handle the historical data. This has been done many times, I'd be surprised if there is not a case study out there on the exact structure you are proposing.
|
|
|
|
|
Your longest time series will have slighly more than 12,000 datapoints assuming you download data from 1970, which is google's start date.
Your dataset using option 2 should be at most 1 GB per 2,000 stock ID assuming you are only loading data from a single source and chose the right data types. So if you have 16+ GB of memory, the entire database might reside in memory and you would have associate performance pickups.
Assuming you do not have that powerful of a machine (otherwise, your firm might already have this data), then using a combination of option 1 and 2 might be best for performance of your application. You store historical data in option 2 format and precompute the data in option 1 format for the next business day. I am assuming that you are not incorporating in live data right now. The precomputed data will not take up much space and can be overwritten each night or as needed (e.g. you could add a time stamp to option 1's table. If the time stamp is too old, then you call a function to fill the table before returning results.)
modified on Sunday, November 22, 2009 11:26 PM
|
|
|
|
|
I am trying to create a TFS report which requires MDX. I am making a report that requires a calculated member. I am not 100% sure how this is done. The member i am trying to do is a difference calculation.
For example:
Table
Sept 1 Sept 2 Sept 3
Actual 0 32 58
Remaining 163 140 132
Difference 0 9 50
The calculation for the difference is as follows:
Actual Effort for that day - (Work remaining previous day - Work remaining that day)
32 - (163 - 140) = 9
Can someone help please? 
|
|
|
|
|
Without any table definition of columns, it's hard to guess at what to provide. As a suggestion, though you may need to join the table on itself in order to have access to "today's" values and "yesterday's" values.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
I have installed Business Intelligence Development from SQL Server 2008 to use to create TFS Reports In Visual Studio 2008. When I add a new report a query builder window opens. This is where Measures and other information to get the data is done. This is where I am trying to create the Calculated member. Any suggestions??
|
|
|
|
|
Hi all,
I want to export my data from a SQL-Server table to an Excel-File.
The following code shows my routine.
oConnection.ConnectionString = "Provider=SQLOLEDB; Server=Servername; database=ABC; Trusted_Connection=yes; User ID=ABC_MAN; Password=secretABC";
oConnection.Open();
oCmd.Connection = oConnection;
oCmd.CommandTimeout = 60;
oCmd.CommandText = "SELECT id AS NO, name AS NAME INTO [Excel 8.0;DATABASE=Test.xls] FROM MyDataTable WHERE typ=1";
oCmd.ExecuteNonQuery();
But instead of an exel file the string creates a new table with the name Excel 8.0;DATABASE=Test.xls
Where is my fault ? Different examples has shown that this should work 
Thanks
Frank
|
|
|
|
|
The SQL SELECT INTO statement is used to select data from a SQL database table and to insert it to a different table at the same time.
Tutorial[^]
|
|
|
|
|
Thank you,
I know what SQL normally does ...
but I think you don't know what I am expecting from this query ...
Thank
Frank
|
|
|
|
|
|
It looks like you want something like this:
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;Database=C:\testing.xls;', 'SELECT Name, Email FROM [Sheet1$]')
SELECT Name, Email FROM tblnames
or
1 Export data to existing EXCEL file from SQL Server table
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from SQLServerTable
I'm not sure of the correct syntax since I haven't used it before.
I found it at after searching for "sql server export to excel [Excel 8.0;DATABASE=Test.xls]"
Scott
|
|
|
|
|
Thank you,
have tried your suggestions, but got some OLEDB-unknown errors, what ever that meens.
(But I think,(not sure) the disadvantage is that the Excel-table must already exist)
But thanks alot
Frank
|
|
|
|
|
|
Hi Richard,
it's an interesting link - I will convert the code to C# - will take some minutes 
I will let you know the result.
Tnx
Frank
|
|
|
|
|
Try this
Create an Excel file named testing having the headers same as that of table columns and use this query
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from SQLServerTable
To export data from Excel to new SQL Server table,
select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
To export data from Excel to existing SQL Server table,
Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [SheetName$]')
Obtained from SQL Server Forums - Export to Excel
Hope this helps.
Niladri Biswas
|
|
|
|
|
Thank you,
I also found this example, but the examples assume, that an Excel file already exists.
But I remember, 5 years ago, I could generate a new file with the sql-sting above (within VB6) without an additional action.
Why can't I do that today - or how can I do that today ?
Tnx for your time
Frank
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
