/**
* Prints out the records for each symbol.
*
* <p>Can also verify the state of the database with csh> sqlite3 stocks.bd ".dump"
*
* @param symbols - list of symbols to dump data for
*/
static Map<String, List<QuoteRecord>> dumpSymbols(List<String> symbols) throws Exception {
Connection conn = initialize();
Map<String, List<QuoteRecord>> symbolToQuotesMap = new TreeMap<String, List<QuoteRecord>>();
for (int i = 0; i < symbols.size(); i++) {
String symbolname = symbols.get(i);
List<QuoteRecord> symbolToQuotes = new ArrayList<QuoteRecord>();
symbolToQuotesMap.put(symbolname, symbolToQuotes);
Statement stat = conn.createStatement();
try {
ResultSet rs = stat.executeQuery("SELECT * FROM " + symbolname + ";");
// 'Date','Open','High','Low','Close','Volume','Adj Close'
while (rs.next()) {
String date = rs.getString("Date");
String open = rs.getString("Open");
String high = rs.getString("High");
String low = rs.getString("Low");
String close = rs.getString("Close");
String volume = rs.getString("Volume");
String adjClose = rs.getString("Adj Close");
QuoteRecord qr = new QuoteRecord(date, open, high, low, close, volume, adjClose);
symbolToQuotes.add(qr);
// System.out.println(symbolname + ":open," + open + ":date," + date);
}
// TODO: why/when do we need to close this?
rs.close();
} catch (java.sql.SQLException e) {
// it can happen that symbols in Symbols.java go away over time
// so we need to ignore them
}
}
return symbolToQuotesMap;
}
/**
* Makes the entries in the database for a given symbol up to date.
*
* <p>This method as it currently is written is wasteful, since it does not attempt to see if the
* date range has any actual trading days - e.g., may try to pull in a range of a Sat and a
* Sunday.
*
* @param symbol - the symbol we are trying to bring up to date
*/
static void makeUpToDate(String symbol) throws Exception {
Connection conn = initialize();
Statement stat = conn.createStatement();
ResultSet rs = stat.executeQuery("select * from " + symbol + ";");
Date today = new Date();
Date latestDateFoundSoFar = null;
Date dateBeingProcessed = null;
while (rs.next()) {
String dateString = rs.getString("Date");
dateBeingProcessed = DateFromString(dateString);
System.out.println("processing:" + dateBeingProcessed);
if ((latestDateFoundSoFar == null)
|| (latestDateFoundSoFar.compareTo(dateBeingProcessed) < 0)) {
latestDateFoundSoFar = dateBeingProcessed;
System.out.println("\tupdated latestDateFoundSoFar:" + dateBeingProcessed);
}
}
if (latestDateFoundSoFar.compareTo(today) < 0) {
Calendar cal = new GregorianCalendar();
cal.setTime(latestDateFoundSoFar);
Date succLatestDateFoundSoFar = nextDate(latestDateFoundSoFar);
if (DaysAreEqual(succLatestDateFoundSoFar, today)) {
return;
}
updateSymbol(symbol, succLatestDateFoundSoFar, today);
}
}
/**
* Deletes record for corresponding date for symbol. Used for debugging the makeUpToDate method.
* Note that the makeUpToDate won't catch missing days before the last day in the table.
*
* @param symbol - symbol for which we are to delete a record
* @param date - the date for which we want to delete
*/
static void deleteDateForSymbol(String symbol, String date) throws Exception {
Connection conn = initialize();
Statement removeSymbol = conn.createStatement();
String command = "DELETE FROM " + symbol + " WHERE ( Date = '" + date + "' ) ;";
removeSymbol.executeUpdate(command);
conn.close();
return;
}
/**
* Remove a symbol from the listofsymbols table and drop the table for the symbol.
*
* @param symbol - symbol to delete from database
*/
static void deleteSymbol(String symbol) throws Exception {
Connection conn = initialize();
Statement removeSymbol = conn.createStatement();
removeSymbol.executeUpdate(
"DELETE FROM listofsymbols WHERE symbolname = " + "'" + symbol + "'" + ";");
Statement stat = conn.createStatement();
stat.executeUpdate("drop table if exists " + symbol + ";");
conn.close();
return;
}
/**
* Drop the listofsymbols and recreate - this is a one-off method, I was adding SP symbols in
* sets, but this table was repeatedly deleted in the process.
*
* @see createDatabase
*/
public static void updateListOfSymbols(String[] symbolArray) throws Exception {
Connection conn = initialize();
Statement stat = conn.createStatement();
try {
stat.executeUpdate("DROP TABLE IF EXISTS " + "listofsymbols" + ";");
stat.executeUpdate(
"CREATE TABLE IF NOT EXISTS " + "listofsymbols" + " (" + "'symbolname'" + ");");
} catch (Exception e) {
System.out.println("failed stat.executeUpdate");
System.out.println(e.getMessage());
}
for (int i = 0; i < symbolArray.length; i++) {
stat.executeUpdate("INSERT INTO listofsymbols VALUES ('" + symbolArray[i] + "');");
}
conn.close();
}
/**
* Updates the table of quotes for this symbol. Assumes that the listofsymbols has been updated,
* but the table itself may not exist. Takes a date range, including both start and end days.
*
* <p>Yahoo Finance returns an error message rather than an empty CSV if the start and end dates
* are today. The caller is responsible for checking that the call range is acceptable.
*
* @param symbol - symbol to update
* @param startDate - beginning of range to add to
* @param endDate - end of range to add to
*/
static void updateSymbol(String symbol, Date startDate, Date endDate) throws Exception {
System.out.println("Trying to update:" + symbol);
Connection conn = initialize();
Statement stat = conn.createStatement();
URL data = YahooCsvDownloadUrl(symbol, startDate, endDate);
BufferedReader in = null;
try {
in = new BufferedReader(new InputStreamReader(data.openStream()));
} catch (java.io.FileNotFoundException e) {
System.out.println("Symbol not found:" + symbol);
e.printStackTrace();
return;
}
CsvReader reader = new CsvReader(in);
reader.readHeaders();
String[] headers = reader.getHeaders();
stat.executeUpdate("CREATE TABLE IF NOT EXISTS " + symbol + " (" + getColNames(headers) + ");");
String statement =
"INSERT INTO "
+ symbol
+ " ("
+ getColNames(headers)
+ ") VALUES ("
+ getQueryQuestionMarks(headers)
+ ");";
PreparedStatement prep = conn.prepareStatement(statement);
while (reader.readRecord()) {
for (int j = 0; j < headers.length; j++) {
String str = reader.get(headers[j]);
prep.setString(j + 1, str);
}
// TODO: salim, what's the point of these calls?
prep.addBatch();
conn.setAutoCommit(false);
prep.executeBatch();
conn.setAutoCommit(true);
}
reader.close();
in.close();
conn.close();
}
/**
* Get the database as a map from symbols (e.g., "XOM") to a list of QuoteRecords. These records
* are sorted in ascending order by date.
*
* <p>The procedure does not guarantee duplicate quote records have been removed, but it does draw
* attention to their existence. (Such records can enter the database when performing
* tests/updates.)
*
* @see QuoteRecord, CheckSymbolToQuotesMap, SortSymbolToQuotesMap
*/
public static Map<String, List<QuoteRecord>> GetDatabaseAsMap() {
Map<String, List<QuoteRecord>> result = null;
List<String> symbols = new ArrayList<String>();
Connection conn = null;
try {
conn = initialize();
Statement stat = conn.createStatement();
ResultSet rs = stat.executeQuery("select * from listofsymbols;");
while (rs.next()) {
symbols.add(rs.getString("symbolname"));
}
rs.close();
result = dumpSymbols(symbols);
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
SortSymbolToQuotesMap(result);
// CheckSymbolToQuotesMap( result );
return result;
}
/**
* Creates the database, deleting data that existed previously.
*
* <p>Won't actually remove symbol's tables if they aren't in the set SYMBOLS.
*
* @param pauseDuration - this is how long we sleep (in ms) for before hitting yahoo again
* @see SYMBOLS
*/
public static void createDatabase(String[] symbolArray, int pauseDuration) {
try {
Connection conn = initialize();
Statement stat = conn.createStatement();
try {
stat.executeUpdate("DROP TABLE IF EXISTS " + "listofsymbols" + ";");
stat.executeUpdate(
"CREATE TABLE IF NOT EXISTS " + "listofsymbols" + " (" + "'symbolname'" + ");");
} catch (Exception e) {
System.out.println("failed stat.executeUpdate");
System.out.println(e.getMessage());
}
for (int i = 0; i < symbolArray.length; i++) {
addSymbol(symbolArray[i]);
if (pauseDuration != 0) {
Thread.sleep(pauseDuration);
}
}
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
