public void GrabAndSave()
{
int i = 0;
int tot = m_Tickers.Count;
float progress = 0.0f;
foreach (string ticker in m_Tickers)
{
//Console.WriteLine("Processing {0}", ticker);
WebRequest webReq = WebRequest.Create(srcLink + ticker);
using (WebResponse response = webReq.GetResponse())
{
// Get the data stream that is associated with the specified URL.
using (Stream responseStream = response.GetResponseStream())
{
// Read the bytes in responseStream and copy them to content.
//get the downloaded string
var strmReader = new StreamReader(responseStream);
string rxBuffer = strmReader.ReadToEnd();
FundaData fData = ProcessFunda(rxBuffer, ticker, m_cID[i]);
StoreFunda(fData);
}
}
i++;
//update progress
progress = 100 * (i + 1.0f) / tot;
if (m_Sender.CancellationPending == true)
{
break;
}
else
{
m_Sender.ReportProgress((int)progress);
}
} // foreach
} // GrabAndSave
//given a buffer full of text with company info
//parse and fill the Funda structure
private FundaData ProcessFunda(string strComp, string ticker, string cid)
{
FundaData aData = new FundaData();
aData.ticker = ticker;
aData.cID = cid;
int startAt, endAt;
// ------------------------------------------------------------------------------- //
// basic information
startAt = strComp.IndexOf(@"Basic Information", System.StringComparison.CurrentCultureIgnoreCase);
startAt = strComp.IndexOf(@"<table", startAt, System.StringComparison.CurrentCultureIgnoreCase);
endAt = strComp.IndexOf(@"</table", startAt, System.StringComparison.CurrentCultureIgnoreCase);
string basicInfo = strComp.Substring(startAt, endAt - startAt + @"</table".Length + 1);
// Creates an HtmlDocument object from an URL
HtmlDocument document = new HtmlDocument();
//load the given html string
document.LoadHtml(basicInfo);
//get all the rows
IEnumerable <HtmlNode> allRows = document.DocumentNode.SelectNodes("//tr");
int rowIndex = 0;
HtmlNodeCollection allTds;
foreach (HtmlNode row in allRows)
{
// Extracts all links within that node
allTds = row.SelectNodes("td");
switch (rowIndex)
{
case 0:
aData.authCapital = FloatParse(allTds[1].InnerText.Trim().Replace(",", string.Empty));
break;
case 1:
aData.paidupCapital = FloatParse(allTds[1].InnerText.Trim().Replace(",", string.Empty));
break;
case 2:
aData.faceValue = FloatParse(allTds[1].InnerText.Trim().Replace(",", string.Empty));
break;
case 3:
aData.shareCount = int.Parse(allTds[1].InnerText.Trim().Replace(",", string.Empty));
break;
default:
break;
}
rowIndex++;
if (rowIndex == 4)
{
break;
}
}
//second/right hand side table
startAt = strComp.IndexOf(@"<table", startAt + @"<table".Length, System.StringComparison.CurrentCultureIgnoreCase);
endAt = strComp.IndexOf(@"</table", startAt, System.StringComparison.CurrentCultureIgnoreCase);
basicInfo = strComp.Substring(startAt, endAt - startAt + @"</table".Length + 1);
//load the given html string
document.LoadHtml(basicInfo);
//get all the rows
allRows = document.DocumentNode.SelectNodes("//tr");
rowIndex = 0;
foreach (HtmlNode row in allRows)
{
// Extracts all links within that node
allTds = row.SelectNodes("td");
switch (rowIndex)
{
case 0:
break;
case 1: // type of security
aData.shareType = StringToShareType(allTds[1].InnerText.Trim());
break;
case 2: // lot
aData.lotSize = int.Parse(allTds[1].InnerText.Trim().Replace(",", string.Empty));
break;
case 3: // sector
aData.sID = StringToSectorId(allTds[1].InnerText.Trim());
break;
default:
break;
}
rowIndex++;
if (rowIndex == 4)
{
break;
}
}
aData.compInfoValid = true;
//----------------------------------------------------------------------------------------
//----------------------------------------------------------------------------------------
// interim financials
startAt = strComp.IndexOf(@"Interim Financial Performance:", System.StringComparison.CurrentCultureIgnoreCase);
if (startAt == -1)
{
return(aData);
}
startAt = strComp.IndexOf(@"<table", startAt, System.StringComparison.CurrentCultureIgnoreCase);
endAt = strComp.IndexOf(@"</table", startAt, System.StringComparison.CurrentCultureIgnoreCase);
string interimInfo = strComp.Substring(startAt, endAt - startAt + @"</table".Length + 1);
//load the given html string
document.LoadHtml(interimInfo);
//get all the rows
//allRows = document.DocumentNode.SelectNodes("//tr";
HtmlNode selRow = document.DocumentNode.SelectSingleNode("//tr[10]");
// Extracts all links within that node
allTds = selRow.SelectNodes("td");
aData.EPS_Q1 = FloatParse(allTds[1].InnerText.Trim());
aData.EPS_Q2 = FloatParse(allTds[2].InnerText.Trim());
string[] data = allTds[3].InnerHtml.Trim().Split(new string[] { "<span>" }, StringSplitOptions.RemoveEmptyEntries);
aData.EPS_HalfYearly = FloatParse(StripStartTags(data[0]).Trim());
aData.EPS_Q3 = FloatParse(allTds[4].InnerText.Trim());
data = allTds[6].InnerText.Trim().Split(new string[] { "<span>" }, StringSplitOptions.RemoveEmptyEntries);
aData.EPS_Annual = FloatParse(StripStartTags(data[0]).Trim()); //float.Parse(data[0].Trim());
aData.intermYear = DateTime.Now; //.Year.ToString();
aData.intFinValid = true;
//---------------------------------------------------------------------------------------
//----------------------------------------------------------------------------
// Financial Performance as per Audited
startAt = strComp.IndexOf(@"Financial Performance as per Audited", System.StringComparison.CurrentCultureIgnoreCase);
startAt = strComp.IndexOf(@"<table", startAt, System.StringComparison.CurrentCultureIgnoreCase);
endAt = strComp.IndexOf(@"</table", startAt, System.StringComparison.CurrentCultureIgnoreCase);
string auditedInfo = strComp.Substring(startAt, endAt - startAt + @"</table".Length + 1);
//load the given html string
document.LoadHtml(auditedInfo);
//get all the rows
allRows = document.DocumentNode.SelectNodes("//tr");
rowIndex = 0;
int rowCount = allRows.Count() - 3;
aData.finYear = new DateTime[rowCount];
aData.EPS_Orig = new float[rowCount];
aData.EPS_Restated = new float[rowCount];
aData.NAV_Orig = new float[rowCount];
aData.NAV_Restated = new float[rowCount];
foreach (var row in allRows)
{
rowIndex++;
if (rowIndex == 1 || rowIndex == 2 || rowIndex == 3) // skip first 3 rows
{
continue;
}
// Extracts all links within that node
allTds = row.SelectNodes("td");
aData.finYear[rowIndex - 4] = Convert.ToDateTime(allTds[0].InnerText.Trim() + "-01-01");
aData.EPS_Orig[rowIndex - 4] = FloatParse(allTds[4].InnerText.Trim());
aData.EPS_Restated[rowIndex - 4] = FloatParse(allTds[5].InnerText.Trim());
aData.NAV_Orig[rowIndex - 4] = FloatParse(allTds[7].InnerText.Trim());
aData.NAV_Restated[rowIndex - 4] = FloatParse(allTds[8].InnerText.Trim());
}
aData.audFinValid = true;
//-------------------------------------------------------------------------------------
//--------------------------------------------------------------------------------------
//other information
startAt = strComp.IndexOf(@"Other Information of the Company", System.StringComparison.CurrentCultureIgnoreCase);
startAt = strComp.IndexOf(@"<table", startAt, System.StringComparison.CurrentCultureIgnoreCase);
endAt = strComp.IndexOf(@"</table", startAt, System.StringComparison.CurrentCultureIgnoreCase);
string otherInfo = strComp.Substring(startAt, endAt - startAt + @"</table".Length + 1);
//load the given html string
document.LoadHtml(otherInfo);
//get all the rows
allRows = document.DocumentNode.SelectNodes("//tr");
rowIndex = 0;
foreach (HtmlNode row in allRows)
{
// Extracts all links within that node
allTds = row.SelectNodes("td");
switch (rowIndex)
{
case 0:
aData.listingYear = Convert.ToDateTime(allTds[1].InnerText.Trim() + "-01-01");
break;
case 1:
aData.shareCat = FundaData.GetCat(allTds[1].InnerText.Trim());
break;
default:
break;
}
rowIndex++;
if (rowIndex == 2)
{
break;
}
}
//----------------------------------------------------------------------------------------
//----------------------------------------------------------------------------------------
//holding info
int found = strComp.IndexOf(@"Share Holding", System.StringComparison.CurrentCultureIgnoreCase);
while (found > -1)
{
startAt = found;
found = strComp.IndexOf(@"Share Holding", startAt + 10, System.StringComparison.CurrentCultureIgnoreCase);
}
startAt = strComp.IndexOf(@"<table", startAt, System.StringComparison.CurrentCultureIgnoreCase);
endAt = strComp.IndexOf(@"</table", startAt, System.StringComparison.CurrentCultureIgnoreCase);
string holdingInfo = strComp.Substring(startAt, endAt - startAt + @"</table".Length + 1);
//load the given html string
document.LoadHtml(holdingInfo);
//get all the rows
allRows = document.DocumentNode.SelectNodes("//tr");
rowIndex = 0;
foreach (HtmlNode row in allRows)
{
// Extracts all links within that node
allTds = row.SelectNodes("td");
for (int col = 0; col < 5; col++)
{
string tempHolding = allTds[col].InnerText.Trim(); //InnerHtml.Trim();
string[] holdings = tempHolding.Split(new string[] { ":" }, StringSplitOptions.RemoveEmptyEntries); //"\r\n"
switch (col)
{
case 0:
aData.sponDir = FloatParse(holdings[1].Trim());
break;
case 1:
aData.gov = FloatParse(holdings[1].Trim());
break;
case 2:
aData.inst = FloatParse(holdings[1].Trim());
break;
case 3:
aData.foreign = FloatParse(holdings[1].Trim());
break;
case 4:
aData.pub = FloatParse(holdings[1].Trim());
break;
default:
break;
}
}
break; // only one row
} // for row
aData.holdingInfoValid = true;
//---------------------------------------------------------------------------------------
//----------------------------------------------------------------------------------------
//corporate performance
startAt = strComp.IndexOf(@"Corporate Performance", System.StringComparison.CurrentCultureIgnoreCase);
startAt = strComp.IndexOf(@"<table", startAt, System.StringComparison.CurrentCultureIgnoreCase);
endAt = strComp.IndexOf(@"</table", startAt, System.StringComparison.CurrentCultureIgnoreCase);
string corpPerf = strComp.Substring(startAt, endAt - startAt + @"</table".Length + 1);
//load the given html string
document.LoadHtml(corpPerf);
//get all the rows
allRows = document.DocumentNode.SelectNodes("//tr");
rowIndex = 0;
foreach (HtmlNode row in allRows)
{
if (rowIndex == 0 || rowIndex == 1)
{
rowIndex++;
continue;
}
// Extracts all links within that node
allTds = row.SelectNodes("td");
switch (rowIndex)
{
case 2:
aData.LoanShort = FloatParse(allTds[2].InnerText.Trim());
break;
case 3:
aData.LoanLong = FloatParse(allTds[1].InnerText.Trim());
break;
default:
break;
}
rowIndex++;
if (rowIndex == 4)
{
break;
}
}
return(aData);
}
} // GrabAndSave
//store a block of funda data to the dB
private void StoreFunda(FundaData fData)
{
m_dbConnection.Open();
using (SQLiteCommand command = m_dbConnection.CreateCommand())
{
//basic info
if (fData.compInfoValid) // valid company info
{
string query = @"UPDATE Company SET sID=" + fData.sID + ", AuthCapital=" + fData.authCapital + ", PaidupCapital=" + fData.paidupCapital + ", ShareCount=" + fData.shareCount +
", FaceValue=" + fData.faceValue + ", LotSize=" + fData.lotSize + ", ListYear='" + fData.listingYear + "', ShareType=" + (int)fData.shareType +
" WHERE Ticker='" + fData.ticker + "'";
command.CommandText = query;
command.ExecuteNonQuery();
} // comp info
if (fData.holdingInfoValid)
{
string query = @"UPDATE Holding SET ChangeYear='" + fData.changeYear + "', SponDir=" + fData.sponDir + ", Gov=" + fData.gov + ", Inst=" + fData.inst + ", Forgn=" + fData.foreign +
", Public=" + fData.pub + ", ShareCount=" + fData.shareCount +
@" WHERE cID=(SELECT cID FROM Company WHERE Ticker='" + fData.ticker + "')";
command.CommandText = query;
command.ExecuteNonQuery();
}//holdingInfo
if (fData.audFinValid) // audited financials
{
string query = "UPDATE AudFinance SET LoanShortTerm=" + fData.LoanShort + ", LoanLongTerm=" + fData.LoanLong +
@" WHERE cID=(SELECT cID FROM Company WHERE Ticker='" + fData.ticker + "')";
command.CommandText = query;
command.ExecuteNonQuery();
for (int i = 0; i < fData.finYear.Count(); i++)
{
query = @"INSERT OR REPLACE INTO AudFinance(cID, Year, EPS_Orig, EPS_Restated, NAV_Orig, NAV_Restated) VALUES(" +
fData.cID + ",'" + fData.finYear[i] + "'," + fData.EPS_Orig[i] + "," + fData.EPS_Restated[i] + "," + fData.NAV_Orig[i] + "," + fData.NAV_Restated[i] + ")";
//@"WHERE cID=(SELECT cID FROM Company WHERE Ticker='" + fData.ticker+"'";
command.CommandText = query;
command.ExecuteNonQuery();
}
}// aud financials
if (fData.intFinValid) //interim financials
{
string query = "";
if (fData.shareType == ShareType.STOCK)
{
query = @"INSERT OR REPLACE INTO EPS_Perf(cID, Year, Q1, Q2, Half_Yearly, Q3, Annual) VALUES(" +
fData.cID + ",'" + fData.intermYear + "'," + fData.EPS_Q1 + "," + fData.EPS_Q2 + "," + fData.EPS_HalfYearly + "," + fData.EPS_Q3 + "," + fData.EPS_Annual + ")";
// @" WHERE cID=(SELECT cID FROM Company WHERE Ticker='" + fData.ticker + "'";
}
else
{
//query = @"INSERT OR REPLACE INTO MF_Perf(Year, Q1, Q2, Half_Yearly, Q3, Annual) VALUES(" +
// fData.intermYear + "," + fData.EPS_Q1 + "," + fData.EPS_Q2 + "," + fData.EPS_HalfYearly + "," + fData.EPS_Q3 + "," + fData.EPS_Annual + ")" +
// @" WHERE cID=(SELECT cID FROM Company WHERE Ticker='" + fData.ticker + "'";
//TODO
query = @"INSERT OR REPLACE INTO MF_Perf(cID, Year, Quarter, NAV_Market, NAV_Cost) VALUES(" +
fData.cID + ",'" + fData.intermYear + "'," + 1 + "," + 0 + "," + 0 + ")";
//@" WHERE cID=(SELECT cID FROM Company WHERE Ticker='" + fData.ticker + "'";
}
command.CommandText = query;
command.ExecuteNonQuery();
}
}
m_dbConnection.Close();
}
