public List <string> getColumns()
{
helperClass.log.Info("getColumns from getDictionary");
List <string> column_keys = new List <string>();
//var jsnData = new requestData(_url);
//var jsData = jsnData.getJSON();
JArray jsData = new JArray();
string[][] cntrStaff = helperClass.getStaff(_cntry);
for (int j = 0; j < cntrStaff.Length; j++)
{
string chunk = "";
for (int i = 0; i < cntrStaff[j].Length; i++)
{
chunk += cntrStaff[j][i] + ",";
}
string url = helperClass.getTsUrl(chunk, _indctr, _key, _iniDate, _clsDate);
var jsnData = new requestData(url);
foreach (var k in jsnData.getJSON())
{
jsData.Add(k);
}
}
for (int r = 0; r < jsData.Count; r++)
{
if (!column_keys.Contains(jsData[r]["Country"].ToString() + "-" + jsData[r]["Category"].ToString(), StringComparer.OrdinalIgnoreCase))
{
column_keys.Add(jsData[r]["Country"].ToString() + "-" + jsData[r]["Category"].ToString());
}
}
//return column_keys.OrderBy(a => a.Split('.', '-')[0]).ToList();
List <string> ordered_fullCntryNm = new List <string>();;
foreach (var item in _cntry.Split(',').ToList())
{
if (helperClass.myCountrysDict.ContainsValue(item))
{
ordered_fullCntryNm.Add(helperClass.myLongCountrysDict[item]);
}
}
List <string> results;
results = column_keys.OrderBy(d => {
var index = ordered_fullCntryNm.IndexOf(d.Split('.', '-')[0]);
return(index == -1 ? int.MaxValue : index);
}).ThenBy(p => {
var index = _indctr.Split(',').ToList().IndexOf(p.Split('.', '-')[1]);
return(index == -1 ? int.MaxValue : index);
}).ToList();
return(results);
}
//public Dictionary<string, Dictionary<string, string>> getDic()
public Dictionary <DateTime, Dictionary <string, string> > getDic()
{
helperClass.log.Info("getDictionary");
//var jsnData = new requestData(_url);
//var jsData = jsnData.getJSON();
JArray jsData = new JArray();
string[][] cntrStaff = helperClass.getStaff(_cntry);
for (int j = 0; j < cntrStaff.Length; j++)
{
string chunk = "";
for (int i = 0; i < cntrStaff[j].Length; i++)
{
chunk += cntrStaff[j][i] + ",";
}
string url = helperClass.getTsUrl(chunk, _indctr, _key, _iniDate, _clsDate);
var jsnData = new requestData(url);
foreach (var k in jsnData.getJSON())
{
jsData.Add(k);
}
}
//Dictionary<string, Dictionary<string, string>> myDictDict = new Dictionary<string, Dictionary<string, string>>();
Dictionary <DateTime, Dictionary <string, string> > myDictDict = new Dictionary <DateTime, Dictionary <string, string> >();
//List<string> column_keys = new List<string>();
for (int r = 0; r < jsData.Count; r++)
{
Dictionary <string, string> dict = new Dictionary <string, string>();
dict.Add(jsData[r]["Country"].ToString() + "-" + jsData[r]["Category"].ToString(), jsData[r]["Value"].ToString());
//if (myDictDict.ContainsKey(jsData[r]["DateTime"].ToString()))
if (myDictDict.ContainsKey(Convert.ToDateTime(jsData[r]["DateTime"])))
{
//myDictDict[jsData[r]["DateTime"].ToString()].Add(jsData[r]["Country"].ToString() + "-" + jsData[r]["Category"].ToString(), jsData[r]["Value"].ToString());
myDictDict[Convert.ToDateTime(jsData[r]["DateTime"])].Add(jsData[r]["Country"].ToString() + "-" + jsData[r]["Category"].ToString(), jsData[r]["Value"].ToString());
}
else
{
//myDictDict.Add(jsData[r]["DateTime"].ToString(), dict);
myDictDict.Add(Convert.ToDateTime(jsData[r]["DateTime"]), dict);
}
}
return(myDictDict);
}
public Dictionary <DateTime, Dictionary <string, string> > getDic()
{
//helperClass.log.Info("getDictionary");
JArray jsData = new JArray();
string[][] cntrStaff = helperClass.getStaff(_cntry);
for (int j = 0; j < cntrStaff.Length; j++)
{
string chunk = "";
for (int i = 0; i < cntrStaff[j].Length; i++)
{
chunk += cntrStaff[j][i] + ",";
}
var jsnData = new requestData(helperClass.getTsUrl(chunk, _indctr, _iniDate, _clsDate));
foreach (var k in jsnData.getJSON())
{
jsData.Add(k);
}
}
if (jsData.Count == 0)
{
MessageBox.Show("No data provided for selected parameters");
}
Dictionary <DateTime, Dictionary <string, string> > myDictDict = new Dictionary <DateTime, Dictionary <string, string> >();
for (int r = 0; r < jsData.Count; r++)
{
Dictionary <string, string> dict = new Dictionary <string, string>();
dict.Add(jsData[r]["Country"].ToString() + "-" + jsData[r]["Category"].ToString(), jsData[r]["Value"].ToString());
if (myDictDict.ContainsKey(Convert.ToDateTime(jsData[r]["DateTime"])))
{
myDictDict[Convert.ToDateTime(jsData[r]["DateTime"])].Add(jsData[r]["Country"].ToString() + "-" +
jsData[r]["Category"].ToString(), jsData[r]["Value"].ToString());
}
else
{
myDictDict.Add(Convert.ToDateTime(jsData[r]["DateTime"]), dict);
}
}
return(myDictDict);
}
public static string teGetMarkets(string mrkt, string columnsToUse, [ExcelArgument(AllowReference = true)] object firstCell)
{
SearchEngine.fromSearch = false;
udfClassHelper("TEMarkets", mrkt);
if (firstCell is ExcelMissing)
{
dataStartCell = formulaCell;
newFormula = string.Format($"=TEMarkets( \"{mrkt}\", \"{columnsToUse}\")");
}
else
{
try
{
dataStartCell = helperClass.ReferenceToRange((ExcelReference)firstCell);
newFormula = string.Format($"=TEMarkets( \"{mrkt}\", \"{columnsToUse}\", {dataStartCell.Address[false, false]})");
}
catch (Exception)
{
refError = true;
helperClass.getNewDict();
return("#REF!");
throw;
}
}
Range pass = null;
formulaColumns frmlaColumnsPair = new formulaColumns(newFormula, columnsToUse, pass, formulaCell);
MyRibbon.myNewDict = new Dictionary <string, formulaColumns>();
try
{
XlCall.Excel(XlCall.xlfVolatile, false);
}
catch (Exception e)
{
helperClass.log.Error(e.Message);
helperClass.log.Trace(e.StackTrace);
throw;
}
if (helperClass.runFormula == "RunAutomatically = 1")
{
if (MyRibbon.refresh != true)
{
helperClass.setGlobalDict(formulaCell.Address[false, false], frmlaColumnsPair);
}
url = host + "markets/" + mrkt + "?client=" + apiKeyFrm.apiKey + "&excel=" + apiKeyFrm.excelVersion;
var jsData = new requestData(url).getJSON();
if (jsData.Count == 0)
{
MessageBox.Show("No data provided for selected parameters");
}
else
{
try
{
helperClass.elseFunction(columnNamesHack(columnsToUse), jsData, dataStartCell, newFormula, formulaCell);
}
catch (Exception ex)
{
helperClass.log.Info(ex.Message);
helperClass.log.Trace(ex.StackTrace);
throw;
}
}
}
else
{
MyRibbon.sheet = MyRibbon.app.ActiveSheet;
MyRibbon.myFormulasDict = (MyRibbon.myMainDict.ContainsKey(MyRibbon.sheet.Index.ToString())) ?
MyRibbon.myMainDict[MyRibbon.sheet.Index.ToString()] : new Dictionary <string, formulaColumns>();
foreach (var item in MyRibbon.myFormulasDict.Keys)
{
if (MyRibbon.myFormulasDict[item]._formula == newFormula && item == MyRibbon.myFormulasDict[item]._caller.Address[false, false])
{
return(formulaCell.Text);
}
}
Dictionary <string, formulaColumns> myNewDict = helperClass.getNewDict();
url = host + "markets/" + mrkt + "?client=" + apiKeyFrm.apiKey + "&excel=" + apiKeyFrm.excelVersion;
var jsData = new requestData(url).getJSON();
if (jsData.Count == 0)
{
MessageBox.Show("No data provided for selected parameters");
}
else
{
try
{
helperClass.elseFunction(columnNamesHack(columnsToUse), jsData, dataStartCell, newFormula, formulaCell);
}
catch (Exception ex)
{
helperClass.log.Info(ex.Message);
helperClass.log.Trace(ex.StackTrace);
throw;
}
}
helperClass.RemoveOldKey(myNewDict);
}
customFunctionEnd("Markets");
return((formulaCell.Address == dataStartCell.Address)? columnsToUse.Split(',')[0] : sharedFunctions.getAnswer(mrkt));
}
public static string teGetMarkets(string mrkt, string columnsToUse, [ExcelArgument(AllowReference = true)] object firstCell)
{
helperClass.log.Info("=======================");
helperClass.log.Info("Starting TEMarkets udf");
string key = Properties.Settings.Default.userApiKey;
// Convert column names to JArray names
fullNames = new Dictionary <string, string>();
if (mrkt == "bonds")
{
for (int i = 0; i < helperClass.bondsNames.Length; i++)
{
fullNames.Add(helperClass.bondsNames[i], helperClass.bondsNamesFull[i]);
}
}
else
{
for (int i = 0; i < helperClass.marketsNames.Length; i++)
{
fullNames.Add(helperClass.marketsNames[i], helperClass.marketsNamesFull[i]);
}
}
string answer = "Updated at " + DateTime.Now.TimeOfDay.ToString("hh\\:mm\\:ss");
string columns = columnsToUse;
try
{
MyRibbon.sheet = MyRibbon.app.ActiveSheet;
}
catch (Exception)
{
MyRibbon.app = (Microsoft.Office.Interop.Excel.Application)ExcelDnaUtil.Application;
MyRibbon.sheet = MyRibbon.app.ActiveSheet;
}
ExcelReference caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;
Range caller_range = helperClass.ReferenceToRange(caller);
Range formulaCell = caller_range; //Last cell used in userform
Range dataStartCell;
string newFormula;
if (firstCell is ExcelMissing)
{
dataStartCell = formulaCell;
newFormula = string.Format($"=TEMarkets( \"{mrkt}\", \"{columns}\")");
}
else
{
try
{
dataStartCell = helperClass.ReferenceToRange((ExcelReference)firstCell);
newFormula = string.Format($"=TEMarkets( \"{mrkt}\", \"{columns}\", {dataStartCell.Address[false, false]})");
}
catch (Exception)
{
refError = true;
helperClass.getNewDict();
return("#REF!");
throw;
}
}
Range pass = null;
formulaColumns frmlaColumnsPair = new formulaColumns(newFormula, columns, pass, formulaCell);
MyRibbon.myNewDict = new Dictionary <string, formulaColumns>();
try
{
XlCall.Excel(XlCall.xlfVolatile, false);
}
catch (Exception e)
{
helperClass.log.Error(e.Message);
helperClass.log.Trace(e.StackTrace);
throw;
}
if (formulaCell.Address == dataStartCell.Address)
{
answer = columns.Split(',')[0];
}
if (helperClass.runFormula == "RunAutomatically = 1")
{
helperClass.log.Info("TEMarkets udf -> RunAutomatically = 1");
if (MyRibbon.refresh != true)
{
helperClass.setGlobalDict(formulaCell.Address[false, false], frmlaColumnsPair);
}
url = host + "markets/" + mrkt + "?client=" + key + "&excel=" + helperClass.Determine_OfficeVersion();
var jsnData = new requestData(url);
var jsData = jsnData.getJSON();
if (jsData.Count == 0)
{
MessageBox.Show("No data provided for selected parameters");
}
else
{
List <string> columnsFull = new List <string>();
foreach (var item in columns.Split(','))
{
if (fullNames.ContainsKey(item))
{
columnsFull.Add(fullNames[item]);
}
}
columns = String.Join(",", columnsFull);
try
{
helperClass.elseFunction(columns, jsData, key, dataStartCell, newFormula, formulaCell);
}
catch (Exception ex)
{
helperClass.log.Info(ex.Message);
helperClass.log.Trace(ex.StackTrace);
throw;
}
}
}
else
{
helperClass.log.Info("TEMarkets udf -> RunAutomatically != 1");
MyRibbon.sheet = MyRibbon.app.ActiveSheet;
if (MyRibbon.myMainDict.ContainsKey(MyRibbon.sheet.Index.ToString()))
{
MyRibbon.myFormulasDict = MyRibbon.myMainDict[MyRibbon.sheet.Index.ToString()];
}
else
{
MyRibbon.myFormulasDict = new Dictionary <string, formulaColumns>();
}
foreach (var item in MyRibbon.myFormulasDict.Keys)
{
if (MyRibbon.myFormulasDict[item]._formula == newFormula && item == MyRibbon.myFormulasDict[item]._caller.Address[false, false])
{
return(answer);
}
}
Dictionary <string, formulaColumns> myNewDict = helperClass.getNewDict();
url = host + "markets/" + mrkt + "?client=" + key + "&excel=" + helperClass.Determine_OfficeVersion();
var jsnData = new requestData(url);
var jsData = jsnData.getJSON();
if (jsData.Count == 0)
{
MessageBox.Show("No data provided for selected parameters");
}
else
{
List <string> columnsFull = new List <string>();
foreach (var item in columns.Split(','))
{
if (fullNames.ContainsKey(item))
{
columnsFull.Add(fullNames[item]);
}
}
columns = String.Join(",", columnsFull);
try
{
helperClass.elseFunction(columns, jsData, key, dataStartCell, newFormula, formulaCell);
}
catch (Exception ex)
{
helperClass.log.Info(ex.Message);
helperClass.log.Trace(ex.StackTrace);
throw;
}
}
helperClass.RemoveOldKey(myNewDict);
}
helperClass.runFormula = "RunAutomatically = 0";
helperClass.origin = true;
helperClass.log.Info("Printing current cell value and finishing Markets process");
MyRibbon.refresh = false;
return(answer);
}
