/** * 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(); } }