/**
* Main method - used for testing
*
* @param args later use for seeing what calls to make using reflection
*/
public static void main(String[] args) {
if ((args.length != 1) || !(args[0].equals("create") || args[0].equals("update"))) {
System.out.println("Bad argument, must be one of create or update");
}
try {
if (args[0].equals("create")) {
createDatabase(SYMBOLS);
} else if (args[0].equals("update")) {
makeDatabaseUpToDate();
}
// createDatabase(SP500.SYMBOLS, 1500);
// updateListOfSymbols(SP500.SYMBOLS);
// updateListOfSymbols(SYMBOLS);
// System.out.println("Starting GetDatabaseAsMap:" + new Date());
// Map<String,List<QuoteRecord>> quotes = GetDatabaseAsMap();
// System.out.println("Starting SerializeSymbolToQuotes:" + new Date());
// SerializeSymbolToQuotes( quotes, "/tmp/quotes.ser" );
// System.out.println("Starting DeserializeSymbolToQuotes:" + new Date());
// quotes = DeserializeSymbolToQuotes( "/tmp/quotes.ser" );
// System.out.println("Done DeserializeSymbolToQuotes:" + new Date());
// CheckSymbolToQuotesMap(Global.symbolToQuotesMap);
// addSymbol("MSFT");
// deleteSymbol("YHOO");
// addSymbol("YHOO");
// deleteDateForSymbol("YHOO","2010-04-12" );
// makeUpToDate("YHOO");
// makeUpToDate("INTC");
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* Serialize the map-view of the database
*
* <p>
*
* @param symbolToQuotesMap - the map to be serialized
* @param - the name of the file we'll be writing to
* @see DeserializeSymbolToQuotes
*/
public static void SerializeSymbolToQuotes(
Map<String, List<QuoteRecord>> symbolToQuotesMap, String filename) {
try {
// ObjectOutputStream out = new ObjectOutputStream( new StringWriter( ) );
ObjectOutputStream out = new ObjectOutputStream(new FileOutputStream(filename));
out.writeObject(symbolToQuotesMap);
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* Compute a date object from a string
*
* @param dateString - assumed to be of the form "2010-12-31"
* @return correspoding java.util.Date object
*/
private static Date DateFromString(String dateString) {
Date result = null;
try {
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
result = formatter.parse(dateString);
} catch (Exception e) {
System.out.println("Parse error:" + dateString);
e.printStackTrace();
}
return result;
}
/**
* Deserialize the map-view of the database
*
* <p>
*
* @see SerializeSymbolToQuotes
*/
public static Map<String, List<QuoteRecord>> DeserializeSymbolToQuotes(String filename) {
Map<String, List<QuoteRecord>> symbolToQuotesMap = null;
try {
// ObjectOutputStream out = new ObjectOutputStream( new StringWriter( ) );
ObjectInputStream in = new ObjectInputStream(new FileInputStream(filename));
symbolToQuotesMap = (Map<String, List<QuoteRecord>>) in.readObject();
in.close();
} catch (Exception e) {
e.printStackTrace();
}
return symbolToQuotesMap;
}
/**
* Returns URL for data starting from the default starting day to today, just wraps
* YahooCsvDownloadUrl(symbol, defaultStartDay, today);
*/
public static URL YahooCsvDownloadUrl(String symbol) {
Date today = new Date();
Date startDate = null;
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
try {
startDate = formatter.parse(DefaultStartDate);
} catch (Exception e) {
System.out.println("Bad formatting for DefaultStartDate:" + DefaultStartDate);
e.printStackTrace();
}
return YahooCsvDownloadUrl(symbol, startDate, today);
}
/**
* 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();
}
/**
* Returns a URL that can be used for downloading historical stock data in CSV format from Yahoo
* Finance.
*
* @param symbol A quote symbol, e.g., "XOM"
* @param startDate, the first date in the range, inclusive
* @param endDate, the last date in the range, inclusive
* @return a URL that's backed up by a CSV
*/
public static URL YahooCsvDownloadUrl(String symbol, Date startDate, Date endDate) {
Calendar startCal = new GregorianCalendar();
startCal.setTime(startDate);
Calendar endCal = new GregorianCalendar();
endCal.setTime(endDate);
int startMonthInt = startCal.get(Calendar.MONTH);
int startDateInt = startCal.get(Calendar.DATE);
int startYearInt = startCal.get(Calendar.YEAR);
int endMonthInt = endCal.get(Calendar.MONTH);
int endDateInt = endCal.get(Calendar.DATE);
int endYearInt = endCal.get(Calendar.YEAR);
// to get range of dates to update for:
// from http://www.etraderzone.com/free-scripts/47-historical-quotes-yahoo.html
String url =
"http://ichart.finance.yahoo.com/table.csv?s="
+ symbol
+ "&a="
+ startMonthInt
+ "&b="
+ startDateInt
+ "&c="
+ startYearInt
+ "&d="
+ endMonthInt
+ "&e="
+ endDateInt
+ "&f="
+ endYearInt;
URL csvUrl = null;
try {
csvUrl = new URL(url);
} catch (Exception e) {
e.printStackTrace();
System.out.println("URL construction failure: " + symbol);
}
return csvUrl;
}
/**
* 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();
}
}
/**
* Returns a Connection object for the database called "stocks.db" that lives in the current
* directory.
*
* <p>
*
* @return A good connection to "stock.db"
*/
public static Connection initialize() {
Connection conn = null;
try {
Class.forName("org.sqlite.JDBC");
} catch (Exception e) {
System.out.println("failed class.forname");
System.out.println(e.getStackTrace());
}
try {
conn = DriverManager.getConnection("jdbc:sqlite:stocks.db");
} catch (Exception e) {
System.out.println("failed conn");
File f = new File("stocks.db");
if (!f.exists()) {
System.out.println("stocks.db doesnt exist");
System.out.println(
"create the database from scratch, see the TSS webapge for instructions");
} else {
System.out.println("stocks.db does exist, but conn failed, corrupt stocks.db?");
System.out.println(e.getStackTrace());
}
}
return conn;
}
