Beispiel #1
0
 /**
  * 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;
 }
Beispiel #2
0
  /**
   * 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);
    }
  }
Beispiel #3
0
 /**
  * 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;
 }
Beispiel #4
0
  /**
   * 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;
  }
Beispiel #5
0
 /**
  * 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();
 }
Beispiel #6
0
  /**
   * 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();
  }
Beispiel #7
0
  /**
   * 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;
  }
Beispiel #8
0
  /**
   * 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();
    }
  }