// write transactions loaded from webservice into the database // private static void SaveTransactions(TWTransactions transactions) { try { SQLiteCommand cmd; if (transactions.Count() == 0) { return; } string propName = "LastDate-" + transactions[0].AccountRef; DateTime lastDate = Config.GetDateProp(propName); int maxLoadID = DBUtilities.GetMax("SELECT Max(LoadGroupID) FROM transactions"); int newLoadID = maxLoadID + 1; //(new SQLiteCommand("DELETE FROM transactions WHERE 1=1", conn)).ExecuteNonQuery(); string sql = "INSERT INTO transactions(Time, LoadGroupID, TransType, TransSubType, TransID, Symbol, 'Buy-Sell', 'Open-Close', Quantity, ExpireDate, Strike, Type, Price, Fees, Amount, Description, Account)" + " Values(@tm,@lg,@tt,@tst,@tid,@sym,@buy,@op,@qu,@exp,@str,@ty,@pr,@fe,@am,@des,@acc)"; SQLiteTransaction sqlTransaction = App.ConnStr.BeginTransaction(); foreach (var record in transactions) { if (record.Time.Trim(TimeSpan.TicksPerSecond) > lastDate) { cmd = new SQLiteCommand(sql, App.ConnStr); cmd.Parameters.AddWithValue("tm", record.Time.Trim(TimeSpan.TicksPerSecond)); cmd.Parameters.AddWithValue("lg", newLoadID); // load group id cmd.Parameters.AddWithValue("tt", record.TransactionCode); cmd.Parameters.AddWithValue("tst", record.TransactionSubcode); cmd.Parameters.AddWithValue("tid", record.TransID); cmd.Parameters.AddWithValue("sym", record.Symbol); cmd.Parameters.AddWithValue("buy", record.BuySell); cmd.Parameters.AddWithValue("op", record.OpenClose); cmd.Parameters.AddWithValue("qu", record.Quantity); cmd.Parameters.AddWithValue("exp", record.ExpireDate); cmd.Parameters.AddWithValue("str", record.Strike); cmd.Parameters.AddWithValue("ty", record.InsType); cmd.Parameters.AddWithValue("pr", record.Price); cmd.Parameters.AddWithValue("fe", record.Fees); cmd.Parameters.AddWithValue("am", record.Amount); cmd.Parameters.AddWithValue("des", record.Description); cmd.Parameters.AddWithValue("acc", record.AccountRef); cmd.ExecuteNonQuery(); } } sqlTransaction.Commit(); } catch (Exception ex) { Debug.WriteLine("ERROR: Save Transaction: " + ex.Message); } return; }
public static TWTransactions Transactions(string accountNumber, DateTime?start, DateTime?end) { SetHeaders(Token); string url = "https://api.tastyworks.com/accounts/" + accountNumber + "/transactions?"; if (start != null) { url += "start-date=" + String.Format("{0:yyyy'-'MM'-'dd'T'HH':'mm':'ss'Z'}", start) + "&"; } if (end != null) { url += "end-date=" + String.Format("{0:yyyy'-'MM'-'dd'T'HH':'mm':'ss'Z'}", end); } string reply = Web.DownloadString(url); //Debug.WriteLine(reply); JObject package = JObject.Parse(reply); TWTransactions returnList = new TWTransactions(); Int32 pages = Convert.ToInt32(package["pagination"]["total-pages"]); Int32 pageOffset = 0; List <JToken> list = package["data"]["items"].Children().ToList(); do { foreach (JToken item in list) { //Debug.WriteLine(item.ToString()); TWTransaction inst = new TWTransaction(); inst.TransID = Convert.ToInt32(item["id"]); inst.Time = Convert.ToDateTime(item["executed-at"]).ToUniversalTime(); inst.TransactionCode = item["transaction-type"].ToString(); inst.TransactionSubcode = item["transaction-sub-type"].ToString(); if (item["action"] != null) { inst.Action = item["action"].ToString(); } inst.Description = item["description"].ToString(); inst.AccountRef = item["account-number"].ToString(); inst.Price = Convert.ToDecimal(item["price"]); inst.Fees = Convert.ToDecimal(item["commission"]) + Convert.ToDecimal(item["clearing-fees"]) + Convert.ToDecimal(item["regulatory-fees"]); inst.Amount = Convert.ToDecimal(item["value"]) * ((item["value-effect"].ToString() == "Debit") ? -1 : 1); if ((inst.TransactionCode == "Trade") || (inst.TransactionCode == "Receive Deliver")) { inst.Symbol = item["underlying-symbol"].ToString(); inst.Quantity = Convert.ToDecimal(item["quantity"]); SymbolDecoder symbol = new SymbolDecoder(item["symbol"].ToString(), item["instrument-type"].ToString()); inst.InsType = symbol.Type; inst.ExpireDate = symbol.Expiration; inst.Strike = symbol.Strike; } CompleteInstance(inst); returnList.Add(inst); } if (pages > 1) { SetHeaders(Token); reply = Web.DownloadString(url + "&page-offset=" + ++pageOffset); package = JObject.Parse(reply); list = package["data"]["items"].Children().ToList(); } pages--; } while (pages > 0); return((returnList.Count > 0) ? returnList : null); }
public static void Load(Accounts accounts) { try { // establish db connection App.OpenConnection(); if (TastyWorks.InitiateSession(Config.GetEncryptedProp("Username"), Config.GetEncryptedProp("Password"))) { // cache the current positions for details required to establish default risk and capreq twpositions = new Dictionary <string, TWPositions>(); twMarginReq = new Dictionary <string, TWMargins>(); foreach (Account a in accounts) { if (a.Active) { // retrieve Tastyworks positions for given account TWPositions pos = TastyWorks.Positions(a.ID); twpositions.Add(a.ID, pos); TWMargins mar = TastyWorks.MarginData(a.ID); twMarginReq.Add(a.ID, mar); } } // proceed with transactions from all accounts foreach (Account a in accounts) { if (a.Active) { Debug.WriteLine(a.ID); // retrieve Tastyworks transactions for the past month TWTransactions transactions = TastyWorks.Transactions(a.ID, DateTime.Today.AddDays(-30), null); SaveTransactions(transactions); // transfer transaction array to database // save latest transaction for next upload SQLiteCommand cmd = new SQLiteCommand("SELECT max(time) FROM transactions WHERE Account = @ac", App.ConnStr); cmd.Parameters.AddWithValue("ac", a.ID); SQLiteDataReader rdr = cmd.ExecuteReader(); string propName = "LastDate-" + a.ID; if (rdr.Read()) { Config.SetProp(propName, rdr[0].ToString()); } } } UpdateNewTransactions(); // matches unassociated asignments and exercises } else { MessageBox.Show("Login to TastyWorks failed", "Error"); } } catch (Exception ex) { Console.WriteLine("ERROR DataLoader: " + ex.Message); MessageBox.Show(ex.Message, "Sync Error"); } }