Home  Users HOWTO: Replace Yahoo Quotes with SQL
|
|
HOWTO: Replace Yahoo Quotes with SQL |
|
|
|
|
Written by Administrator
|
|
Monday, 14 August 2006 |
Setup
AST uses unixODBC for database connectivity. You will need to have unixODBC installed prior to installing AST. AST must be compiled (source installation) with SQL support because the binary distribution does not have SQL support enabled. To enable SQL, run ./configure --enable-sql when compiling AST. You will need to setup a DSN for AST to use with unixODBC (Information on configuring unixODBC).
In AST, login as admin and click on Setup and then System Settings. Under database options you need to enter the Data Source Name (DSN) you configured unixODBC with and the username and password for that DSN. You can enable SQL support for every different type of quote by clicking on the Quote Engines tab and changing from Yahoo! Finance (default) to SQL Database.
The SQL engine in AST looks for some specific tables. You will likely want to create a view on your existing data for ast to use. If any information is not available, you can set it to zero, blank or NULL. You only need to create views for the features you enable (for example, if you want to use Yahoo! for dividend information you don't need to create the dividend quote table). The tables used are described below:
Views
Below is a list of all of the tables or views AST requires.
Realtime Quotes - ast_realtime_quote
| Column |
Data Type |
Description |
| symbol |
varchar(10) |
Ticker symbol of the security |
| market_key |
varchar(4) |
Identifier for the market, set this up in the market configuration in AST |
| date |
timestamp |
Quote date |
| time |
varchar(10) |
Quote time (text format) |
| open |
float |
Open price for the day |
| high |
float |
Highest price of the day |
| low |
float |
Lowest price of the day |
| price |
float |
Last trae price |
| change |
float |
Change between yesterday's close and last price |
| volume |
integer |
Volume so far today |
| daily_avg_vol |
integer |
Average daily volume for this security (0 if unavailable) |
| prev_close |
float |
Closing price yesterday |
| avg_50_dy |
float |
Average price over the last 50 days. |
| avg_200_dy |
float |
Average price over the last 200 days |
| bid |
float |
Last bid price |
| bid_size |
integer |
Size of last bid |
| ask |
float |
Last ask price |
| ask_size |
integer |
Size of last ask |
Historical Quotes - ast_daily_quote
Also, ast_weekly_quote and ast_monthly_quote. All views should have the same format.
| Column |
Data Type |
Description |
| symbol |
varchar(10) |
Ticker symbol of the security |
| market_key |
varchar(4) |
Identifier for the market, set this up in the market configuration in AST |
| date |
timestamp |
Quote date (start of period, e.g. first day of week) |
| open |
float |
Open price for the day/week/month |
| high |
float |
Highest price of the day/week/month |
| low |
float |
Lowest price of the day/week/month |
| close |
float |
Closing price for the period (day/week/month) |
| volume |
integer |
Total volume for the period (day/week/month) |
| adjclose |
float |
Closing price adjusted for splits. For example, if the close was $20 and then there was a two for one split, the adjclose would be $10 (close should still be $20). If the data is already adjusted for splits, set adjclose=close. |
Dividend Quotes - ast_dividend_quote
| Column |
Data Type |
Description |
| symbol |
varchar(10) |
Ticker symbol of the security |
| market_key |
varchar(4) |
Identifier for the market, set this up in the market configuration in AST |
| date |
timestamp |
Payment date for the dividend |
| value |
float |
Dividend value (per share) |
Extra Information - ast_detail_quote
| Column |
Data Type |
Description |
| symbol |
varchar(10) |
Ticker symbol of the security |
| market_key |
varchar(4) |
Identifier for the market, set this up in the market configuration in AST |
| eps |
float |
Earnings Per Share (last year) |
| short_ratio |
float |
Current short ratio (set to 0 if not available) |
| dividends_per_share |
float |
Dividends per share last year or current (set to 0 if not available) |
| one_yr_target |
float |
Analyst's 1-year price target (set to 0 if not available) |
| eps_est_cur_yr |
float |
Earnings Per Share estimate for the current year (set to 0 if not available) |
| eps_est_next_yr |
float |
Earnings Per Share estimate for next year (set to 0 if not available) |
| eps_est_next_qtr |
float |
Earnings Per Share for the next quarter (set to 0 if not available) |
| eps_peg_ratio |
float |
Price / Earnings Growth ratio (set to 0 if not available) |
| book_value |
float |
Book value (set to 0 if not available) |
| after_hours_change |
float |
Unused (set to 0) |
| ebitda |
float |
EBITDA value (set to 0 if not available) |
| shares_outstanding |
integer |
Total number of shares issues (set to 0 if not available) |
|
|
Last Updated ( Monday, 14 August 2006 )
|
|