private void updateMasterData(StockDataTransferObject record)
{
DBUpdater updater = new DBUpdater();
if (updater.updateMasterData(record) > 0)
{
MessageBox.Show(
"Folgende Stammdaten wurden gespeichert: " +
'\n' +
"ISIN: " +
record.isin +
'\n' +
"WKN: " +
record.wkn +
'\n' +
"Symbol: " +
record.symbol +
'\n' +
"Name: " +
record.name +
'\n' +
"Sektor: " +
record.sector +
'\n' +
"URL-Suffix: " +
record.suffix_onvista
);
}
else
{
MessageBox.Show("Stammdaten für " + record.name + " konnten nicht gespeichert werden.");
}
}
private int insertNewMasterData(StockDataTransferObject record)
{
string query =
"INSERT INTO " +
"aqm.tbl_stock_masterdata " +
"(" +
"at_isin, " +
"at_wkn, " +
"at_symbol, " +
"at_name, " +
"at_suffix_onvista, " +
"at_sector" +
")" +
"VALUES" +
"('"
+ record.isin
+ "', '"
+ record.wkn
+ "', '"
+ record.symbol
+ "', '"
+ record.name
+ "', '"
+ record.suffix_onvista
+ "', '"
+ record.sector +
"')";
return(executeQueryStandalone(query));
}
//Variante 5: Position, StockDataTransferObject-Liste (Content), ColumnsToDraw
public TableObject(Worksheet worksheet, Excel.Range startPosition, List <StockDataTransferObject> records, List <int> columnsToDraw)
{
this.worksheet = worksheet;
this.startPosition = startPosition;
initStartPosition();
if (records != null)
{
if (records.Count > 0)
{
this.headline = StockDataTransferObject.getHeadlineAsList();
}
else
{
this.headline = new List <string>();
}
}
this.content = new List <List <string> >();
this.columnsToDraw = columnsToDraw;
if (records != null)
{
foreach (StockDataTransferObject record in records)
{
content.Add(record.getLineAsList());
}
}
}
private string getStockID(StockDataTransferObject record)
{
if (stockID == null)
{
stockID = getStockIDBySymbol(record);
if (stockID != null)
{
return(stockID);
}
stockID = getStockIDByISIN(record);
if (stockID != null)
{
return(stockID);
}
stockID = getStockIDByWKN(record);
if (stockID != null)
{
return(stockID);
}
return(null);
}
else
{
return(stockID);
}
}
private void loadMetaData()
{
try
{
MySQLConnector mySQLConnector = new MySQLConnector();
StockDataTransferObject record = mySQLConnector.getMasterDataForSymbol(symbol);
if (record == null)
{
throw new Exception("Es konnten keine Stammdaten für dieses Symbol in Ihrer Datenbank gefunden werden. Bitte laden Sie diese zunächst nach und versuchen es dann erneut.");
}
urlSuffix = record.suffix_onvista;
isin = record.isin;
wkn = record.wkn;
name = record.name;
sector = record.sector;
}
catch (Exception e)
{
ExceptionHandler.handle(e);
urlSuffix = "";
isin = "";
wkn = "";
name = "";
sector = "";
}
}
private void updateSubscribers(StockDataTransferObject record)
{
foreach (LiveConnectionSubscriber subscriber in listOfSubscribers)
{
Logger.log("(" + symbol + ") Updated Subscriber: " + subscriber.ToString().Replace("AQM_Algo_Trading_Addin_CGR.", ""));
subscriber.updateMeWithNewData(record);
}
}
//Variante 3: Position, Headline, Content
public TableObject(Worksheet worksheet, Excel.Range startPosition, List <string> headline, List <List <string> > content)
{
this.worksheet = worksheet;
this.startPosition = startPosition;
initStartPosition();
this.headline = headline;
this.content = content;
columnsToDraw = StockDataTransferObject.getStandardColumnsToDraw();
}
//Variante 6: Position, ColumnsToDraw
public TableObject(Worksheet worksheet, Excel.Range startPosition, List <int> columnsToDraw)
{
this.worksheet = worksheet;
this.startPosition = startPosition;
initStartPosition();
this.headline = StockDataTransferObject.getHeadlineAsList();
this.columnsToDraw = columnsToDraw;
this.content = new List <List <string> >();
}
private List <StockDataTransferObject> parseHistoricData(string csvData)
{
List <StockDataTransferObject> records = new List <StockDataTransferObject>();
bool firstRow = true;
string temp;
int i;
string[] rows = csvData.Replace("\r", "").Split('\n');
foreach (string row in rows)
{
i = 0;
if (string.IsNullOrEmpty(row))
{
continue;
}
string[] cols = row.Split(',');
if (firstRow == true)
{
firstRow = false;
//skip first row which contains column names
}
else
{
StockDataTransferObject record = new StockDataTransferObject();
record.timestamp_otherdata = cols[i++];
temp = cols[i++];
record.open = temp;
temp = cols[i++];
record.high = temp;
temp = cols[i++];
record.low = temp;
temp = cols[i++];
record.close = temp;
temp = cols[i++];
record.total_volume = temp;
temp = cols[i++];
record.adj_close = temp;
records.Add(record);
}
}
return(records);
}
public List <int> getColumnsToDraw_LiveStockData()
{
List <int> columnsToDraw = StockDataTransferObject.getStandardColumnsToDraw();
columnsToDraw.Remove(StockDataTransferObject.posAdjClose);
columnsToDraw.Remove(StockDataTransferObject.posTimestampPrice);
columnsToDraw.Remove(StockDataTransferObject.posTimestampVolume);
columnsToDraw.Remove(StockDataTransferObject.posClose);
return(columnsToDraw);
}
public int updateMasterData(StockDataTransferObject record)
{
if (getStockID(record) == null)
{
return(insertNewMasterData(record));
}
else
{
return(updateExistingMasterData(record));
}
}
public void updateMeWithNewData(StockDataTransferObject newRecord)
{
letzterwert = aktuelleraktienwert;
aktuelleraktienwert = Convert.ToDouble(newRecord.price);
if (letzterwert != 0 && aktuelleraktienwert > letzterwert)
{
initStart = initStart + 1;
zahlVerlust = 0;
if (initStart == 2)
{
startaktienwert = aktuelleraktienwert;
status = "Kaufen";
kontostand = kontostand - startaktienwert;
setDataInRibbon(0, kontostand, System.Drawing.Color.Green);
}
if (initStart > 2)
{
gewinn = ((aktuelleraktienwert - startaktienwert) / startaktienwert) * 100;
kontostand = kontostand + (aktuelleraktienwert - startaktienwert);
status = "Behalten";
setDataInRibbon(gewinn, kontostand, System.Drawing.Color.DarkOrange);
}
}
if (letzterwert != 0 && aktuelleraktienwert < letzterwert)
{
if (initStart >= 2)
{
zahlVerlust = zahlVerlust + 1;
gewinn = ((aktuelleraktienwert - startaktienwert) / startaktienwert) * 100;
kontostand = kontostand + (aktuelleraktienwert - startaktienwert);
setDataInRibbon(gewinn, kontostand, System.Drawing.Color.DarkOrange);
if (gewinn < 2 && zahlVerlust >= 3 || gewinn > 2 && zahlVerlust >= 5)
{
status = "Verkaufen";
setDataInRibbon(gewinn, kontostand, System.Drawing.Color.Red);
status = "Verkauft";
gewinn = 0;
initStart = 0;
kontostand = kontostand + aktuelleraktienwert;
zahlVerlust = 0;
startaktienwert = 0;
setDataInRibbon(gewinn, kontostand, System.Drawing.Color.Black);
}
}
else
{
initStart = 0;
}
}
}
public void updateMeWithNewData(StockDataTransferObject newRecord)
{
headline = StockDataTransferObject.getHeadlineAsList();
content.Add(newRecord.getLineAsList());
if (headlineVisible == false)
{
drawHeadline();
}
drawRecord(newRecord);
updateSubscribers();
}
public void updateMeWithNewData(StockDataTransferObject newRecord)
{
try
{
updateMasterData(newRecord);
updateTransactionData(newRecord);
}
catch (Exception e)
{
mySQLConnector.closeConnection();
ExceptionHandler.handle(e);
}
}
public StockDataTransferObject getStockData()
{
StockDataTransferObject stdTransferObject = new StockDataTransferObject();
if (urlSuffix != "")
{
initWebClient();
loadHtmlData();
lastRecord = newRecord;
stdTransferObject.isin = extractIsin();
stdTransferObject.wkn = extractWkn();
stdTransferObject.symbol = extractSymbol();
stdTransferObject.name = extractName();
stdTransferObject.sector = extractSector();
stdTransferObject.price = extractPrice();
stdTransferObject.timestamp_price = extractTimestampPrice();
stdTransferObject.volume = extractVolume();
stdTransferObject.timestamp_volume = extractTimestampVolume();
stdTransferObject.high = extractDayHigh();
stdTransferObject.low = extractDayLow();
stdTransferObject.open = extractDayOpen();
stdTransferObject.preday_close = extractPredayClose();
stdTransferObject.total_volume = extractDayVolume();
stdTransferObject.trend_abs = extractTrendAbs();
stdTransferObject.trend_perc = extractTrendPerc();
stdTransferObject.timestamp_otherdata = extractTimestampOtherData();
stdTransferObject.trading_floor = extractTradingFloor();
stdTransferObject.currency = extractCurrency();
stdTransferObject.provider = provider;
stdTransferObject.suffix_onvista = extractUrlSuffix();
newRecord = stdTransferObject;
}
return(stdTransferObject);
}
private StockDataTransferObject getMasterDataForX(string stockID)
{
if (stockID == null)
{
return(null);
}
StockDataTransferObject record = new StockDataTransferObject();
string query =
"SELECT " +
"at_isin, " +
"at_wkn, " +
"at_symbol, " +
"at_name, " +
"at_suffix_onvista, " +
"at_sector " +
"FROM " +
"aqm.tbl_stock_masterdata " +
"WHERE " +
"pk_id " +
"= " +
"'" +
stockID +
"'";
openConnection();
executeQuery(query);
if (mySQLDataReader.Read())
{
record.isin = mySQLDataReader.GetString("at_isin");
record.wkn = mySQLDataReader.GetString("at_wkn");
record.symbol = mySQLDataReader.GetString("at_symbol");
record.name = mySQLDataReader.GetString("at_name");
record.suffix_onvista = mySQLDataReader.GetString("at_suffix_onvista");
record.sector = mySQLDataReader.GetString("at_sector");
}
closeConnection();
return(record);
}
private void doWork()
{
while (doThreading)
{
while (doPause)
{
Thread.Sleep(1000);
}
//Logger.log("(" + symbol + ") Requested Data");
StockDataTransferObject sdtObject = liveConnector.getStockData();
if (liveConnector.checkChange())
{
updateSubscribers(sdtObject);
}
/*else
* Logger.log("(" + symbol + ") No new Record available");*/
}
Logger.log("(" + symbol + ") Stopped and killed PushWorker");
}
private string getStockIDByWKN(StockDataTransferObject record)
{
return(getStockIDByWKN(record.wkn));
}
private string getStockIDBySymbol(StockDataTransferObject record)
{
return(getStockIDBySymbol(record.symbol));
}
public void updateTransactionData(StockDataTransferObject newRecord)
{
mySQLConnector.enrichTransactionData(newRecord);
}
public int updateMasterData(StockDataTransferObject newRecord)
{
return(mySQLConnector.updateMasterData(newRecord));
}
public int enrichTransactionData(StockDataTransferObject record)
{
string query =
"INSERT INTO " +
"aqm.tbl_stock_transactiondata " +
"(" +
"fk_stock_id, " +
"at_price, " +
"at_volume, " +
"at_trend_abs, " +
"at_trend_perc, " +
"at_open, " +
"at_close, " +
"at_high, " +
"at_low, " +
"at_adj_close, " +
"at_preday_close, " +
"at_totalvolume, " +
"at_timestamp_price, " +
"at_timestamp_volume, " +
"at_timestamp_otherdata, " +
"at_provider, " +
"at_trading_floor, " +
"at_currency" +
")" +
"VALUES" +
"('"
+ getStockID(record)
+ "', '"
+ record.price
+ "', '"
+ record.volume
+ "', '"
+ record.trend_abs
+ "', '"
+ record.trend_perc
+ "', '"
+ record.open
+ "', '"
+ record.close
+ "', '"
+ record.high
+ "', '"
+ record.low
+ "', '"
+ record.adj_close
+ "', '"
+ record.preday_close
+ "', '"
+ record.total_volume
+ "', '"
+ record.timestamp_price
+ "', '"
+ record.timestamp_volume
+ "', '"
+ record.timestamp_otherdata
+ "', '"
+ record.provider
+ "', '"
+ record.trading_floor
+ "', '"
+ record.currency +
"')";
return(executeQueryStandalone(query));
}
private int updateExistingMasterData(StockDataTransferObject record)
{
bool isinSet = record.isin != null;
bool wknSet = record.wkn != null;
bool symbolSet = record.symbol != null;
bool nameSet = record.name != null;
bool suffixSet = record.suffix_onvista != null;
bool sectorSet = record.sector != null;
bool firstItem = true;
string query =
"UPDATE " +
"aqm.tbl_stock_masterdata " +
"SET ";
if (isinSet)
{
if (firstItem)
{
firstItem = false;
}
query += "at_isin = '" + record.isin + "'";
}
if (wknSet)
{
if (firstItem == false)
{
query += ", ";
}
else
{
firstItem = false;
}
query += "at_wkn = '" + record.wkn + "'";
}
if (symbolSet)
{
if (firstItem == false)
{
query += ", ";
}
else
{
firstItem = false;
}
query += "at_symbol = '" + record.symbol + "'";
}
if (nameSet)
{
if (firstItem == false)
{
query += ", ";
}
else
{
firstItem = false;
}
query += "at_name = '" + record.name + "'";
}
if (suffixSet)
{
if (firstItem == false)
{
query += ", ";
}
else
{
firstItem = false;
}
query += "at_suffix_onvista = '" + record.suffix_onvista + "'";
}
if (sectorSet)
{
if (firstItem == false)
{
query += ", ";
}
else
{
firstItem = false;
}
query += "at_sector = '" + record.sector + "'";
}
query +=
" WHERE " +
"pk_id " +
"= '" +
getStockID(record) +
"'";
return(executeQueryStandalone(query));
}
public List <int> getColumnsToDraw_Standard()
{
return(StockDataTransferObject.getStandardColumnsToDraw());
}
public void drawRecord(StockDataTransferObject record)
{
drawLine(record.getLineAsList());
}
private string getStockIDByISIN(StockDataTransferObject record)
{
return(getStockIDByISIN(record.isin));
}
public List <StockDataTransferObject> getHistoricalStockData(string stockSymbol, DateTime dateFrom, DateTime dateTo)
{
List <StockDataTransferObject> result = new List <StockDataTransferObject>();
string from = dateFrom.ToString("yyyy-MM-dd HH:mm:ss");
string to = dateTo.ToString("yyyy-MM-dd HH:mm:ss");
string query = "SELECT " +
"at_isin, " +
"at_wkn, " +
"at_symbol, " +
"at_name, " +
"at_sector, " +
"at_price, " +
"at_volume, " +
"at_trend_abs, " +
"at_trend_perc, " +
"at_open, " +
"at_close, " +
"at_high, " +
"at_low, " +
"at_adj_close, " +
"at_preday_close, " +
"at_totalvolume, " +
"at_timestamp_price, " +
"at_timestamp_volume, " +
"at_timestamp_otherdata, " +
"at_provider, " +
"at_trading_floor, " +
"at_currency " +
"FROM " +
"aqm.tbl_stock_transactiondata " +
"JOIN " +
"aqm.tbl_stock_masterdata " +
"ON " +
"aqm.tbl_stock_transactiondata.fk_stock_id " +
"= " +
"aqm.tbl_stock_masterdata.pk_id " +
"WHERE " +
"fk_stock_id " +
"= '" +
getStockIDBySymbol(stockSymbol) +
"' " +
"AND " +
"at_timestamp_otherdata " +
">= '" +
from +
"' AND " +
"at_timestamp_otherdata " +
"<= '" +
to +
"'";
try
{
openConnection();
executeQuery(query);
while (mySQLDataReader.Read())
{
StockDataTransferObject record = new StockDataTransferObject();
record.isin = mySQLDataReader.GetString("at_isin");
record.wkn = mySQLDataReader.GetString("at_wkn");
record.symbol = mySQLDataReader.GetString("at_symbol");
record.name = mySQLDataReader.GetString("at_name");
record.sector = mySQLDataReader.GetString("at_sector");
record.price = mySQLDataReader.GetString("at_price");
record.volume = mySQLDataReader.GetString("at_volume");
record.trend_abs = mySQLDataReader.GetString("at_trend_abs");
record.trend_perc = mySQLDataReader.GetString("at_trend_perc");
record.open = mySQLDataReader.GetString("at_open");
record.close = mySQLDataReader.GetString("at_close");
record.high = mySQLDataReader.GetString("at_high");
record.low = mySQLDataReader.GetString("at_low");
record.adj_close = mySQLDataReader.GetString("at_adj_close");
record.preday_close = mySQLDataReader.GetString("at_preday_close");
record.total_volume = mySQLDataReader.GetString("at_totalvolume");
record.timestamp_price = mySQLDataReader.GetString("at_timestamp_price");
record.timestamp_volume = mySQLDataReader.GetString("at_timestamp_volume");
record.timestamp_otherdata = mySQLDataReader.GetString("at_timestamp_otherdata");
record.provider = mySQLDataReader.GetString("at_provider");
record.trading_floor = mySQLDataReader.GetString("at_trading_floor");
record.currency = mySQLDataReader.GetString("at_currency");
result.Add(record);
}
closeConnection();
return(result);
}
catch (Exception e)
{
ExceptionHandler.handle(e);
return(result);
}
}
