From 43d42b0b88927a552c09bc32145f7e7ef2222652 Mon Sep 17 00:00:00 2001 From: clarkohw Date: Mon, 19 Apr 2021 18:49:44 -0400 Subject: exclude only sell people --- src/main/java/edu/brown/cs/student/term/Main.java | 4 ++++ .../brown/cs/student/term/hub/SuspicionRanker.java | 4 ---- .../cs/student/term/profit/ProfitCalculation.java | 26 +++++++++++++++++----- .../brown/cs/student/ProfitCalculationTest.java | 16 +++++++++++++ 4 files changed, 40 insertions(+), 10 deletions(-) diff --git a/src/main/java/edu/brown/cs/student/term/Main.java b/src/main/java/edu/brown/cs/student/term/Main.java index df95ec0..6a20de3 100644 --- a/src/main/java/edu/brown/cs/student/term/Main.java +++ b/src/main/java/edu/brown/cs/student/term/Main.java @@ -151,6 +151,7 @@ public final class Main { public Object handle(Request request, Response response) throws Exception { //String str = request.body(); //xmlLinks = new JSONObject(str); //this is all the filedAt times and xml files + long startTime = System.currentTimeMillis(); try { System.err.println("LOG: Call to /data from frontend"); DatabaseQuerier db = SetupCommand.getDq(); @@ -166,6 +167,9 @@ public final class Main { List suspiciousHolders = ranker.getSuspicionScoreList(start, end); System.err.println("LOG: Making map " + getClass()); Map variables = ImmutableMap.of("holders", suspiciousHolders); + + System.out.println("DATA ENDPOINT " + ((System.currentTimeMillis() - startTime) / 1000) + "seconds"); + return GSON.toJson(variables); } catch (Exception e) { System.out.println("Error retrieving the suspicion ranks for GUI"); diff --git a/src/main/java/edu/brown/cs/student/term/hub/SuspicionRanker.java b/src/main/java/edu/brown/cs/student/term/hub/SuspicionRanker.java index 3283f5c..c77eb9f 100644 --- a/src/main/java/edu/brown/cs/student/term/hub/SuspicionRanker.java +++ b/src/main/java/edu/brown/cs/student/term/hub/SuspicionRanker.java @@ -52,7 +52,6 @@ public class SuspicionRanker { HubSearch hub = new HubSearch(lm); Map holderToHubScore = hub.runHubSearch(start, end); - /* ProfitCalculation pc = new ProfitCalculation(DatabaseQuerier.getConn(), "", new Date(start.toEpochMilli()), @@ -68,7 +67,6 @@ public class SuspicionRanker { double profitMax = getMaxOfMap(profitMap); /*if all of our values are negative, we need to flip sides so that the * biggest loser doesn't end up being the most suspicious person*/ - /* if(profitMax <= 0) { profitMax = Math.abs(getMinOfMap(profitMap)); } @@ -76,11 +74,9 @@ public class SuspicionRanker { /*if both the min we found and max we found are 0, then we have the special case where all the values are 0, in which case we need to avoid dividing by 0*/ - /* if(profitMax == 0){ profitMax = 1; } - */ double hubMax = getMaxOfMap(holderToHubScore); 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 0ef87c3..ab76003 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 @@ -83,10 +83,10 @@ 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 ""; } @@ -109,7 +109,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"); @@ -284,11 +284,14 @@ public class ProfitCalculation { } public double calculateGains() { + if (!tablesFilled) { organizeOrders(); getRealizedGains(); tablesFilled = true; } + + double realizedGains = 0; for (double value : realizedGainsMap.values()) { @@ -383,13 +386,22 @@ public class ProfitCalculation { } 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_name having max(is_buy) = 1;"); ResultSet rs = prep.executeQuery(); + + long QUERY = System.currentTimeMillis(); + System.out.println((QUERY - START) + " query time"); + 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); @@ -398,6 +410,8 @@ public class ProfitCalculation { } } + + } catch (SQLException throwables) { System.out.println("ERROR: SQl error in profit calculation"); } diff --git a/src/test/java/edu/brown/cs/student/ProfitCalculationTest.java b/src/test/java/edu/brown/cs/student/ProfitCalculationTest.java index 1291245..4ca1780 100644 --- a/src/test/java/edu/brown/cs/student/ProfitCalculationTest.java +++ b/src/test/java/edu/brown/cs/student/ProfitCalculationTest.java @@ -140,5 +140,21 @@ public class ProfitCalculationTest { assertEquals(profitCalculation.getHoldingsList().get(0).getUnrealizedGain(), 0, .01); } + @Test + public void optimizationTest() { + try { + db = new DatabaseQuerier("data/trades.sqlite3"); + } catch (Exception e) { + System.out.println("DBQuerier Test, couldn't connect to db???"); + } + + ProfitCalculation profitCalculation = new ProfitCalculation(DatabaseQuerier.getConn(), "invalidTicker", new Date(1618223864000l), + new Date(1618483064000l)); + + long startTime = System.currentTimeMillis(); + profitCalculation.getProfitMap(); + System.out.println("DATA ENDPOINT " + ((System.currentTimeMillis() - startTime) / 1000) + "seconds"); + } + } \ No newline at end of file -- cgit v1.2.3-70-g09d2 From 905460902c06e9df370c91109554ecbbeb730ac7 Mon Sep 17 00:00:00 2001 From: clarkohw Date: Mon, 19 Apr 2021 20:36:33 -0400 Subject: heavily optimized profit calculations --- data/profit_testing.sqlite3 | Bin 8192 -> 8192 bytes src/main/java/edu/brown/cs/student/term/Main.java | 6 +- .../brown/cs/student/term/hub/SuspicionRanker.java | 13 +- .../cs/student/term/profit/ProfitCalculation.java | 185 +++++++++++++++++++-- .../brown/cs/student/ProfitCalculationTest.java | 29 +--- 5 files changed, 187 insertions(+), 46 deletions(-) diff --git a/data/profit_testing.sqlite3 b/data/profit_testing.sqlite3 index c3b32ee..33dd2b8 100644 Binary files a/data/profit_testing.sqlite3 and b/data/profit_testing.sqlite3 differ diff --git a/src/main/java/edu/brown/cs/student/term/Main.java b/src/main/java/edu/brown/cs/student/term/Main.java index 6a20de3..dd304c5 100644 --- a/src/main/java/edu/brown/cs/student/term/Main.java +++ b/src/main/java/edu/brown/cs/student/term/Main.java @@ -151,7 +151,6 @@ public final class Main { public Object handle(Request request, Response response) throws Exception { //String str = request.body(); //xmlLinks = new JSONObject(str); //this is all the filedAt times and xml files - long startTime = System.currentTimeMillis(); try { System.err.println("LOG: Call to /data from frontend"); DatabaseQuerier db = SetupCommand.getDq(); @@ -167,9 +166,6 @@ public final class Main { List suspiciousHolders = ranker.getSuspicionScoreList(start, end); System.err.println("LOG: Making map " + getClass()); Map variables = ImmutableMap.of("holders", suspiciousHolders); - - System.out.println("DATA ENDPOINT " + ((System.currentTimeMillis() - startTime) / 1000) + "seconds"); - return GSON.toJson(variables); } catch (Exception e) { System.out.println("Error retrieving the suspicion ranks for GUI"); @@ -189,7 +185,7 @@ public final class Main { ProfitCalculation profit = new ProfitCalculation(DatabaseQuerier.getConn(), person, startPeriod, endPeriod); List holdings = profit.getHoldingsList(); - double gains = profit.calculateGains(); + double gains = profit.calculateGainsSingle(); double sp500PercentGain = profit.compareToSP500(); Map res = new HashMap<>(); diff --git a/src/main/java/edu/brown/cs/student/term/hub/SuspicionRanker.java b/src/main/java/edu/brown/cs/student/term/hub/SuspicionRanker.java index c77eb9f..9f17569 100644 --- a/src/main/java/edu/brown/cs/student/term/hub/SuspicionRanker.java +++ b/src/main/java/edu/brown/cs/student/term/hub/SuspicionRanker.java @@ -16,8 +16,9 @@ public class SuspicionRanker { } private > V getMaxOfMap(Map map) { - Map.Entry maxEntry = Collections.max(map.entrySet(), Map.Entry.comparingByValue()); - return maxEntry.getValue(); + //Map.Entry maxEntry = Collections.max(map.entrySet(), Map.Entry.comparingByValue()); + Collection values = map.values(); + return Collections.max(map.values()); } private > V getMinOfMap(Map map) { @@ -82,10 +83,14 @@ public class SuspicionRanker { double hubMax = getMaxOfMap(holderToHubScore); for (Holder guy : holderToHubScore.keySet()) { - //double normalizedProfitScore = profitMap.get(guy.getId()) / profitMax; + double normalizedProfitScore = 0; + if (profitMap.containsKey(guy.getId())) { + normalizedProfitScore = profitMap.get(guy.getId()) / profitMax; + } + double normalizedHubScore = holderToHubScore.get(guy) / hubMax; - double suspicionScore = normalizedHubScore; //* 0.6 + normalizedProfitScore * 0.4; + double suspicionScore = normalizedHubScore * 0.6 + normalizedProfitScore * 0.4; guy.setSuspicionScore(suspicionScore); orderedSuspicion.add(guy); } 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 ab76003..d0df8a8 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 @@ -99,7 +99,7 @@ public class ProfitCalculation { PreparedStatement prep; prep = conn.prepareStatement("SELECT * FROM \'trades\' WHERE holder_name= ? " - + " AND trade_timestamp BETWEEN ? AND ?" + + " AND trade_timestamp BETWEEN ? AND ? " + "order by trade_timestamp asc;"); prep.setString(1, this.person); prep.setDate(2, startTime); @@ -283,7 +283,7 @@ public class ProfitCalculation { } - public double calculateGains() { + public double calculateGainsSingle() { if (!tablesFilled) { organizeOrders(); @@ -389,33 +389,188 @@ public class ProfitCalculation { long START = System.currentTimeMillis(); prep = conn.prepareStatement( - "SELECT * From trades GROUP BY holder_name having max(is_buy) = 1;"); + "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"); + //System.out.println((QUERY - START) + " query time"); + //set of all people who have made both buy and sell orders + Set 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); +// } + 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 calculateGains(Set people) { + Map gainsMap = new HashMap<>(); + + //map of stock to list of buy orders, first element in list is oldest + Map>> sellMap = new HashMap<>(); + //map of stock to list of buy orders, first element in list is oldest + Map>> buyMap = new HashMap<>(); + //money input + Map 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()) { - int id = rs.getInt("holder_id"); - this.person = rs.getString("holder_name"); - resetClass(); + 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 oneElement = new LinkedList(); + 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 sells = sellHistoryMap.get(ticker); + LinkedList 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; + } + } + } + } - double gain = this.calculateGains(); - if (moneyInput == 0) { - profitMap.put(id, 0.0); + if (gainsMap.containsKey(person)) { + gainsMap.put(person, gainsMap.get(person) + realizedGain); } else { - profitMap.put(id, gain / moneyInput); + gainsMap.put(person, realizedGain); } } - - } catch (SQLException throwables) { - System.out.println("ERROR: SQl error in profit calculation"); + //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 profitMap; + + return gainsMap; } public double getMoneyInput() { diff --git a/src/test/java/edu/brown/cs/student/ProfitCalculationTest.java b/src/test/java/edu/brown/cs/student/ProfitCalculationTest.java index 4ca1780..68f53fc 100644 --- a/src/test/java/edu/brown/cs/student/ProfitCalculationTest.java +++ b/src/test/java/edu/brown/cs/student/ProfitCalculationTest.java @@ -67,7 +67,7 @@ public class ProfitCalculationTest { new Date(1618698807000l)); trade = profitCalculation.getHoldingsList(); assertTrue(trade.isEmpty()); - assertEquals(profitCalculation.calculateGains(), 0, 0.001); + assertEquals(profitCalculation.calculateGainsSingle(), 0, 0.001); tearDown(); } @@ -80,16 +80,18 @@ public class ProfitCalculationTest { new ProfitCalculation(DatabaseQuerier.getConn(), "concurrentBS", new Date(1518010558000l), new Date(1715629591000l)); - assertEquals(profitCalculation.getProfitMap().get(100), 1, .01); + Map map = profitCalculation.getProfitMap(); + + assertEquals(map.get(100), 1, .01); //buys at multiple prices profitCalculation = new ProfitCalculation(DatabaseQuerier.getConn(), "mulitpleBuyPrices", new Date(1518010558000l), new Date(1715629591000l)); - assertEquals(profitCalculation.getProfitMap().get(101), 1, .01); - assertEquals(profitCalculation.getMoneyInput(), 3750, .01); + assertEquals(profitCalculation.getHoldingsList().get(0).getRealizedGain(), 3750, 0.01); + assertEquals(profitCalculation.getMoneyInput(), 3750, .01); //left over holdings profitCalculation = @@ -136,25 +138,8 @@ public class ProfitCalculationTest { profitCalculation = new ProfitCalculation(DatabaseQuerier.getConn(), "invalidTicker", new Date(1518010558000l), new Date(1618698807000l)); - assertEquals(profitCalculation.getHoldingsList().get(0).getRealizedGain(), 0, .01); - assertEquals(profitCalculation.getHoldingsList().get(0).getUnrealizedGain(), 0, .01); - } + assertTrue(profitCalculation.getHoldingsList().isEmpty()); - @Test - public void optimizationTest() { - try { - db = new DatabaseQuerier("data/trades.sqlite3"); - } catch (Exception e) { - System.out.println("DBQuerier Test, couldn't connect to db???"); - } - - ProfitCalculation profitCalculation = new ProfitCalculation(DatabaseQuerier.getConn(), "invalidTicker", new Date(1618223864000l), - new Date(1618483064000l)); - - long startTime = System.currentTimeMillis(); - profitCalculation.getProfitMap(); - System.out.println("DATA ENDPOINT " + ((System.currentTimeMillis() - startTime) / 1000) + "seconds"); } - } \ No newline at end of file -- cgit v1.2.3-70-g09d2