diff options
Diffstat (limited to 'src/main/java/edu/brown/cs/student/term/profit/ProfitCalculation.java')
| -rw-r--r-- | src/main/java/edu/brown/cs/student/term/profit/ProfitCalculation.java | 366 |
1 files changed, 276 insertions, 90 deletions
diff --git a/src/main/java/edu/brown/cs/student/term/profit/ProfitCalculation.java b/src/main/java/edu/brown/cs/student/term/profit/ProfitCalculation.java index 15f31cc..4b19899 100644 --- a/src/main/java/edu/brown/cs/student/term/profit/ProfitCalculation.java +++ b/src/main/java/edu/brown/cs/student/term/profit/ProfitCalculation.java @@ -19,6 +19,11 @@ import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; +import java.text.SimpleDateFormat; +import java.util.LinkedList; +import java.util.HashMap; +import java.util.List; +import java.util.Map; import java.util.*; public class ProfitCalculation { @@ -71,6 +76,7 @@ public class ProfitCalculation { tablesFilled = false; } + /** * This method fills the maps of sell and buy orders with lists of oldest - new trades. */ @@ -78,25 +84,35 @@ public class ProfitCalculation { private String validateTicker(String ticker) { //this is cleaning some improperly formatted tickers ticker = ticker.replaceAll("[^a-zA-Z0-9]", "").toUpperCase(); - if(ticker.contains("[0-9]") || - ticker.length() > 5 || - ticker.length() < 2 || - ticker.contains("NONE")) { + if (ticker.contains("[0-9]") || + ticker.length() > 5 || + ticker.length() < 2 || + ticker.contains("NONE")) { return ""; } return ticker; } - private void organizeOrders() { + private void organizeOrders(Integer id) { //get a list of trades for a person to consider try { PreparedStatement prep; - prep = - conn.prepareStatement("SELECT * FROM \'trades\' WHERE holder_name= ? " - + " AND trade_timestamp BETWEEN ? AND ?" - + "order by trade_timestamp asc;"); - prep.setString(1, this.person); + if (id == -1) { + //search by name + prep = + conn.prepareStatement("SELECT * FROM \'trades\' WHERE holder_name= ? " + + " AND trade_timestamp BETWEEN ? AND ? " + + "order by trade_timestamp asc;"); + prep.setString(1, this.person); + } else { + //search by id + prep = + conn.prepareStatement("SELECT * FROM \'trades\' WHERE holder_id = ? " + + " AND trade_timestamp BETWEEN ? AND ? " + + "order by trade_timestamp asc;"); + prep.setInt(1, id); + } prep.setDate(2, startTime); prep.setDate(3, endTime); ResultSet rs = prep.executeQuery(); @@ -104,7 +120,7 @@ public class ProfitCalculation { while (rs.next()) { String ticker = rs.getString("stock_name"); ticker = validateTicker(ticker); - if(ticker.equals("")){ + if (ticker.equals("")) { continue; } int shares = rs.getInt("number_of_shares"); @@ -125,13 +141,12 @@ public class ProfitCalculation { } } else { //ignore sell orders for which we do not have buys for - if (buyHistoryMap.containsKey(ticker)) { - if (sellHistoryMap.containsKey(ticker)) { - sellHistoryMap.get(ticker).addLast(order); - } else { - sellHistoryMap.put(ticker, oneElement); - } + if (sellHistoryMap.containsKey(ticker)) { + sellHistoryMap.get(ticker).addLast(order); + } else { + sellHistoryMap.put(ticker, oneElement); } + } } @@ -150,39 +165,41 @@ public class ProfitCalculation { LinkedList<OrderTuple> sells = sellHistoryMap.get(ticker); LinkedList<OrderTuple> buys = buyHistoryMap.get(ticker); double realizedGain = 0; + if (sells != null && buys != null) { + //process each sell order (unless all buy orders are "drained" + for (OrderTuple sell : sells) { + //stop if buys are empty, stop if buy happened after sell + if (buys.isEmpty()) { + break; + } - //process each sell order (unless all buy orders are "drained" - for (OrderTuple sell : sells) { - //stop if buys are empty, stop if buy happened after sell - if (buys.isEmpty()) { - break; - } - - int sharesToSell = sell.getShares(); - - //sell off through list of buys - while (sharesToSell > 0 && !buys.isEmpty()) { - //dont sell from buys which didn't exist at the time. - if (sell.getDate().after(buys.getFirst().getDate())) { - OrderTuple buyBundle = buys.removeFirst(); - int sharesAtBundlePrice; - //the buy has more shares than we want to sell - if (buyBundle.getShares() > sharesToSell) { - sharesAtBundlePrice = sharesToSell; - sharesToSell = 0; - //add back the holdings that were not sold - buyBundle.setShares(buyBundle.getShares() - sharesAtBundlePrice); - buys.addFirst(buyBundle); + int sharesToSell = sell.getShares(); + + //sell off through list of buys + while (sharesToSell > 0 && !buys.isEmpty()) { + //dont sell from buys which didn't exist at the time. + if (sell.getDate().after(buys.getFirst().getDate()) + || sell.getDate().equals(buys.getFirst().getDate())) { + OrderTuple buyBundle = buys.removeFirst(); + int sharesAtBundlePrice; + //the buy has more shares than we want to sell + if (buyBundle.getShares() > sharesToSell) { + sharesAtBundlePrice = sharesToSell; + sharesToSell = 0; + //add back the holdings that were not sold + buyBundle.setShares(buyBundle.getShares() - sharesAtBundlePrice); + buys.addFirst(buyBundle); + } else { + sharesToSell -= buyBundle.getShares(); + sharesAtBundlePrice = buyBundle.getShares(); + } + realizedGain += sharesAtBundlePrice * (sell.getCost() - buyBundle.getCost()); } else { - sharesToSell -= buyBundle.getShares(); - sharesAtBundlePrice = buyBundle.getShares(); + break; } - realizedGain += sharesAtBundlePrice * (sell.getCost() - buyBundle.getCost()); - } else { - break; - } + } } } @@ -242,12 +259,16 @@ public class ProfitCalculation { if (currentStockPrices.containsKey(ticker)) { return currentStockPrices.get(ticker); } else { - String PRICE_URL = BASE_URL + "/last/stocks/" + ticker; - System.out.println("LOG: Making call to " + PRICE_URL + " in " + getClass()); + SimpleDateFormat localDateFormat = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSSXXX"); + String url = "https://data.alpaca.markets/v1/bars/" + + "day?" + + "symbols=" + ticker + + "&start=" + localDateFormat.format(startTime) + + "&end=" + localDateFormat.format(endTime); HttpClient client = HttpClient.newHttpClient(); HttpRequest request = HttpRequest.newBuilder() - .uri(URI.create(PRICE_URL)).setHeader("APCA-API-KEY-ID", API_KEY) + .uri(URI.create(url)).setHeader("APCA-API-KEY-ID", API_KEY) .setHeader("APCA-API-SECRET-KEY", SECRET_KEY) .build(); @@ -255,18 +276,15 @@ public class ProfitCalculation { try { response = client.send(request, HttpResponse.BodyHandlers.ofString()); - } catch (IOException e) { - e.printStackTrace(); - } catch (InterruptedException e) { - e.printStackTrace(); + } catch (Exception e) { + System.out.println("ERROR: error getting price for profit calculation"); } - - JSONObject object = new JSONObject(response.body()); + JSONArray object = new JSONObject(response.body()).getJSONArray(ticker); try { - double price = object.getJSONObject("last").getDouble("price"); - currentStockPrices.put(ticker, price); - return price; + double endPrice = object.getJSONObject(object.length() - 1).getDouble("c"); + currentStockPrices.put(ticker, endPrice); + return endPrice; } catch (JSONException e) { currentStockPrices.put(ticker, -1.0); return -1.0; @@ -276,29 +294,37 @@ public class ProfitCalculation { } - public double calculateGains() { + public double calculateGainsSingle(Integer id) { + if (!tablesFilled) { - organizeOrders(); + organizeOrders(id); getRealizedGains(); getUnrealizedGains(); tablesFilled = true; } - double realizedGains = 0; - double unrealizedGains = 0; + + + double gains = 0; for (double value : realizedGainsMap.values()) { - realizedGains += value; + gains += value; } - for (double value : unrealizedGainsMap.values()) { - unrealizedGains += value; + for (double value: unrealizedGainsMap.values()) { + gains += value; } - return unrealizedGains + realizedGains; + + return gains; } - public List<StockHolding> getHoldingsList() { + public List<StockHolding> getHoldingsList(Integer id) { + if (conn == null) { + System.out.println("ERROR: No database connection"); + return new LinkedList<>(); + } + if (!tablesFilled) { - organizeOrders(); + organizeOrders(id); getRealizedGains(); getUnrealizedGains(); tablesFilled = true; @@ -329,11 +355,12 @@ public class ProfitCalculation { * return percent change in SPY (SP 500) over the time period. */ public double compareToSP500() { + SimpleDateFormat localDateFormat = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSSXXX"); String url = "https://data.alpaca.markets/v1/bars/" + "day?" + "symbols=SPY" - + "&start=" + startTime - + "&end=" + endTime; + + "&start=" + localDateFormat.format(startTime) + + "&end=" + localDateFormat.format(endTime); HttpClient client = HttpClient.newHttpClient(); HttpRequest request = HttpRequest.newBuilder() @@ -369,29 +396,199 @@ public class ProfitCalculation { */ public Map<Integer, Double> getProfitMap() { Map<Integer, Double> profitMap = new HashMap<>(); + if (conn == null) { + System.out.println("ERROR: no database connection"); + return profitMap; + } try { PreparedStatement prep; + long START = System.currentTimeMillis(); prep = - conn.prepareStatement("SELECT * from trades group by holder_name;"); + conn.prepareStatement( + "SELECT * From trades GROUP BY holder_id having max(is_buy) = 1;"); ResultSet rs = prep.executeQuery(); + + long QUERY = System.currentTimeMillis(); + //System.out.println((QUERY - START) + " query time"); + + //set of all people who have made both buy and sell orders + Set<Integer> people = new HashSet<>(); + while (rs.next()) { - int id = rs.getInt("holder_id"); - this.person = rs.getString("holder_name"); - resetClass(); - double gain = this.calculateGains(); - if (moneyInput == 0) { - profitMap.put(id, 0.0); - } else { - profitMap.put(id, gain / moneyInput); - } +// int id = rs.getInt("holder_id"); +// this.person = rs.getString("holder_name"); +// resetClass(); +// +// +// +// double gain = this.calculateGains(); +// if (moneyInput == 0) { +// profitMap.put(id, 0.0); +// } else { +// profitMap.put(id, gain / moneyInput); +// } + people.add(rs.getInt("holder_id")); } + + profitMap = calculateGains(people); + + long LOOP = System.currentTimeMillis(); + //System.out.println((LOOP - QUERY) + " loop"); + } catch (SQLException throwables) { System.out.println("ERROR: SQl error in profit calculation"); } return profitMap; } + private Map<Integer, Double> calculateGains(Set<Integer> people) { + Map<Integer, Double> gainsMap = new HashMap<>(); + + //map of stock to list of buy orders, first element in list is oldest + Map<Integer, Map<String, LinkedList<OrderTuple>>> sellMap = new HashMap<>(); + //map of stock to list of buy orders, first element in list is oldest + Map<Integer, Map<String, LinkedList<OrderTuple>>> buyMap = new HashMap<>(); + //money input + Map<Integer, Double> moneyInMap = new HashMap<>(); + + + try { + PreparedStatement prep; + prep = + conn.prepareStatement("SELECT * FROM \'trades\'" + + " WHERE NOT number_of_shares = 0 AND trade_timestamp BETWEEN ? AND ? " + + "order by trade_timestamp asc;"); + prep.setDate(1, startTime); + prep.setDate(2, endTime); + ResultSet rs = prep.executeQuery(); + while (rs.next()) { + if (people.contains(rs.getInt("holder_id"))) { + String ticker = rs.getString("stock_name"); + ticker = validateTicker(ticker); + if (ticker.equals("")) { + continue; + } + int shares = rs.getInt("number_of_shares"); + double price = rs.getDouble("share_price"); + int holder_id = rs.getInt("holder_id"); + if (!buyMap.containsKey(holder_id)) { + buyMap.put(holder_id, new HashMap<>()); + } + if (!sellMap.containsKey(holder_id)) { + sellMap.put(holder_id, new HashMap<>()); + } + + + OrderTuple order = new OrderTuple(shares, price, rs.getDate("trade_timestamp")); + + //one element list for first time ticker is seen. + LinkedList<OrderTuple> oneElement = new LinkedList<OrderTuple>(); + oneElement.addLast(order); + + //for buy orders, build up buy history + if (rs.getInt("is_buy") != 0) { + + if (moneyInMap.containsKey(holder_id)) { + moneyInMap.put(holder_id, moneyInMap.get(holder_id) + shares * price); + } else { + moneyInMap.put(holder_id, shares * price); + } + + + if (buyMap.get(holder_id).containsKey(ticker)) { + buyMap.get(holder_id).get(ticker).addLast(order); + } else { + buyMap.get(holder_id).put(ticker, oneElement); + } + } else { + //ignore sell orders for which we do not have buys for + if (sellMap.get(holder_id).containsKey(ticker)) { + sellMap.get(holder_id).get(ticker).addLast(order); + } else { + sellMap.get(holder_id).put(ticker, oneElement); + } + + } + } + } + } catch (SQLException e) { + System.out.println("ERROR: sql error getting trades"); + } + + + //part 2 doing math... + for (Integer person : people) { + this.buyHistoryMap = buyMap.get(person); + this.sellHistoryMap = sellMap.get(person); + if (sellHistoryMap == null) { + continue; + } + + for (String ticker : sellHistoryMap.keySet()) { + //use FIFO selling + LinkedList<OrderTuple> sells = sellHistoryMap.get(ticker); + LinkedList<OrderTuple> buys = buyHistoryMap.get(ticker); + double realizedGain = 0; + if (sells != null && buys != null) { + //process each sell order (unless all buy orders are "drained" + for (OrderTuple sell : sells) { + //stop if buys are empty, stop if buy happened after sell + if (buys.isEmpty()) { + break; + } + + int sharesToSell = sell.getShares(); + + //sell off through list of buys + while (sharesToSell > 0 && !buys.isEmpty()) { + //dont sell from buys which didn't exist at the time. + if (sell.getDate().after(buys.getFirst().getDate()) + || sell.getDate().equals(buys.getFirst().getDate())) { + OrderTuple buyBundle = buys.removeFirst(); + int sharesAtBundlePrice; + //the buy has more shares than we want to sell + if (buyBundle.getShares() > sharesToSell) { + sharesAtBundlePrice = sharesToSell; + sharesToSell = 0; + //add back the holdings that were not sold + buyBundle.setShares(buyBundle.getShares() - sharesAtBundlePrice); + buys.addFirst(buyBundle); + } else { + sharesToSell -= buyBundle.getShares(); + sharesAtBundlePrice = buyBundle.getShares(); + } + realizedGain += sharesAtBundlePrice * (sell.getCost() - buyBundle.getCost()); + } else { + break; + } + + + } + } + } + + if (gainsMap.containsKey(person)) { + gainsMap.put(person, gainsMap.get(person) + realizedGain); + } else { + gainsMap.put(person, realizedGain); + } + + } + + //percent gain + if (gainsMap.containsKey(person) && moneyInMap.containsKey(person)) { + gainsMap.put(person, gainsMap.get(person) / moneyInMap.get(person)); + } else { + gainsMap.put(person, 0.0); + } + + + } + + return gainsMap; + } + public double getMoneyInput() { return this.moneyInput; } @@ -406,15 +603,4 @@ public class ProfitCalculation { tablesFilled = false; } - public void setConnection(String filename) throws SQLException, ClassNotFoundException { - - // Initialize the database connection, turn foreign keys on - Class.forName("org.sqlite.JDBC"); - String urlToDB = "jdbc:sqlite:" + filename; - conn = DriverManager.getConnection(urlToDB); - - Statement stat = conn.createStatement(); - stat.executeUpdate("PRAGMA foreign_keys=ON;"); - } - } |