I'm trying to find the last actual reported quarter and it's accompanying display value. The criteria is the @clientsDescription or @displayName has to EPS. Here's what my JSON string looks like:
{
"?xml": {
"@version": "1.0",
"@encoding": "UTF-8"
},
"DataFeed": {
"@FeedName": "issuerDetails",
"SecurityDetails": {
"Security": {
"@sequence": "850",
"TimesSeriesList": [{
"@description": "EPS",
"@clientsDescription": "EPS",
"FinancialValue": [{
"@displayRank": "850",
"@estimateActual": "Actual",
"@period": "Q1",
"@periodEnd": "2015-9-30T00:00:00.00",
"@displayName": "EPS",
"CurrentValue": {
"@displayValue": "$0.19"
}
}, {
"@displayRank": "850",
"@estimateActual": "Actual",
"@period": "Q2",
"@periodEnd": "2015-12-31T00:00:00.00",
"@displayName": "EPS",
"CurrentValue": {
"@displayValue": "$0.26"
}
}, {
"@displayRank": "850",
"@estimateActual": "Actual",
"@period": "Q3",
"@periodEnd": "2015-3-31T00:00:00.00",
"@displayName": "EPS",
"CurrentValue": {
"@displayValue": "$0.34"
}
}, {
"@displayRank": "850",
"@estimateActual": "Estimate",
"@period": "Q4",
"@periodEnd": "2015-6-30T00:00:00.00",
"@displayName": "EPS",
"CurrentValue": {
"@displayValue": "$0.32"
}
}, {
"@displayRank": "850",
"@estimateActual": "Estimate",
"@period": "Annual",
"@periodEnd": "2015-6-30T00:00:00.00",
"@displayName": "EPS",
"CurrentValue": {
"@displayValue": "$1.11"
}
}
]
}
]
}
}
}
}
Here's what json classes looks like:
public static class JsonExtensions
{
public static IEnumerable<JToken> DescendantsAndSelf(this JToken node)
{
if (node == null)
return Enumerable.Empty<JToken>();
var container = node as JContainer;
if (container != null)
return container.DescendantsAndSelf();
else
return new[] { node };
}
public static IEnumerable<JObject> ObjectsOrSelf(this JToken root)
{
if (root is JObject)
yield return (JObject)root;
else if (root is JContainer)
foreach (var item in ((JContainer)root).Children())
foreach (var child in item.ObjectsOrSelf())
yield return child;
else
yield break;
}
public static IEnumerable<JToken> SingleOrMultiple(this JToken source)
{
if (source == null)
return Enumerable.Empty<JToken>();
IEnumerable<JToken> arr = source as JArray;
return arr ?? new[] { source };
}
}
Since someone asked for the original XML, here it is:
<DataFeed FeedName="issuerDetails">
<CompanyDetails></CompanyDetails>
<SecurityDetails minDisplayYear="2014" maxDisplayYear="2020">
<Security sequence="900" primaryIndicator="No">
<TimesSeriesList description="EPS" clientsDescription="EPS" id="1" qualifier="--" currencyName="CAD" currencySymbol="C$" fiscalId="1" fiscalCalendar="Fiscal" qualifierId="0">
<FinancialValue displayRank="900" estimateActual="Actual" period="Q1" periodEnd="2015-12-31T00:00:00.00" displayName="EPS">
<CurrentValue displayValue="$0.45">0.4502</CurrentValue>
</FinancialValue>
<FinancialValue displayRank="900" estimateActual="Actual" period="Q2" periodEnd="2015-3-31T00:00:00.00" displayName="EPS">
<CurrentValue displayValue="$0.43">0.43</CurrentValue>
</FinancialValue>
<FinancialValue displayRank="900" estimateActual="Actual" period="Q3" periodEnd="2015-6-30T00:00:00.00" displayName="EPS">
<CurrentValue displayValue="$0.64">0.64</CurrentValue>
</FinancialValue>
<FinancialValue displayRank="900" estimateActual="Estimate" period="Q4" periodEnd="2015-9-30T00:00:00.00" displayName="EPS">
<CurrentValue displayValue="$0.52">0.52</CurrentValue>
</FinancialValue>
<FinancialValue displayRank="900" estimateActual="Estimate" period="Annual" periodEnd="2015-9-30T00:00:00.00" displayName="EPS">
<CurrentValue displayValue="$2.03">2.0325</CurrentValue>
</FinancialValue>
<FinancialValue displayRank="900" estimateActual="Estimate" period="Q1" periodEnd="2016-12-31T00:00:00.00" displayName="EPS">
<CurrentValue displayValue="$0.56">0.56</CurrentValue>
</FinancialValue>
</TimesSeriesList>
</Security>
</SecurityDetails>
</DataFeed>
Here's what my code looks like for the JSON feed:
from securityDetail in jsonFeed
.SelectTokens("DataFeed.SecurityDetails.Security.TimesSeriesList")
.SelectMany(i => i.ObjectsOrSelf())
let metric = securityDetail
.SelectToken("@clientDescription")
.SingleOrMultiple()
.Select(t => (string)t)
.ToArray()
where metric.Equals("EPS") && metric != null
let finValues = securityDetail.SelectTokens("FinancialValue")
.SelectMany(d => d.ObjectsOrSelf())
orderby finValues.SelectToken("@periodEnd") descending // <-- getting a error here
orderby finValues.SelectToken("@period") descending
select new
{
LastRptQtr = string.format("{0}{1}",
(string)(finValues
.SelectToken("@periodEnd").FirstOrDefault()).Substring(0, 4),
(string)finValues
.SelectToken("@period").FirstOrDefault()),
DispVal = finValues
.SelectToken("CurrentValue.@displayValue").FirstOrDefault()
}
In the end, I need:
LastRptQtr = "2015Q3"
DispVal = "$0.34"
I'm getting the following error:
'IEnumerable<JObject>' does not contain a definition for 'SelectToken'
and no extension method 'SelectToken' accepting a first argument of
type 'IEnumerable<JObject>' could be found
What am I doing wrong?
Classical XY problem
if all you have is a hammer, everything looks like a nail. You don't need to convert your original xml to json. Working on original xml will be simpler.
Using Linq2Xml + Xpath
var xDoc = XDocument.Parse(xmlstring);
var elements = xDoc.XPathSelectElements("//*[@periodEnd and (@clientsDescription='EPS' or @displayName='EPS')]")
.OrderBy(x => (DateTime)x.Attribute("periodEnd"))
.Select(x=>new {
PeriodEnd = (DateTime)x.Attribute("periodEnd"),
DispVal = (string)x.Element("CurrentValue").Attribute("displayValue")
})
.ToList();