Summary of Open Forum and Breakout Sessions

Tuesday, April 24, 2018 

University of Chicago – Gleacher Center 

450 North Cityfront Plaza Drive 

Chicago, IL 60611

Table of Contents 

Topic Page 

Table of Contents ............................................................................................ 3 

Open Forum Question and Answer Summary ................................................ 5 

NASS Quick Stats / Cornell ESMIS Metadata API Transcript .................... 21 

NASS Quick Stats Presentation .................................................................... 29 

Cornell ESMIS Metadata API Presentation .................................................. 42

Farm Income and Cost of Production Transcript.......................................... 55

Farm Sector Income and Wealth Data Production Presentation .................. 64

Commodity Costs and Returns (CAR) Data Product Presentation .............. 79 

Voluntary and Mandatory Reporting Transcript ...........................................92

Marketing Analysis and Reporting Services (MARS) Transcript ...............101

MARS Presentation .................................................................................... 113

Foreign Production, Trade, and Import/Export Data Transcript ................ 128

NASS Programs .......................................................................................... 137

Appendix A: MARS API to Excel Guide ................................................... 145

2018 Chicago Data Users’ Meeting 

United States Department of Agriculture (USDA), National Agricultural Statistics Service 

April 24, 2018

Introductions and Opening Remarks from agency representatives within USDA and the 

Census Bureau 

Agricultural Marketing Service (AMS) 

Economic Research Service (ERS) 

Foreign Agricultural Service (FAS) 

Farm Service Agency (FSA) 

World Agricultural Outlook Board (WAOB) 

U.S. Census Bureau (Census), Department of Commerce 

National Agricultural Statistics Service (NASS) 

Question & Answer Summary 

Note: The following write-up presents a topical summary of issues discussed during the 

afternoon open forum. Material is presented in chronological order. Questions and answers have 

been lightly edited for clarity and readability. 

Attendee: This question is for Warren regarding the specific price estimate. Are you 

changing the methodology by which you come up with that expected price? 

Second I understood from last fall's Data Users’ Meeting that you would be 

including these probability estimates at the back [of the publication] in some sort 

of a range. Today you said “could”. Does that mean they're not doing that initially 

in the May [WASDE] and you're waiting for a request from users to get that in 

there or you're starting with it and if people don't use it you’re dropping it? 

WAOB: In response to your first question the processes that the Interagency Commodity 

Estimates Committees (ICEC) use to come up with the price projections will be 

the same. What will be different is that we will be posting the point estimate and 

not putting around a distribution with some of that historical information. 

WAOB: Initially with the May WASDE we will not be doing ranges but will hopefully be 

added at a later time. To my understanding it is not something that will be in the 

WASDE but will be on the OCE website. But that is something that still has to be 

worked out. The way we forecast prices is not changing, each committee comes 

up with its own point forecast. What was different among the commodities was 

how we were assigning the ranges and it was providing perhaps a false 

assumption about the probability distributions. By going to the point estimate that 

we were originally forecasting in our meetings, we are simply providing that 

information. 

Attendee: I actually think you're losing information when you do this. I appreciate that often 

in published articles or even a University Extension outlook people are using the 

midpoint, which is inappropriate given the way the range is actually constructed. 
But if you go back and look at the October WASDE for example, at the all wheat

price, I think nine out of ten years or eight out of ten years, by the end of the 

marketing year on May 31, it has fallen between that range. If you get rid of that 

information and you go with the midpoint, I'm concerned, one, about what the 

midpoint means, but more importantly I’m concerned we are going to lose some 

information. That's why I’d like to see at least the probability range in the back of 

WASDE even if you want to report the midpoint on the balance sheet or the 

original range you would have come up with given the different ways that 

different groups come up with their price expectations.

WAOB: This is something of an evolutionary process and our objectives are to provide as 

much good information as possible, but not to provide misleading signals. 

Hopefully as we work forward we will come up with a program or way of doing it 

that’s agreeable to the majority of people.

WAOB: I think the intention particularly on the crop side is to post, not necessarily with 

the balance sheet, but to post probability ranges or just ranges. 

WAOB: We were working on probability ranges and a person at ERS was doing a lot of 

work on that, and then my understanding is that he was detailed to the Council of 

Economic Advisers and that put a hold on things. They’re working on corn, wheat

and soybean probability distributions. 

Attendee: To eliminate the probability ranges, from a research standpoint, starts to degrade 

the past data too. When you look back at past data from when they start, let’s say

in May, and start moving forward with their S&D’s [supply and demand 

estimates], we know where their range is at, we know where their midpoint’s at

we can ultimately tell where they end up. The question then becomes as we move 

through that period of time, what if we go outside of that probability range or 

what if we go to one extreme or another. It's a matter of being able to look back at 

past history and understand there was something similar today compared to, let's 

say, 1973.

Attendee: This is a question for NASS. On behalf of our members we’ve had some concern

over the report release times from livestock reports and particularly with the 3:00 

p.m. Friday release. When there’s multiple reports it makes it very difficult to 

digest and disseminate that information across all market participants. That is 

amplified when you have multiple reports coming out that late in the day. We 

request that NASS consider moving those reports to a Thursday and possibly not 

stacking them up on the same day.

There's also some concerns over the policy on noon release times ahead of 

holidays. Some of our members have expressed that this is an additional challenge 

for them in terms of risk mitigation and the major impacts that has on the market 

when there is a noon release time. Whether it's artificial or imagined or real those 
things do matter and are highly visible at that time. In addition, moving that type 

of report, Cattle on Feed has been brought up multiple times, to a Thursday 

release after hours we believe would fix that problem. We request that you look at 

that in future scheduling.

NASS: We understand that too. In fact, I was talking to somebody from Montana this 

morning concerning the Cattle on Feed releases. NASS tried, on this last calendar,

to take a conscientious look at what’s coming out on Fridays. You'll notice that 

June Acreage and June Grain Stocks are coming out on Friday so we moved the 

Hogs for that time period to Thursday. We've tried to accommodate that so not all 

of those reports are coming out at the same time. Sometimes there's really nothing 

you can do because of the fact that we only have so many data collection days and 

that's what really makes it tough on Cattle on Feed. But that's one thing that we 

are looking into. 

Attendee: When will RMA be releasing the county yield data?

FSA: We get our yield data from NASS and RMA. NASS, I think they start out maybe 

in February with the small grains and we will have them over the next couple of 

months. RMA, we don't get that data until August, so for the 2017 crop, we will 

get that this August. There's some lag between when the producers have to report

it to their agents and the agents have to submit it to RMA. We've worked with 

RMA and August is when we feel the data are almost complete. So we get it in 

August, look at it, and then make that data available in September then we make 

payments in October. 

NASS: Small Grain County Estimates actually come out in December now. Corn and 

soybeans in February and then it kind of rolls from there. 

NASS: One of the challenges with using RMA data is the current lag between when we 

publish and when that data ultimately become available from RMA and through 

their insurance provider.

FSA: We have a big payment cycle in October and that's why we get the RMA data in 

August when it’s virtually complete. We look at it, make sure it's correct and use 

that to make payments the first week in October.

Attendee: This is mostly for AMS but may need to be answered in collaboration with 

NASS. The weekly broiler and slaughter data shows year to date is down slightly,

while NASS data shows it is up more than 2%. These say very different things 

about industry trends. I know the weekly data is preliminary, I'm just wondering 

what can be done to improve accuracy and make sure those numbers line up a 

little bit better. And is there use for the weekly if it's not going to reflect the data 

that's released later by NASS.
AMS: Ours are preliminary estimates and NASS’s would be the actual. We get that this 

is a constant challenge whether it's poultry or cattle or hogs. I hope it's not 

misleading, we don't want that, we do know there's a lag. I think people want an 

estimate now to get an indicator of what the slaughter numbers are. I'm not sure 

what the lag is on the slaughter data for poultry but for other livestock it is two 

weeks, and sometimes that does come in with some slightly different numbers.

NASS: Are you referring to the difference between NASS final poultry data and AMS or 

are you talking about the weekly broiler report?

Attendee: When I talk about the AMS weekly I am talking about the weekly broiler report 

that comes out on Thursday’s at noon. That data has continuously shown year to 

date is down by 1% for broiler slaughter. NASS data has continuously showed up 

over 2%. Those are very different as far as what the industry is doing.

NASS: I think one thing you’ll see, there's a closer following in our livestock data. I think 

there's more sampling done by AMS and a little bit more truing up there. I think 

we do a pretty good job on the poultry side. We looked at this with Mike Sheats a

few years ago and it's a little bit of the way AMS does some of their modeling. I 

know about 10 years ago we noticed a deviation between NASS and AMS and the 

two agencies worked together to true that up. So that's something that we could 

look into, I am glad you pointed that out.

Attendee: Part of the difference is at AMS. They’re using a week to date, so one day is a big 

deal and it makes 2% easily for many weeks. AMS does not start on January 1, 

it's a week so they can have three or four days in there or not in there from one 

year to the next on all their reports. It's not a true year to date, they're using a 

weekly so when you have one day in a month that's 5% difference, so your 2% 

difference is mostly how they're doing it. There are these rules of thumb. They're 

always wrong on weights on hogs and cattle because it's a prior 5 week average. 

When weights are going down they are always too high, when weights are going 

up they are always too low. Most of what you're talking about is just because the 

first full week can have a different number of days every year to the next. Which

is 1% or 2% or 3%, you have to get four or five months into the calendar year 

until this washes out. 

Attendee: I guess the question is how to even use that weekly preliminary data?

Attendee: You adjust for slaughter days for all livestock to a daily average slaughter basis.

Attendee: I'm surprised we're now moving towards RMA data. I put fall yields in for my 

farm and others but RMA and insurance companies can’t give a final number 

until August of next year? 

FSA: It has to do with our reporting requirements. When producers don't have losses, 

they’re not required to report production until the following year when they report
their acres. If they're planting corn in May that's when they report their production 

to an agent if they don't have a loss. In May, June, and July all the data trickles 

into us and that's when we use it.

It can be reported by the producer earlier. When they harvest they can report it to 

their agent and be done with it. But according to the current rules they don’t have 

to report it until they report their acreage data the next year. That can be changed,

I actually think the House Ag Committee is looking at that, so we can get the data 

sooner.

Attendee: Are there any changes in the 2018 methodologies that we should be aware for 

corn yield or soybean yield or anything related to crops specifically? Secondly,

making a request to push the reports out earlier in the month. Finally, the chasm 

between the final spring wheat crop and the conditions last year.

NASS: We'll go in reverse order. The condition versus yield, whether it's spring wheat, 

corn, or whatever, the discussion is the same. Two different processes two 

different things entirely. When you look at crop condition we're talking to roughly 

2300 county extension agents and FSA employees and we're asking them to give 

us their opinion week by week of what the crop looks like in those five condition 

categories. When we go to forecast yield, we’re going to producers and asking 

them to tell us what they expect their yield to be and then we are also putting 

enumerators in fields and they're counting and ultimately weighing portions of 

those fields and getting yields that way. So again, two different processes. Most of 

the time they sync up pretty well sometimes they don't. Last year was a prime 

example for more than one crop.

When we scheduled the crop production reports the mandate that we have is

basically written into law that we have to publish on or before the 12th of each 

month. We try to schedule them as early as we can. You’ll notice that those vary 

month to month depending on the content of those reports. There are two factors 

that we have to consider. One is we need to make sure we have enough time to 

collect the data. The second is we have to make sure we have enough time to 

compile and analyze the data. The bigger the report or the more crops that we're 

forecasting, typically you'll see those dates closer to the 12th than those that don't 

have as many crops to work through. 

And then last but not least, any new methodology for crop forecasting? In general, 

I would say no. Although I would say that we continue to look at the procedures 

that we use. We spent a lot of time looking into different modeling approaches we 

can use to effectively use the same information that we're using now. We've done 

a lot of work in recent years trying to figure out is there a better way to pull that 

together that would make things more transparent for all of you. So instead of me 

saying that's the magic that happens when we get together in the room, we can 

actually point you too a mathematical formula that says this is how all those 
different pieces of information we have get put together to result in that yield at 

the end of the day.

Attendee: From what I understand this is quite a packed situation for you to get together a 

combination of all these different things and to get it in the hands of the 

committee people. It seems like it's going to be very difficult to move earlier [the 

dates of the Crop Production reports]. 

NASS: The forecasts in the Crop Production reports are based on conditions as of the first 

of the month. So that means that we can’t start collecting that data too early. We 

start a couple of days prior to the first normally. Our goal is to try to get that data 

collection centered around the first because if you start collecting it a week before 

the reference period and a hailstorm comes through or weather drastically changes

then your forecast, you might say it's based on conditions as of the first but it 

wouldn't be anymore. We're collecting data up until the fourth or fifth of the 

month. That leaves a real short window to get it out any sooner than we are now.

NASS: On some months we also have remotely sensed yield indications as well and

there's some time period to get that imagery and then the processing associated 

with it. There are some constraints between the eighth of the twelfth getting the 

necessary data collected to be able to come down.

Attendee: How much are you utilizing remote sensing when putting together your crop 

estimates? 

NASS: The way I describe the remote sensing information is that it helps you interpret 

the other data you have. We're not at a point where we are ready to standalone on 

remotely sensed yield information, but as I mentioned, we've got data we're 

getting from producers, we’ve got data we're getting from putting enumerators in 

the fields, it can be really nice to have a third independent set of data that helps as 

well. It's particularly useful when you look at more specific situations that may 

have occurred. It can be really helpful to see how big the impact from some of 

those weather events have been. It's very beneficial but as I've said before, there's 

not a precise number that we put on how we weight each of these together. In fact, 

it varies month to month, crop to crop, state to state.

NASS: I think one thing to understand too is that with remotely sensed yield models 

different crops perform differently and you get different performance across the 

growing period as well. Having data that emanate from a completely different 

source, from the plots in the fields, from farmer reports and then from remotely

sensed data is very valuable. They don't always agree and I think understanding 

what's going on in each of them can help you understand what's going on overall a 

little better and a little quicker.
Attendee: I've got a question for Patrick, you talked about updating the PS&Ds (Production, 

Supply and Disposition) back at the end of 2016, and you don't have any planned

changes or updates coming anytime soon?

FAS: Not for PS&D online. We are looking to address issues that we have with our 

internal PS&D system. PS&D online is where it's going to be for a while but 

we're always open to input and suggestions that might help improve the system.

Attendee: You were talking about putting out a historical database on the WASDE S&Ds. Is 

that going to include the world numbers on the historical S&Ds as well as the 

domestic numbers?

WAOB: It’s a compilation of everything that is published.

Attendee: I have a question on mandatory price reporting for beef. As select production 

continues to decrease and trade volume goes down, is there any way to improve 

price discovery?

AMS: As we see such a high percentage of the cattle grading choice, the choice select

spread is becoming somewhat antiquated. We have been looking at choice/high 

choice spreads as we're seeing a lot more choice cattle. As far as data for select,

the volumes are just not there.

Attendee: This is a question on yield modeling. I heard a lot of questions about using 

imagery as a way to validate some of the survey data. Are you guys looking at 

implementing any machine learning or data science AI tools into USDA’s 

modeling techniques to help manage the data flow and the modeling?

NASS: The remote sensing modeling uses machine learning.

Attendee: I didn't know if you're using it on top of a master model to combine all the 

different data sources.

NASS: We are also using some Bayesian modeling that's looking at combining all the 

data sources as well.

Attendee: Question about the choice/select spread. Can you give us an update on the grading 

changes that happened last fall? There was a little bit of volatility, since then the 

prime percentage has really exploded and I'm not sure if that's because more

plants have started to adopt the new systems.

AMS: There was an issue of cameras, they have made adjustments to normalize that. 

Also with the rule of looking at dentition, I think more cattle grade choice or 

higher that would have previously been ruled ineligible for choice grade by the 

ossification of the chine bones. By using dentition, there's more of these cattle

coming in that grade choice or prime.
Attendee: Since we only get your count data are there any plans to eliminate the ear weight

chart that compares the current month's ear weight to final ear weights of history. 

Is there any way we actually can look at the implied ear weight by month?

NASS: Our big challenge is trying to make sure that folks understand that is an implied 

ear weight. It's a simple mathematical calculation using the published yield and 

those ear counts. It does not say that's the ear weight that we measured, it simply 

says that if the yield ends up being at the level we forecasted and if those ear 

counts hold, that's what it would imply that ear weight to be.

Attendee: That's the suggestion for August, September, etc. you're using final ear weight for 

the history of the regression graphic. Is there any way you can populate them with 

the implied ear weight that was presented at that time. It’s an August report that’s 

reporting an August implied ear weight, so why not compare it to the historic

August implied ear weights.

NASS: We've had that suggestion before and I think it would be a little bit like instead of 

comparing the August yield forecast with what the actual yield was last year, that 

would be the same argument, saying, how does it compare to what we forecasted 

in August last year. I'm not sure there's a lot of value in knowing that, because 

what we forecasted in August last year might not have been anywhere close to 

what the final yield was. Do you want to know how this year compared to last 

year's actual yield or do you want to know how this year compared to what we 

said this time last year? From the folks I've talked to the vast majority would 

rather compare to the actual from the previous years.

Attendee: Has the conservative bias of farm operator yield indications changed notably in 

recent years?

NASS: In general, I'd say no. We've seen a fairly consistent bias in that data over time.

Attendee: This is a Wheat Outlook report question. There's a wheat index in there where

they're taking the crop condition ratings and they're putting it on what looks like a 

400 point scale on the graph. It says, and I quote, “ratings for the very poor are

not included in the index calculation.” We've got five categories of ratings, we’re 

deliberately excluding one, is that because we assume none of it's going to get 

harvested or why are we skipping one of the categories?

ERS: I'd love to take credit for that chart, but I borrowed that off of Brad Rippey who 

actually puts it together. That's their convention, they dropped the very poor. I 

think it is to reduce some of the noise. 

Attendee: I have a question about the March Planting Intentions and June Acreage reports. 

I’m under the impression that the survey samples can be two different sets of 

farmers. Is that correct?
NASS: Yes, there's always some overlap between those two samples, but they are 

independent samples. We do have some efforts to make sure that we have some 

overlap between the two. The sample counts are pretty large. One other difference 

in June is not only do we sample from our list of producers, but we also do our 

area based survey which means we're sampling based on land rather than just 

based on a list of farmers. 

Attendee: What I'm really wondering is, if we can get those methodologies to align and be 

maybe identical. That's essentially the beginning of the crop year is the intentions 

when farmers are going to begin planting and putting it into the ground. 

NASS: We have a structured overlap of operators between quarters of the survey between 

June, September, December, and March. Once you get to June it’s in the ground 

and then you really want to follow it and see if there was a change between June 

and September, or was there a change between June and December what the same 

producer said. So there is a choice to be made because you also have to worry 

about respondent burden and keeping the same folks in the sample for long 

periods of time is also problematic. 

Attendee: In the NASS recap for crops and stocks reports they’ve started to put in the trade 

ranges and average trade guess. I was wondering about the thinking behind that.

Do you think that has influenced your process of coming up with estimates?

NASS: We don't look at those numbers before we set the estimates. Lance is not looking 

at that until the morning when they're putting the briefing together. We don't want 

to be biased by anything.

NASS: The only change is that we started including them in the version we post out on 

the web. We've had those charts in there for the Secretary for some time so 

nothing's changed other than we're trying to be a little bit more transparent with 

what we're sharing.

NASS: We’ve also changed the look a little bit this last season. We wanted to try to show 

with the crop items over the forecast months where we've been, the changes we've 

made as we move through the months and also where the market analysts were as 

well. I think last year was an interesting picture when a lot of folks thought we 

were too high at least on corn. 

WAOB: One point that I think is important is that the packet that's put together is for the 

Secretary's briefing or more often it's the Secretary’s designate. That person wants 

to know what's going to be a surprise. It’s a briefing for somebody who's not 

necessarily following tick by tick what's happening in the market. That’s one 

reason that information is valuable is to give a signal to the Office of the 

Secretary what’s going to be a surprise in this release.
Attendee: It seemed like a year, maybe two years ago there was a structural shift in what got 

reported for wheat as cover crop, especially in the soft red winter wheat states. 

Just curious if there might be an error there where we’re getting an overestimation 

of wheat acres that actually were planted for cover crop. There have been quite a 

few states that have been incentivizing producers to plant a crop that does not get 

harvested for grain that actually is truly a cover crop, but it seems like that is 

going into an assessment as a wheat acre.

NASS: There hasn't been any change. NASS's position has always been if you plant a 

particular crop it is included in our planted acreage estimate. If it’s strictly for 

cover or if it fails for any other reason it's not going to be represented in the 

harvest acreage total. I know there have been some changes in how cover crops

get reported through FSA so if you're comparing to some of those certified 

acreage numbers, you will see some shifting there because of how that data is 

being picked up.

Attendee: When you talk about the remote imaging is that the NDVI index?

NASS: We use the NDVI model.

Attendee: Exclusively?

NASS: We have a remote sensing yield program and a remote sensing acreage program. 

We're using some other sensors for other purposes, especially when there's been a 

disaster. I don't want to be too closed in our answer but predominantly for yield 

it's an NDVI model.

Attendee: This is a question for NASS. Since agriculture has changed quite a bit, especially 

in the past few years, do you have any intention of trying to capture seed traits or 

seed treatments specifically for the chemical use survey?

NASS: Between us and our partners at ERS the ARMS program would be the best avenue 

that we have for tracking those trends. What we're going to do typically is find 

those in the ARMS Phase Two, which is the chemical use program that looks at 

field crops. We do meet on an ongoing basis with ERS staff and the internal staff 

in my branch to make sure that we capture those trends.

Attendee: By remote sensing, I think a lot of people might be referring to work that

Descartes Labs is doing. Are you guys looking at those type of products and that

type of methodology on an internal basis at all?

NASS: We've met with both Tellus and Descartes in the last 18 months. I don't want to 

comment on exactly what they're doing since then, they may have introduced 

some technology, but the remote sensing community is not so huge and our folks 

are certainly involved in that industry and a discussion of different procedures. 

We’ve published our methodology, there may be a few tweaks to it since it was 
last published but it lays out pretty clearly our procedures for yield estimation 

from remote sensing.

Attendee: I just wanted to add to that on the foreign analysis side, if you're interested in that,

we are also following Descartes Labs and Tellus and these organizations.

Descartes Labs has a small business innovative research grant that they're 

working with DARPA, which is a Defense Agency, looking at forecasting yields 

using their machine learning algorithms. 

Attendee: Last question for the time being on the NASS Hatchery report. The eggs set and 

chicks placed are reported as well as a hatchability number that's given as a 

percentage rounded to the nearest percent. As far as I know the hatchability data 

is not able to be downloaded and the only way I've ever been able to get ahold of 

it is through that rounded number at the top of the report. I was wondering if 

there's any way to get that more exact, to get it historically, and to get a chicks 

hatched as opposed to just chicks placed.

NASS: The hatchability is not loaded to Quick Stats. It's done internally in our 

publication on a weekly basis.

Attendee: The LS196, which is a national grading percent report is great, however, it's 

voluntary from what I understand. We have the fed cattle comprehensive that 

shows the weekly choice plus percentage. From a CME standpoint, if we're going 

to make changes to our live cattle contracts as far as what the par value of a 

contract is, recently we went from 60/40, now it's going to be 65/35, and we still 

have to evaluate if we need to bring that up and or if we can do a mechanism that 

automatically adjusts but we would need a mandatory number. I believe in the 

Fed Cattle Comprehensive that choice plus number is mandatory data. How could 

we get, a mandatory choice, a mandatory prime to go with a select number so that 

we can use that and not rely on something that, although it's a great report, the

LS196 is voluntary?

AMS: The LS196 data we get from another part of our program, from the Quality 

Assessment Division. The mandatory data, we are using that is coming from all 

the formula cattle purchase types, formula nets. That data is from those plants that 

are in LMR (Livestock Mandatory Reporting) so plants that slaughter 125,000 or 

more per year. The grading data, presumably, is from wherever they get grading

services provided.

AMS: The voluntary report has a significant lag because we use a sister division to get 

that data. The idea with a comprehensive is to be more timely. That's mandatory 

data in the comprehensive and its net data. As with anything with that 

comprehensive it's very fluid, if you have something specific we certainly are 

open to suggestions. On the comprehensive the idea was to be more timely, it's 

net data for the previous week as opposed to, not quite a two week delay on the on 

the voluntary side.
Attendee: First of all, the mandatory number is great. If you could separate the choice from 

the prime that would be a huge help. I think it was mentioned earlier about the 

prime, it is dentition and it is the cameras but really that dentition that's really 

done the trick here. We have packer feedback that's telling us that if they were 

killing 70% at this time last year, they're killing 75% today. That's dramatic and 

it's never happened in their history and they tell me this is the new normal. 

When we look at it from our contract standpoint we become a bit of a discount 

contract and our lag that we're trying to keep up. Our goal is once a year, we take 

a number agreed upon and we go forward with it and that's our new number. The 

74%-75% of the mandatory number that we get in our kill works really well. If we 

could separate out the choice from the prime it allows our job to be a little easier.

That would be an excellent approach for us from a CFTC standpoint and from our 

own market regulatory standpoint, they really want to see a mandatory number, 

they're worried about a voluntary number, they'll push back at us. If we could get 

a mandatory choice number off the weekly comprehensive and three years of that 

data that would allow us to put a number out there that, I think even the feeding 

industry is with us on this, that if we could set it on autopilot and it adjusts itself 

every year we don't have that lag that we we've had on our contract.

AMS: That's good comments. We will take a look at it and if we can make it happen, 

we'll make it happen.

Attendee: Is there any plans at NASS to potentially release their implication of the quarterly 

by class wheat stocks? In the last two years we've had a really hard time 

reconciling the by class wheat balance sheet changes based on a breakdown of 

those stocks. I realize that's not necessarily data that's getting reported but very 

important for the by class wheat balance sheet breakdowns to understand those 

quarterly numbers.

NASS: We only estimate and publish an all wheat stocks number and a durum wheat 

stocks number. Internally we don't even necessarily break that down. It's very 

difficult to collect stocks broken down by type like that. That's why we focused 

our efforts on the all wheat stocks. Durum is completely different, it's pretty easy 

to separate that out.

Attendee: I’ll put that same question to the World Ag Outlook Board because that’s who is 

publishing the by class balance sheet. 

WAOB: I have an answer to the previous wheat condition question. It's an arbitrary index 

and the very poor is not excluded from the calculation of the index, it’s given a 

value of zero. 
WAOB: This is a really small group that does this but it is really important. I just started 

doing it myself. It's hard, some classes of wheat are easier, but SRW is difficult, 

spring wheat is difficult, white wheat is a little easier.

Attendee: The by class wheat balance sheet changes every month and they change a lot after

a quarterly stocks numbers. So an input for the World Outlook Board is clearly 

the quarterly stocks numbers. Is there any guidance to the rest of us that consume 

the data what that's implying?

ERS: That's one of the most challenging aspects to estimate, wheat by stocks by class. 

We do have a process, we begin by looking at what wheat is where and looking at 

historical proportions. When we start putting together a preliminary balance sheet 

by class we're looking at where has wheat been historically and we iterate from 

there as we get more information. It's not any sort of intention to do big swings, 

but these are dynamic markets and things change from year to year, demand 

changes, quality changes. There's many different things that can make demand for 

product and disappearance that's implied by those quarterly stocks figures pretty 

different from year to year, but the previous year is a good source of information

and we move from there. I have talked to Bill before about publishing wheat by 

stocks or some of our quarterly by stocks data prior to when it comes out in June. 

The reason that we hold off on publishing it any sooner is that we have to wait for 

the last months of data from NASS from the Flour Milling Products reports so we 

can final out those food use figures. I don't see us publishing any earlier than that 

or doing anything in the interim. We do some annual by class figures and I do 

discuss some of the changes in the Wheat Outlook. 

Attendee: This is a question that we started to discuss back in Kansas City involving the 

whole structure of the WASDE sheet. Why is Russia not listed as an exporter for 

corn and wheat?

WAOB: In May we are putting Russia and Ukraine in the major wheat exporters. We're 

also including a line, for wheat and rice. Because China's the leading wheat and

rice producer and largest holder of stocks, we’re including a line below the world 

line for world less China. Reflective of current market realities, we put Russia and 

Ukraine as major exports to join the other traditional five Argentina, Australia, 

Canada, EU and U.S.

Attendee: When you look at the soybean industry around the world, we've had a 

monumental shift over the last 20 – 25 years. Today we have a new harvest every 

six months and that has really changed the parameters and dynamics of how you 

look at the soybean industry. Is there anybody, whether it's in WASDE or whether 

it's in ERS, have they taken a look at putting together a six month S&D of the 

major exporters of soybeans? The people in the oil world have put out some six 

month numbers for probably 20-25 years now and it really is important because 

you start to see the availability in that short time frame.
ERS: Something we haven't thought about but I can take it back and talk to our soybean 

analyst about it. I think it's a good idea.

Attendee: I have a question about the WASDE. When you are looking at the export number

what guides it more, is it the shipments or the sales out of the U.S.?

WAOB: It depends on which tables we're looking at. In the U.S. balance sheet we're 

pulling that from the customs data we’re getting through GTIS (Global Trade 

Information Services). That’s going to be shipments on our stuff going out, but 

then we are looking at major importers and exporters to make sure that numbers 

coming in and numbers coming out are truing up to hone in on which ones we 

think are more accurate.

WAOB: It depends on the commodity. Each committee is going to handle it a little bit 

differently. On the livestock side we look at the weekly sales data as an indicator. 

The monthly data that we get from the Census is the official data. You could use 

it as a marker, but you're never going to get the two of them to ever lineup 

precisely. You have to look at what's going on in the context of the world, who 

are we competing against, what countries are they going to and then you create a 

balance to try and do your forecast based upon all the available information what 

has historically been shipped, what the weekly data is telling you, and what you 

think or know is going on in the rest of the world as well.

FAS: The time of the year makes a huge difference. If you're early in the season, you

don't have much or any shipment data to work with so you're looking at sales, 

you're looking at prior years, you're looking at the rest of the world and what 

suppliers are out there, what their crops are doing and things like that as well.

Attendee: You’re also looking at the changes around the world and you're making your 

updates. This year we didn't have a lot of export sales or a lot of shipments until 

we figured out that Argentina’s crop was gone. So now all of a sudden, we've got 

people buying U.S. and that changes your exports. It was export sales that really 

started the momentum of change and I think every market is different at different 

times of the year. In particular, right now, with wheat, hopefully we can get 25 

million a week to get us to the end, but most likely not.

Attendee: I have a question about page 28 in the WASDE the World Soybean Supply and 

Demand. I'm curious as to what is the benefit of shifting South American 

soybeans stocks to an October through September year? The effect that I've seen 

is that this causes people to believe that at the end of Brazil and Argentina’s 

marketing year that they have this huge supply of soybeans leftover when Brazil's 

going to have hardly a soybean leftover after their actual marketing year. We were 

talking about the wheat situation with China and now that we're going to show 

how the world supply looks without China. Well, it's kind of a similar situation 

with soybeans. Maybe the fine print should be larger because not enough people 

are noticing this. 
WAOB: The comment about the historical precedent and going to a six months S&D, I 

don't know that it's something that we've really taken under serious consideration, 

but it's definitely feedback to be thinking about because I know that it has been a 

problem for a long time. Our marketing year and South America's marketing year

don't match up. It’s an artificial cutoff point. You get the monthly balance sheets 

and that’s more relevant as opposed to trying to bring it all together in terms of 

one marketing year. You've got questions then as crop years change, whether its 

climate, varieties, production practices do we start moving that around? At some 

point you have to put some markers down but these are points well taken. It’s

always been a domestic focus from a U.S. producer, U.S. centric viewpoint. But 

maybe a six month could be one way to look at. 

Attendee: Thanks for your response. I understand that it's difficult based on totally opposite 

cycles, that's just the nature of what we're dealing with. But I think that this year

this is an especially big deal because now we're looking at Argentina running out 

of soybeans, we're looking at China trying to avoid the U.S. and so people looking 

at the balance sheet are seeing there's 90 million tons of soybeans left over at the 

end of this marketing year, plenty for China but you know Brazil doesn't have 36 

million they have maybe a million or less. I don't know if it's just making it more 

obvious in the text or making that fine print more clear but people just continue to 

not read that and not understand that there is not that excess supply.

Attendee: When you go in to the PS&D, I've had people ask me when you look at the 

soybeans what is this local marketing year for Brazil and Argentina. You list them 

over there but you also have everything adjusted to a U.S. marketing year as well 

which I think we have to look at if we're going to use a yearly S&D. What you are

going to do in corn that's becoming an issue as well, Brazil, in particular, they're 

becoming a bigger competitor. Again, we're moving so much production into the 

southern hemisphere that we didn't have 20 years ago that is really creating a lot 

of unsettlement when you look at some of these bigger S&D pictures. Maybe in 

looking at South America, you need to start looking comprehensive, in terms of 

soybeans, but maybe corn too and figure out how do we rectify this and put it out 

in a clearer format.

FAS: I was going to highlight the fact that we do have soybean information in the 

PS&D on a local marketing year basis. The global S&D is aggregated on an offset 

basis to try to account for everything. I have to take this back and the committee's

will discuss this issue with the growing production in South American and see if 

there's a solution.

Attendee: I have two questions. On the export sales for switched destinations, there's a 

report that shows the data, but it's only for the last five weeks. I was wondering if 

there's a longer history of that. Second, there was a meeting in Kansas City about

three weeks ago and there's a question about the WASDE release times, I believe 
it was in the CFTC section of the meeting that suggested that the WASDE should

not be released during trading hours. 

WAOB: When they do a noon Lockup that was a change from when it was the morning 

releases and I don't know that there's been any serious discussion of looking at 

that but I hadn’t heard that feedback from CFTC either.

Attendee: Professor Charles John from Columbia University said at the CFTC meeting that 

there was reason at times to have a pause in activity and markets, it was valuable

and it was good. I talked to Andy Bush afterwards. He's one of the directors here 

in Chicago of part of their divisions. The problem we have every time that the 

NASS report was changed, the CME changed their trading hours and so I told 

Andy if you really want to have a pause, we've tried to have a bottom up, push, 

it's got to come from the top down. You’ve got to have the regulators say cease 

and desist.

FAS: On your export sales question there is an online system to access export sales 

data. I’d checked there first. If the historical information on the change in 

shipments is not there, I'll be happy to research and see what I can find out and 

get back to you.
2018 Chicago Data Users’ Meeting 

Session: NASS Quick Stats and Cornell ESMIS Metadata API 

Presenters: Rich Holcomb, National Agricultural Statistics Service, Tahir Poduska –

Cornell University, John Fereira – Cornell University 

Note: The following write-up presents a full transcription of the session, it been lightly edited 

for clarity and readability. 

Rich Holcomb 

Hello everyone, my name is Rich Holcomb. I'm going to give a quick overview of where we are 

with Quick Stats, end of the story is nothing has changed. We are in the process of rethinking 

this but we're not quite to the point of killing it and burning it as Jim thinks we should do. I'm 

here with Jonathan Straight who can help answer any questions we have. If you have any 

specific questions please find us after the session or during the question time, we're more than 

happy to sit down and work with you on being able to use Quick Stats. 

There are three primary ways of using Quick Stats. Quick Stats Lite, that's our structured or 

curated view of Quick Stats. The Quick Stats database itself and its web interface was the 

original release, it can be daunting for a lot of data users. Quick Stats Lite was our attempt to 

streamline the process and a few years ago we added an API which I'm going to briefly talk 

about today. 

There are several ways to get to Quick Stats through the website. Click on the Data & Statistics 

tab up on the far left and then it'll take you to a page to access Quick Stats. From this page you 

can access Quick Stats or Quick Stats Lite. Quick Stats Lite is similar to Quick Stats, you pick a 

sector, a group, and a commodity and a view. For these you can only make one selection. Then 

for year and location you can make multiple selections by holding the control key and clicking 

multiple selections or shift and click to select multiples. Once you have a location selected the 

Get Data button will be enabled, you can't do anything until you have a location. This is the 

result for corn grain stocks. You can download that data to a CSV or you can continue the 

analysis in the full version of Quick Stats. Quick Stats has a little bit better spreadsheet interface, 

a little more dynamic so it'll just transfer to there if you want. That's Quick Stats Lite. 

Quick Stats is the original release very, similar to Quick Stats Lite. The important point here is 

that you can start anywhere. This is an interface to our 34 million records. In the upper top right 

corner is the actual number of numbers in there. The purpose of this interface is to narrow down 

your query to a manageable size. We do have a limit, you can only return 50,000 records. It is 

dynamic, if you select a state, for example New Jersey, it will only show you variables for what's 

available in New Jersey. You'll also see the number of records come down considerably too. You 
can start anywhere, typically you would start at the top left and then work down to geographic 

level and time. Just a tip between the very first box between census and survey, that's a key 

distinction. Generally I think people in here would be more interested in the survey data.

The point that I want to make here is you don't need to make a selection for every box. For the

data item field, you see three items there. Those by default will be included in your query the 

others where you have one selected, that's the limiting selection so that's the only thing you'll get 

there. In this instance for corn stocks, you only need to limit by corn as the commodity and 

stocks as the category and the year and location. Couple things about the interface here, you can 

make multiple selections on any of these unlike Quick Stats Lite. 

Once we're down below 50,000 records, when you return data, by default, it’s going to be one 

single item per row. It's difficult to use until you pivot it and you do that by right clicking on the 

variable that you want to string across the top. In this case, I would recommend data item there’s

three different variables and those three variables will be across the top with your locations and 

time on the left. This is the pivoted table, it's a tabular format where you've got three got total 

corn stocks, on farm, and off farm grain stocks those are the three main column variables. You 

also have four quarters, worth of data and then all states and the U.S. When I first executed the 

query, it's one long column of numbers and I pivoted on the data item to get the stocks displaying 

in a tabular format. Any more questions?

Attendee: Is the C.V. percent column ever populated?

NASS: For our Census data it is.

Attendee: Does the Quick Stats Lite also have a 50,000 row limit?

NASS: Yes, it does and the API.

Attendee: When you jumped from Quick Stats Lite to this you didn’t go back and show 

what your clicks were on the homepage to get to that. Could you do that?

NASS: Sure, it's just a different arrow, it would be the top arrow. Quick Stats actually has 

its own url. It's a separate web server so you can go directly to it with 

https://quickstats.nass.usda.gov.

Attendee: You did one pivot, can you do multiple pivots? After you pivot once like you did 

to distribute the stocks number, you can go back and pivot on an individual state, 

or something like that?
NASS: If you pivoted by state you would get the three different stock numbers and then 

for each state spread out. Does that make sense? So you get Alabama total corn 

stocks, off farm and on farm and then sequential.

Attendee: After you have a pivot like this and you’ve distributed the different stocks 

numbers out in separate columns, can I come back and do another pivot on the 

states? 

NASS: If you want to filter, that's another really good functionality here. If you want 

Kansas, all you need to do is double click on Kansas and that's all you'll see. 

There's a breadcrumb in the top left corner. Every time you make any kind of 

action, there'll be another breadcrumb. If you filter by Kansas, just click back and 

it'll return everything to you.

The you save the query url, you can execute it at any time and it will pull current numbers. There 

are some links on the top right corner to save the query and that will create a link on your 

desktop with the query, or the spreadsheet option saves it as a CSV file. You can change the 

query url to create a custom query. 

There is another tab for developers. That's the page that has the information about the API. In 

order to use the API, you'll have to read and accept our terms of service and request a key and 

it'll email you a key that's unique to every person. We ask that you attribute the data to us and 

that you don't misrepresent it. You can change the query url to create a custom query. One of the 

benefits of this, it's kind of a quasi API functionality, on the interface you can only click on a 

button and make it a discrete request. For example, a year would be 2017 or 2018 this one you 

can actually code to allow a greater than or equal to or a “like” or other SQL functions. More 

functionality is if you put a question mark that commodity description is actually one of the table 

names so I'm saying “corn”, this statisticat description is another table that is set equal to 

“stocks” and year is greater than equal to 2017. The point of doing this is that if you save this 

query it'll be good forever because every quarter when we release numbers, you'll get that corn 

number. Another important thing that we don't talk enough about is the UUID that's what we call 

that big number. You can actually decode that so if you look at the URL, if you replace results 

with UUID/decode it will give you most of that SQL.

Attendee: What’s the lag time between a report being released and being available?

NASS: That depends upon the release, generally it'll be within three to four minutes. 

When we have our lockups with the WASDE and our Crop Production that data is 

actually loaded during the lock up so it should be available almost immediately 

after lock up.
Attendee: Have you done any load testing with your API?

NASS: We do get a lot of usage. Last year we had over 13 million queries submitted over 

the year. We don't have any metrics of the changing performance. We do have 

some videos from the Help button on the top right corner. The bottom link is to 

download the whole file, they're updated daily almost every day for the current 

data. 

Attendee: When are they updated?

NASS: They are updated every morning Tuesday through Saturday so that we pick up the 

Monday through Friday releases.

Attendee: Is there a lag time between Quick Stats appearing and the publication?

NASS: The hard copy should be much quicker there is a little delay on the database.

Attendee: Is there any reason for the delay? Sometimes it takes up to 8 minutes.

NASS: It depends on how much data is being loaded. The problem is we don't start 

loading, other than our crop production, we don't start loading until the data are 

released. It can take a few minutes. If there are multiple reports stacked up on that 

same release day and time that can impact the speed in which the data get out 

there as well.

My questions are how do you consume our data now? Do you get it from the report, do you use 

Quick Stats, or do you use it from a third party or some other service? The other question is, how

do you want to consume our data? Are you interested in an automated give and how would you 

define what it gives you or do you want a visualization? Do you want a bit that you get on your 

mobile phone, those are those are questions that we're dealing with. If you have any ideas, 

please feel free to let us know. 

Tahir Poduska

Hello everyone, my name is Tahir Poduska and today co-presenting with me is John Fereira, we 

both are developers at Cornell University and want to thank our partners at the USDA for 

inviting us here to do this session today. It is a bit of a technical focus on the presentation, but I'll 

still try to keep it more open so that everybody can have a takeaway from the presentation. We 

also want to get some feedback about how we can improve our website and the services we have 

so feel free to give us that feedback, either in person or through email.
A bit of a history behind the project. It's been a long term partnership that we have had with the 

USDA that started in the early 90s and the current website that you folks might be aware of has 

been up for 10 years now and we host more than 1900 publications from five agencies that you 

see on the right side there. Close to 60,000 users are subscribed to receive email notifications on 

the website. Besides hosting the website and sending the emails, Mann Library at Cornell also 

provides help desk service that you can contact us through email and phone or also in person. We 

are a land grant library so you can come and stop by anytime and we do answer your nontechnical questions on a regular basis.

Progress has been made on the new website that we have been working on for more than a year 

now. It's a major upgrade to both the front end and also the infrastructure both the software and 

hosting system. I'll outline the technical infrastructure, but feel free to ask questions if you need 

information. We are still developing the system and we'll be doing a lot of data migration in the 

next few months to move all the publications and release data from the old site to the new site. 

We are going to be asking for volunteers for beta testing of the new website sometime in early 

summer, you'll see you that notification on the existing site and we are hoping to go live 

sometime this fall. 

In terms of improvements, the search functionality is something that you'll notice has been 

improved a lot, including a lot of filters for keywords and subject areas. We've worked together 

with our agencies and also with the librarians at Cornell to consolidate some of the metadata that 

we have across the five agencies that we work with so you can find the reports easily using 

simple keywords or search terms. We are also improving our email delivery system from our 

existing Cornell based system to an external vendor and we are improving the subscription 

features that we know that people use a lot. That will be highly improved, you can self-register,

subscribe, unsubscribe and you'll be able to download files that you want directly through your 

own dashboard instead of having to go through the emails or go to the website. 

One thing, that is the topic of this presentation, we are going to offer for the new website is an 

API for accessing the metadata. Partly because we realized that a lot of people hit our website

especially around some of these high volume reports and people have created these bots or 

spiders to crawl the website and we see a huge uptick in the requests and it's a much more costly 

transaction for us and on our servers. We are providing an API for people who want to 

implement this in their workflow. Instead of having to go directly to the website, they can write a 

program or a client on their business side and have a workflow setup so whenever they get a 

notification or an email from our system they can hit that URL and get the get the files 

downloaded directly through the program instead of having to go through the website.

A brief introduction of the data model, we are using an open source software called Samvera. A

collection of more than 16 different universities across the United States and some museums are 
working on this repository software to archive and host all of the digital products that people 

produce. In our case we are dividing the metadata, organizing the reports and agencies in a 

hierarchical manner. Some of the agencies will be using subdivisions, for example, AMS will 

have multiple divisions within their agency producing different publications and the publications 

have their own metadata, whether it's keywords or subjects or upcoming releases, how often this 

is released and then the publication has been released, whether it's like a monthly or weekly or 

daily release and this is when you'll download your files. This is the structure that the API 

follows as well. We'll have proper documentation in a couple of places. We are using a new tool 

called Swagger which is a visual tool for people to interact with the API in an easy way, it also 

provides an easy documentation to the system. We'll also have a GitHub repository for some of 

the documentation as well. We will also provide some example code base for you to interact with 

the API and also share some additional documentation. You can use GitHub to create user issues 

and give us feedback on how we can improve the system.

John Fereira

Swagger is a neat way for documenting an API. If you be stick around for the presentation on 

MARS, they do something similar was something called Postman, which also documents their 

API and provides a mechanism for trying out the API without actually having to develop the 

client and so we started with the documentation and then worked from the documentation to the 

implementation of the API itself. This is an example of the endpoints, you can see the three of 

them up at the top there as get endpoints. You can find metadata about all the agencies, find 

metadata about a specific agency by giving its acronym, AMS, NASS, ERS, or by an ID. This is 

an internal identifier and these find by ID endpoints are probably only useful once you're actually 

developing code and developing a client and able to pick off the ID from a previous API request. 

As an example, in the API and the documentation for find by acronym you would see that it 

would render a form and allows you to select from that list to enter one of the parameters that 

you would be sending to the API. This is an example response, you can see this is also in JSON 

and it'll show some of the fields that you may get back. For example, you can see the title there 

and a few of the other fields that you would expect in the response. The API also documents all 

of the error conditions and what those responses would look like.

These are the endpoints for publication metadata. Find all, find by agency, so you can get a list 

of all publications by agency. If you select the World Outlook Board, you're going to get two. If 

you select AMS, you're going to get about 1600. Find by identifier is very useful if you already 

know what the identifier is. The publication, we've been using for many, many years as the 

example is Poultry Slaughter, you would do find by identifier “poul slau” and it would get all the 

metadata. The parameter specification provides a form element, you just type in the name of the 

identifier. This is the example of the response, you can see this is essentially our data model for a 

publication. These are all the fields that we've collected. Most of this has been migrated from the 
legacy data on our old USDA system. There's been some data cleanup during that process, some 

normalization of some of the fields to make a little bit more sense. But essentially that is all the

data and those fields that you could then use to render a page. For example, if you were looking 

for poul slaughter, you would get the full title, the full description some contact information 

various things that you could put up on a page from the actual data from the site.

Then we have the same thing for releases for every publication, whether it's weekly, daily, 

monthly. In some cases, these are one off reports and they have just one release. This is the same 

idea where you provide the identifier in a form, you can select a date range or at the end of that 

URL you add latest=TRUE, it will give you the latest release. This is the example of the 

response, up at the top you see that array of files names, those are all the files that are made up of 

the release. If you created a link from one of those URLs, you would actually allow someone to 

download the actual file from your page.

This is the team that’s involved with the development of this new website. It's quite a number of 

people starting with Mary Ochs our director, the librarian and everybody else that's been 

involved with the project. So that’s about it, we can open it up for questions.

Attendee: Is there going to be overlap between Quick Stats and this API? Is this returning 

files or data? 

Cornell: It returns different types of information, it will actually return a copy of the 

publication. For example, poultry slaughter includes three files. It includes a text 

version of the publication, it includes a PDF version and it includes a zip file 

which contains all the tables. So if you downloaded that zip file, you would get 

that same data. 

Attendee: Would the data be in a JSON format?

Cornell: The JSON format will be the link to the file you would then have to download 

that file and extract the contents in order to make use of it.

Attendee: Is the table file a CSV?

Cornell: It's a zip file containing the CSV files and it also has an index which has the name

of each of those tables. 

Attendee: Are you guys going to go back historically and convert some PDF’s to CSV’s? I 

know if you go back far enough, some reports are only PDFs. 
Cornell: A lot of reports are very old and a lot of the very old PDFs are basically scanned 

paper copies. We don't create any data, all the data we get is from the agencies, 

are we archive and disseminate it.

Attendee: It outputs html code that I can use for a website?

Cornell: Not quite.

Attendee: Secondly, and this would be easier because most of us in the room are not coders, 

but can you put it together. For instance, supply and demand on the monthly 

reports would be an embeddable HTML code that we could drop into our 

websites in some form that would dynamically update so we don't have to cut and 

paste. It would be really nice to be able to pull that off. 

Cornell: I've done this sort of thing, it would be specific report by report.

Attendee: I was thinking of the supply and demand report, it’s there, it has all of the 

numbers, we don’t need you to change it, we just need to be able to publish it at 

11:00 am on report day and it just changes automatically. 

Cornell: We created an open repository on GitHub that we're going to have all of our 

examples. And so as we see these sort of use cases we can create those examples 

and post them. You may have to modify them the bit but that's the HTML code, 

you probably say okay, I want to have a report that shows me the latest release,

the title, the description, contact information as a single web page and will always 

render that latest copy.
Quick Stats 

RICH HOLCOMB / JONATHAN STRAIGHT

DATA USERS’ MEETING

APRIL 24, 2018
Objective: 

Discuss methods to access NASS data

Quick Stats Lite

Quick Stats

Quick Stats API

https://www.nass.usda.gov/Quick_Stats/Lite/index.php

https://quickstats.nass.usda.gov/

1. What, Where, When – start anywhere

2. Each selection limits results

3. Select and move pointer out of box to update

4. Maximum of 50,000 records returned

5. Get Data button enabled when limited to 50,000 records

Pivot by Data 

Items to display 

in tabular format


Web Interface Saved Query (2017-2018 Corn Stocks):

https://quickstats.nass.usda.gov/results/50158376-9AA2-39FA-AF24-852FBDFA499D

Web Interface Saved Pivoted Query:

https://quickstats.nass.usda.gov/results/50158376-9AA2-39FA-AF24-852FBDFA499D#C88DDD2E-FC15-33F2-B4AC9D6CD18BDD8F

Web Interface ‘Enhanced’ Selection (year >= 2017 Corn Stocks):

https://quickstats.nass.usda.gov/?commodity_desc=CORN&statisticcat_desc=STOCKS&year__GE=2017

Web Interface ‘Enhanced’ Saved Pivoted Query:

https://quickstats.nass.usda.gov/results/86C34046-AA53-3FFE-A999-4A06E2C90612#6A940A5F-DA4C-3C90-8209-

FF8C56A3C5AA

*tip: replace /results/ with /uuid/decode/ to help understand query:

https://quickstats.nass.usda.gov/uuid/decode/86C34046-AA53-3FFE-A999-4A06E2C90612#6A940A5F-DA4C-3C90-

8209-FF8C56A3C5AA

API (year >= 2017 Corn Stocks):

https://quickstats.nass.usda.gov/api/api_GET/?key=C65F7136-C640-323E-AF08-

FF576C0C678E&commodity_desc=CORN&year__GE=2017&statisticcat_desc=STOCKS&format=JSON

We are currently exploring next-generation Quick Stats options

• How do you consume NASS data?

• Report

• Quick Stats/API

• Non-NASS Product or Service

• How would you like to consume data?

• Quick Stats (interactive)

• API (automated)

• Highlight file (Text or JSON)

• Visualization

• Mobile app

• Feedback

• https://www.nass.usda.gov/Quick_Stats/Feedback/index.php
Tahir Poduska & John Fereira (Cornell University)

2018 Data Users' Meeting - Chicago, IL

April 24, 2018

USDA ESMIS Metadata API
USDA ESMIS Website

• Long term partnership between 5 

USDA agencies and Mann Library, 

Cornell University

• Hosts current and historic 

publications and releases

o ~1900 publications and their 

current and historical releases

• User Subscription and email 

notification for new releases

o ~58,000 subscribers, more than 

a million email notification per 

month

• Mann Library provides email and 

phone support to website users
New Features:

• Efficient and improved search

• Improved user experience

• Improved User dashboard with 

subscription 

management and flexible file 

download options.

• Better email service (self registration, 

subscription/unsubscription)

• Improved metadata for agencies, 

publications and releases

• New API for access to agency, 

publication, and release metadata

and file downloads.

Modern Infrastructure:

• Based on Hyrax, Samvera open source 

software

• Fedora repository, Solr search indexes 

and Blacklight interface

• Amazon Web Services infrastructure

New USDA ESMIS Website

Timeline

Data Migration (Late Spring 2018)

Beta Release (Early Summer 2018)

Live (Fall 2018) Sign up for beta testing

usda-help@cornell.edu
Data Model

• Portland Commons Data Model 

(PCDM)

• Follows Ag Data Commons guidelines 

for Metadata Schema

• Accessible through the website and 

the API

Metadata Improvements

• Improved the existing metadata fields

• Consolidated the metadata from 5 

agencies to provide a better search 

experience to users

ESMIS Data Model and Terminology

Agency

Division

Publication

Release

Files
• Public GitHub Repository for API 

documentation, code examples, user 

issues and technical support

• Swagger UI documentation and 

examples, fully documented API 

with examples you can try on the 

website

• JWT token based authentication to 

access the metadata

• JSON response (application/json)

API and Documentation
Agency API endpoints

Endpoint documentation

Parameter documentation
Agency API response
Publication API endpoints

Endpoint documentation

Parameter documentation
Publication API response
Release API endpoints

Endpoint documentation

Parameter documentation
Release API response
USDA ESMIS Team @Cornell
THANK YOU

Any questions
2018 Chicago Data Users’ Meeting

Breakout Session – Farm Income and Cost of Production

Presenters: Carrie Litkowski – Economic Research Service, William McBride – Economic 

Research Service

Note: The following write-up presents a full transcription of the session, it been lightly edited 

for clarity and readability. 

Carrie Litkowski

I will have a brief overview, to lay the groundwork with what we produce. USDA has been 

producing farm income annual data for over 100 years and the farm income and wealth statistics 

program is designed to estimate, forecast, and explain the economic performance of the farm 

sector.

What do I mean by the farm sector? We're talking about the roughly 2 million farms that are in 

the United States that farm over 900 million acres of land and there are about 6 million people or 

households who are associated with these farms. The farm sector for these accounts is strictly 

crop, animal, and animal products production, so it doesn't include agricultural chemical 

production, pesticides, or seeds.

What do we produce? We produce state and national historical estimates. These are estimates for 

the years past, they are designed so that all the states are measured consistently so you can 

compare one state to another and the states will sum up to our national total. We also produce 

national level forecasts, these are very short run forecasts that are generally for the current 

calendar year.

What kind of data do we produce within these accounts? This is a screenshot of our data product 

page for the farm income and wealth statistics and this is where all of our data related to these 

accounts are located.

Each time you click on any of these links of the different types of reports a table will pop up. I'll 

show you an example in a minute, but the accounts largely revolve around the two measures of

farm income, net farm income and net cash income. Net farm income being the broader concept 

of farm income because it includes both cash and noncash income as well as adjustments for 

inventory and that's part of our value added statement which tells you how much farm is 

contributing to the economy as a whole. So it's the sum of production income and farm related 

income and net of expenses.

We derive these estimates of farm income from the bottom up, meaning that we estimate the 

components first, so that we can calculate net income. This gives us a lot of detailed information 

that we can provide to our users. We have information on cash receipts, for example, by state and 

for the U.S. and we have cash receipts by commodity for over 100 crops and for about 40 

livestock commodities as well. We also have government payments by farm program and we 
have production expenses by different type of expenses such as feed purchased, livestock 

purchased. There are over 20 different expense items that we’re calculating in these tables.

We also produce a U.S. level balance sheet so this gives us information on assets, debt, and 

wealth for the farm sector. We do forecast this data out as well for the U.S. We also have 

financial ratios and we also produce forecasts of farm business income, focusing on a sub sector

of the farms and only looking at the larger farm businesses. We can get farm business average 

net cash income by typology, sales class, commodity specialization and region.

This is our data page, but we also have a topic page where you can get a narrative. If you don't 

want to look at the tables but want to get more of a descriptive understanding of what the latest 

forecast is you can go to “Summary of Data Findings” and that will present this information with 

some additional charts of what our latest forecast results were. If you go to the value added table 

for the U.S. farm economy, this is an example of what you would get. It’s a tailored report, you 

can select the years that you want, whether you want to see current dollars or inflation adjusted 

dollars and then you can click on any state if you're interested in state level data. This file is

downloadable to a CSV file so that you can take it and work with it on your own computer

systems or on your own environment.

Our current release was in early February of this year. This is when we released our first forecast 

for 2018 and we also slightly updated or revised our forecast for 2017. Our next release is going 

to be late August of this year. This is when we will update our 2018 forecast with more current 

information as well as what has been seen and observed through the calendar year so far. We

will also convert our 2017 forecast for the U.S. into an estimate meaning that's going to be based

on more observed data like NASS survey data and administrative data on government payments. 

And this is also when we will have our first state level estimates for 2017, so that’s one of our 

biggest releases. Then in November we'll go ahead and update the 2018 forecast and then it starts 

all over again. Next February when we'll have our first forecast for 2019. 

There's lots of ways you can look at the data on the website. I showed you an example of a 

tailored report earlier, but we also have a number of data visualizations which give you a more 

graphical view of the data and maps and charts and different sort of presentations. I've got a 

couple of examples. We have “Get to Know Your State”, this is an example where you can pick 

your state and get a lot of interesting information about farm economy within that state. I picked 

Illinois, you can see for instance what net farm income is being estimated at for 2016. There is 

also information on indemnities and insurance. You can also see the state rank. You have a 

number of different components you can rank by, I chose net farm income but you could also 

pick cash receipts, production expenses and a number of others. And you can see that Illinois is 

ranked seventh in net farm income in 2016 and you can also see in Illinois what are the top five 

cash receipt commodities, what are the top five production expenses. A great resource if you 

want information about a particular state.

We also have a data visualization on digging into the U.S. farm balance sheet. So this is looking 

at our financial statement of the U.S. farm economy. There's different story points at the top that

guide you through looking at assets, real estate, debt, and financial ratios. This is the financial 
ratios, the solvency ratio, debt to asset, and debt to equity ratio over time and you can select the

number of years you want to see.

A third data visualization is our Farm Income Atlas. This is if you want to look at the data more 

by component rather than by state, so you can take a particular component. In this example I 

chose soybean cash receipts and we can see in the map which states produce soybeans and cash

receipts in 2016 and see that Iowa had the largest dollar value of sales in 2016. And you can see 

where all the other states rank as well. You can pick multiple commodities, or you can look at 

farm related income, government payments and production expenses, as just an example.

We have a lot of frequently asked questions. If you go to our data page, there is a link to a full 

list of frequently asked questions. We get a lot of questions about our terms, what are our 

definitions of the different items. For instance, the biggest one is: “What's the difference between 

net cash income and net farm income?” I hinted at this earlier that net farm income is a broader 

measure that includes both cash and noncash income and expenses and it also adjusts for year to 

year changes in inventory. That's the short answer. But there's more information on our website. 

“How and why are our forecasts and estimates revised?” They are revised in order to incorporate 

new input data or better data or observed data, especially when we go from having a forecast to 

an estimate, when we're actually putting survey data or administrative data from the agencies that 

are making payments for these different government payment programs, for example. How are 

they revised? We have another data visualization, it's called our forecast evolution and that 

allows you to see how our forecast gets revised over the year because, as you saw, we forecast a 

single year four different times. So, how often are we getting it right or getting the direction 

right. The data visualization will allow you to see how we do on average and judge for yourself 

how accurate you think our forecasts are. “Are cash receipts reported by NASS identical to the 

value of production data reported by NASS?” The answer is sometimes. For some crops and 

livestock commodities we have to make adjustments. For instance, for CCC loan activity or for 

the use of commodities on the farm (which is production that is not sold but they're consuming it 

on the farm) and also to convert the NASS crop year data into calendar data. The ERS forecast

is a calendar year measure of farm income not a crop year. For more information I encourage 

you to see our website for the full answers that have more depth.

These releases are covered highly in the media. It's one of USDA’s most frequently cited 

statistics. This is just an example of some of the news media coverage that we've gotten this year. 

Many, national and local media outlets report on our release --talking about how with the last 

release we saw a pretty low level for farm income. This drop in farm income was interesting to a 

lot of the media outlets. They also will report on different things like our balance sheet or debt 

service ratios. We’re always looking for feedback to make these accounts better and more useful 

to people. Last year we assembled an external panel of experts to review the farm income and 

wealth statistics program. They were tasked with the responsibility of evaluating the methods

used to drive our forecasts and the data that we use and to evaluate how we distribute and 

disseminate this data to our stakeholders and to data users and also to suggest improvements. We 

should be seeing feedback from them this year and into next year and we also highly value and 

want to solicit your comments and feedback on issues that you've run into as you use the data or 
as you're considering using the data. The most direct form or way to give us your feedback, other 

than here today, is to email us at FARMINCOMETEAM@ERS.USDA.gov. This goes directly to

me and the people on the team. I'll get it directly and will reply quickly or you can also email me 

personally. I'll take a couple of questions if there are questions now and then they'll talk about 

the cost and returns. 

Attendee: Along with the net cash receipts, do you have bushels sold so you can back into 

an average farm price by state?

ERS: Not that we release. We release the data only on the value of the sales, but it is 

based on quantity data information from NASS on bushels depending on the data 

availability. Where NASS has data on bushels sold we're using it. Where they 

have production, we will use production.

Attendee: When you do your inventory adjustment, always a pretty fuzzy thing, do you look 

at the December 1st corn stocks estimate and base it off that? Year to year basis?

Where do you come up with those numbers?

ERS: We don't use the stocks data. Instead, what we're essentially doing is taking a crop 

year annual sales or production number from NASS and then NASS produces the 

monthly marketing percentages and so then we use that to convert the production 

data into our calendar year.

Attendee: Even though that's based on past information?

ERS: Those aren't a forecast, obviously. There's a little lag in the data availability. But 

from that we’re able to separate what was sold from current year production, 

because that's what we're trying to get in the net farm income, from what was sold 

from the prior calendar year production. The inventory change is essentially a 

residual. 

Attendee: How about depreciation, how do you guys come up with that?

ERS: That I know a little bit less about but I believe it largely comes from data from the 

Agricultural Resource Management Survey (ARMS) on capital expenditures and 

then we apply a depreciation schedule to the expenditures.

Attendee: The state export data is it still lagged by a year?

ERS: The state export that's a different data product. But it is largely based off the cash 

receipts here so that is probably the reason for the lag. We can’t get these cash 

receipt estimates out until about six months after the end of the calendar year and 

then they need time to convert it to exports.

Attendee: Your balance sheet information, you are strictly using market value not cost?

ERS: We use market value. We might have time for questions later. Feel free to seek 

me out. Thank you.
William McBride

All right, let me start off by contrasting what Carrie does as to what this product is. Carrie 

generated net farm income balance sheet information in the aggregate for states for the national,

the U.S. and so forth. What we're doing here in the Commodity Costs and Returns product is for 

a set of 12 commodities, we're producing estimates of the cost per acre for crops and the cost per 

hundredweight for milk and for hogs, costs per cow for beef cattle. They’re actually vastly 

different products and I like to start off by talking about why we do it. Why do we do this 

product? It dates back to the 1973 Farm Bill in which the Secretary of Agriculture was mandated 

to conduct a cost of production study and it specifically mentions wheat, feed grain, cotton, dairy 

commodities under various production practices and establish a national current weighted 

average cost of production. It goes on to say this study shall be updated annually and even 

mentioned some of the costs that are required to be included in it. This mandate is the basis for 

why this product got started and why we continue to do it is part of the permanent farm bill 

legislation now, meaning it continues to be rolled over and we continue to produce this product 

under this mandate. 

Just a brief overview of the product, we have national and regional accounts for 12 commodities 

dating back to 1975. So the product is over 40 years old now. It's based on data from commodity 

surveys as part of the Agricultural Resource Management Survey. It's USDA’s primary survey of 

the financial conditions in the U.S. farm sector. There are specific versions that we use to do cost 

and return estimates. These versions are done on a 4-8 year rotating basis for most commodities.

We're asked to produce an estimate every year but not every year is based on a survey, it's based 

on a survey that establishes a baseline and then we set estimates between surveys that are updates 

based on price, acreage, production changes until we do a new survey and establish a new 

baseline and move forward from that. Every year we're doing different commodity surveys and 

from there we establish baselines and move forward.

The methodology is that recommended by what at the time was called the American Agricultural 

Economics Association now it's called the Agriculture and Applied Economics Association 

today. They've put out a set of recommendations for doing commodity clustering turn estimates, 

which we follow as close as possible to those recommendations. It gives us a very defensible 

basis for what we do in these estimates. 

This slide shows an overview of the project in four points. First thing it shows is what 

commodities we do estimates for. You'll see the nine major field crop commodities and three 

livestock commodities. It shows in black what numbers we are mandated to produce. As you saw 

in the first slide, we operate under congressional mandate that specifically mentions cotton,

wheat, dairy and feed grain. So that's why we have grain sorghum, barley, corn, and oats. Now 

we choose to do the commodities in green. These are major commodities that compete with these 

other commodities for resources. Obviously, it wouldn't make much sense or wouldn't be easy to 

interpret a corn cost of production estimate unless you also had a soybean cost of production 

estimate in terms of what acreage changes are occurring. 
The next thing is the years, the third point on this slide. What these years indicate are the survey 

data that underlie the current estimates we have published on the website. For example, cotton 

data, our current numbers are up through 2016, next week we will release 2017 estimates. The 

cotton numbers for 2015 and 2016 are based on data from 2015. In contrast, the current numbers 

we have for wheat for 2016 are based on a survey we did in 2009. The further you get from the 

survey, the less reliable or the less dependability or confidence we have in the estimates.

The last thing on this slide is what we've got in the queue. We’ve done surveys for corn and 

dairy in 2016. A week from now on May 1, we're going to release 2017 data and we're going to 

put this corn 2016 over in this top lane up here and it will be used as our baseline for estimates 

going forward in 2016 and 2017. We’re almost done with the dairy. We just finished the survey 

for 2017 for wheat, it'll replace this data next year and then we're in the process of working on 

questionnaires for soybeans and cow calf right now. That lays out where we're at with the project 

at this moment.

I'll briefly talk about how these commodity versions fit into the Agricultural Resource 

Management Survey. If you have any knowledge of the ARMS, it is done in three phases. The 

first phase is a screening sample that allows us to screen the list, make sure it's still a farm and

identify those people who have our target commodities, in 2018 soybeans and cow calf, so that 

when we draw a sample we hit those people. Phase two is a crop year survey done in the fall 

after the harvest of the crop. So in the fall we will go out and collect information on soybean 

production practices and costs after the soybean crop has been harvested and it will cover the 

entire growing season for soybeans. In phase three we have the calendar year survey that's done 

after the end of the year where we have different versions of the farm costs and returns report 

where a lot of the farm income numbers come from. Obviously we measure cow calf and any 

livestock we do on a calendar year basis and not a crop year. And then we have a soybean follow 

on version to the phase two. 

What’s really important about the ARMS and what makes it unique is these linkages these lines I 

put in here. The screening sample allows us to identify people who have our target commodity 

and improve the efficiency of our sampling. The second thing is this connection between the 

phase two and the phase three meaning that everybody who responds here gets a follow on 

survey in phase three. What this allows us to do is to connect all the resource use, the production 

practice, the cost of production we get in phase two with the whole farm financial information,

the operator demographics, and all the balance sheet information we collect in phase three. The 

final linkage is this vertical linkage in phase three meaning that there are questions that are 

common to all these questionnaires so that we can expand our analysis of farm income and farm

balance sheet data by combining all these versions of the ARMS. That's a brief overview of how 

the ARMS works and what's planned for 2018. 

Let me briefly talk about how we do cost of production. The problem of doing cost of production 

is a problem of allocation. Most of the farm record keeping system is for the whole farm. The 

problem is, how do we then get that broken down for all these items into how much of that was 

for the corn enterprise or how much it was for the hogs. That’s the challenge we face in doing 

cost of production. 
We use what I call four different approaches to doing this. The first one is direct costing and this 

is the best way of doing it because this is the price and the quantity the farmer actually paid, but 

we can only do this for items where the farmer can answer the questions in our survey. For 

example, we feel like a farmer knows on a specific selected field what the cost per acre of the 

purchased seed was, fertilizer, chemicals and so forth. That's a very limited set of items that we 

can ask him directly what those are. 

Another thing is valuing input quantities. Here are inputs that are mainly homegrown inputs on 

the farm. For example, the farmer grows the corn he feeds the hogs. We have to assign a cost to 

that corn and we use an opportunity cost approach to do this. That is what the farmer could have 

sold that corn for so we use market prices to charge the cost of the corn. Another good example 

is unpaid labor where the operator’s family provides labor to produce the commodity. We collect 

that in the survey. We estimate a wage rate based on what farm operators earn in off farm 

employment.

The third method is when it gets really difficult. This is things that there's no way we can go out 

there and ask for a selected field. For example, how much fuel you use on a selected field to 

produce this commodity. Even worse, how much of that tractor’s capital was consumed while 

producing that crop on that field. We use an engineering formula that the American Society of 

Agricultural Engineers put out, they’re getting old, but we still use these with our survey data, 

meaning we go in the survey we enumerate every field operation that was actually done on that 

field, we can use these engineering formulas to compute the cost of fuel, the cost of repairs, and 

compute a capital cost for the primary pulling machine and so forth. 

Finally allocating whole farm expenses. This is a methodology we use on items that are not 

attributable to a commodity, but all the commodities must pay for. General farm overhead, things 

like vehicle licensing, general business expenses, farm tools. The allocation we use is we 

compute what the gross margin was of that commodity relative to the whole farm and use that 

percentage, to allocate things like overhead and taxes and insurance. It's kind of like every 

commodity pays based on its ability to pay for these commodities.

This slide shows what the account looks like for our typical crop commodity with the gross value 

of production broken down into the operating costs, allocated overhead, which are a lot of our 

opportunity costs of unpaid labor, of land, capital costs and the total costs and then the net 

returns. You can see over here we actually have variables that represent each one of these and 

what this means is that in our database we produce this chart for every farm in the survey so we 

do what we call farm level estimates and what this allows us to do are things like this analysis of 

the costs and returns data where we look at the unit cost distribution, ordering farms costs from 

lowest to highest and looking at the percent of farms that produce at various cost levels. This 

shows an example of two extremes. The survey was done for corn in 2001 and 2010. The 

marketing your average price in 2001 was $1.97. We see about 17% of producers were able to 

cover their total economic costs at that price. Moving to 2010 we see the costs shift up but we 

see the price at $5.18 where 85% of producers are able to cover costs. When we put the 2016 

data on here it'll be right about in here and a price will be right about in here so it'll look a lot like 

2001. 
The Economic Research Service use these data in a lot of research. These data are very rich for 

doing research at ERS. The top row looks at some examples of products we produce using the 

corn data. We look at what are the characteristics of low and high cost farms. What makes farms 

low or high cost? We have done organic versions of the ARMS where we've computed the cost 

of producing organic versus conventional and compared them in reports. We've also compared 

our cost and return estimates to those in Argentina and Brazil, when we have had the availability 

of data from other places, and produced a report on the export competitiveness of the U.S. The 

second row, looks at some of the things we've done with livestock. As we're starting to

accumulate surveys over time one of the things we're starting to do is look at how industries are 

changing over time. One of them we've done a lot for is the hog industry. It's been extremely 

dynamic looking at the changes from 1992 to 1998 to 2004 to 2009 and now 2015 with the data. 

In contrast, the cow calf industry doesn't change very much. What's interesting with the cow calf 

industry is the fact that it's so diverse. There's 700,000 out of the 2,000,000 farms that have beef 

cows and there's a lot of diversity out there and also we've done some work looking at dairy farm 

structure risk and the things that led up to the margin protection program 

Moving to the interest in these data, here are some statistics on how often it's accessed and used. 

Looking at the web statistics, you can see that they were viewed about 53,000 times in a one year 

period that I measured. The corn data is most accessed about 7,845 times. It's about 21 times a 

day it's actually viewed. Our data products have been internally reviewed and externally 

reviewed. Some of the things that we're doing to respond to those reviews and improve the 

product are we are moving to fewer Excel files. Pivot tables where we just pivot between the 

regions and look at the different costs that way and provide machine readable data sheets. We're 

looking at this idea of the estimates between surveys and how do we move from one to the other 

instead of just anchoring the data to one and letting it move off and then having a discontinuity 

when we do another survey, we're looking at ideas and how to smooth the estimates between 

surveys and then we're also looking at how to add the costs and returns to this data product or 

this data tool we have where we look at production practices so that people can correlate the 

costs of production with various production practices.

Wrapping it up, here's some links for the ARMS data product with the data tool, our data product 

of the costs and returns and my email. I'd be happy to take any questions that you have.

Attendee: Your forecast for corn production looks pretty comparable to a lot of the 

university budgets but the soybean budget looks much higher than the state 

extension service any idea what’s going on?

ERS: The one thing that you see is that almost every land grant university does some 

kind of budget and they all do it different. That's one of the values of this product 

because the fact that we do it consistently across the entire country. Typically we 

do a full economic cost accounting, meaning we charge for every resource used. 

A lot of times if you just compare the bottom lines, ours are always going to look 

higher. 
Attendee: Even trying to smooth those out this year there seemed to be a very large 

discrepancy like the overhead and the labor. 

ERS: I’d have to look at that it’s hard for me to comment right off the top my head on 

the forecast. We actually do a few forecasts that you brought up that that I didn't 

really talk about, but we use our base numbers and move them ahead a year or 

two and typically report that. 

Attendee: I’m excited about the wheat data that’s coming out. I was wondering if you could 

share some details about whether it will be aggregate all wheat or wheat by class.

ERS: We’ve always produced estimates of all wheat but we've always reported what 

percentage of each type that the account produced. In our publications that would 

be the place where we would tend to break it out and report it by various types.

Attendee: Do you think pulses will ever make it into the survey?

ERS: The way the current budget looks, if anything else makes it in I would be

surprised. 

Attendee: Farm income has been under pressure the last couple years. What’s the best 

statistic that really portrays the health of the farmer if you had to pick one?

ERS: I think you have to look at the combination. You can see net farm income, net 

cash income plummet in a year but then you can go to the balance sheet and see 

that the debt to asset ratio is still low, it’s still healthy. I think if you're talking 

about the well-being of farmers in more general terms, maybe the balance sheet 

information, perhaps gives you a more stable picture or in some ways a more 

complete one as to what they might be facing. But I think you have to take it all 

together. 

ERS: I think if we continue to see prices the way they are, though, that balance sheet 

will probably be impacted.

ERS: Yes it will and I think that’s what a lot of us are looking for to see when are farm 

assets going to start going down and the debt start to go up. Because insurance has 

been keeping the debt down and farm land values have still been appreciating. 

We are getting to that question now, how long can that go on? I think the balance 

sheet has a lot of interesting things to look at. 
Farm Sector Income and Wealth Data Product:

A Brief Overview

2018 USDA Data Users Meeting

Carrie Litkowski

4/24/2018

Disclaimer: The views expressed are those of the author and should not be 

attributed to the Economic Research Service or USDA.
2

Farm sector summary

2+

million farms

900+

million acres

6+

million people living in farm 

households 

Source: Farm Income and Wealth Statistics Data Product & Agricultural Census
3

Data and Analysis

Farm Sector Income and Wealth Statistics

Historical State Estimates Historical National Estimates National Forecasts

Reports: http://www.ers.usda.gov/data-products/farm-income-and-wealth-statistics



7

Farm Sector Income and Wealth Statistics 

Timeline

Data product updated 3 times per year. 

Current Release: February 7, 2018

Next Release: August 30, 2018

February 7

2018

February

2019

August 30 

2018

November 

2018

Current 

release

Update 2018 

forecast & 

First 2019 

forecast

Update 2018 forecast

&

First estimate of 2017 

incorporating survey 

data 

Update 

2018 

forecast
8

Visualizations let you dive into the financials of the farm sector

Many Ways to Explore the Data

Tailored financial reports Current and archived 

comprehensive datasets



12

Frequently Asked Questions

• What is the difference between net cash and net farm 

income?

• How and why are the estimates and forecasts revised?

• Are cash receipts reported by ERS identical to value of 

production reported by NASS?

• For more FAQs see https://www.ers.usda.gov/dataproducts/farm-income-and-wealth-statistics/update-andrevision-history/faqs/
13

In the News

“Farm debt service ratio forecast to stabilize in 2017 and 2018.”

4/9/2018

“Net Farm Income Seen Falling to Lowest Level Since 2016.”

2/7/2018

“Farm Sector Profits Expected to Decline in 

2018.” 2/8/2018

“Trends in Farm Balance Sheets over Time.” 

3/13/2018
14

Feedback

• Ongoing: External committee review of farm 

income and wealth data product

 Review methods and data sources

 Evaluate data dissemination 

 Suggest improvements

• Your comments and suggestions?
15

Farm Income Team

FarmIncomeTeam@ers.usda.gov

Carrie Litkowski

Carrie.Litkowski@ers.usda.gov
1

The USDA Commodity

Costs and Returns (CAR)

Data Product

William D. McBride

Data Users Conference

Chicago, IL

April 24, 2018
2

Motivation for CAR Data

• Agricultural and Consumer Protection Act of 1973

“The Secretary of Agriculture...shall conduct a cost of production study 

of the wheat, feed grain, cotton, and dairy commodities under the various 

production practices and establish a current national weighted average 

cost of production. This study shall be updated annually and shall include 

all typical variable costs, including interest costs, a return on fixed costs, 

and a return for management.”

• Mandated reporting of these data is part of 

permanent Farm Bill legislation
3

CAR Project Overview

• National and regional accounts for 12 commodities 

annually from 1975

• Based on data from commodity surveys as part of 

the Agricultural Resource Management Survey 

(ARMS)

• ARMS commodity surveys are conducted every 4-

8 years on a rotating basis for each commodity

• Estimates between surveys are updates based on 

price, acreage, and production changes

• Methods are those recommended by the American 

Agricultural Economics Association Task Force
4

Crop commodities

• Cotton 2015

• Oats 2015

• Rice 2013

• Peanuts 2013

• Soybeans 2012

• Grain sorghum 2011

• Barley 2011

• Corn 2010

• Wheat 2009

Livestock commodities

• Hogs 2015

• Dairy 2010

• Cow-calf 2008

ARMS Data for CAR Estimation

Mandated

Estimates

Upcoming data

• Corn 2016

• Dairy 2016

• Wheat 2017

• Soybeans 2018

• Cow-calf 2018
5

CAR Data Collection in the 2018 ARMS

Screening

sample

Phase I

Soybean

Production

Practices & Costs

Soybean Costs

and Returns

Farm Costs

and Returns

Phase II

Phase III

Cow-calf Costs

and Returns
6

Approaches to CAR Estimation

1-Direct costing 

-Purchased input costs are taken directly from the 

survey

-Examples: purchased seed, fertilizer, chemicals, 

custom operations

2-Valuing input quantities

-Farm supplied or homegrown input quantities from 

the survey valued using relevant prices

-Examples: homegrown seed and feed, unpaid labor, 

land, manure
7

Approaches to CAR Estimation (continued)

3-Indirect costing 

-Engineering formulas used with survey data to 

estimate machinery and equipment costs

-Examples: machinery and irrigation fuel, repairs, 

and capital

4-Allocating whole-farm expenses

- Rules defined to allocate farm business expenses 

not specific to a particular enterprise

-Examples: general farm overhead, taxes and 

insurance
8

CAR Crop Account Format and Variables

Item Variable Name

Gross value of production:

[primary and secondary products] GROSS

Operating costs:

Seed COSTSEED

Fertilizer COSTFERT

Chemicals COSTCHEM

Custom operations COSTCUST

Fuel, lube, and electricity COSTFLUB

Repairs COSTREPA

Purchased irrigation water COSTPWAT

Interest on operating capital COSTOPCA

Total, operating costs COSTOPER

Allocated overhead:

Hired labor COSTPDL

Opportunity cost of unpaid labor COSTUPDL

Capital recovery of machinery and equipment COSTREC

Opportunity cost of land (rental rate) COSTLAND

Taxes and insurance COSTTXIN

General farm overhead COSTOVER

Total, allocated overhead COSTALLO

Total, costs listed COSTTOT

Value of production less total costs listed NETTOT

Value of production less operating costs NETOPER

The views expressed are those of the author(s) and should not be attributed to the Economic Research Service or USDA.
9

0.00

1.00

2.00

3.00

4.00

5.00

6.00

0 20 40 60 80 100

Percent of farms

CAR Data Analysis: Total Unit Cost Distribution

Dollars

Per

Bushel

MYA price= $5.18

MYA price= $1.97

17 85

2010 Corn

2001 Corn
10

Organic Field Crop

Profitability

CAR Data Support Research in Diverse Areas

Characteristics and 

Costs of Corn Farms

Changing Economics

of Hog Production

Production Costs &

Export Competitiveness

Diverse Beef

Cow-Calf Farms

Dairy Farm Structure, 

Risks, and Gov’t Policy

The views expressed are those of the author(s) and should not be attributed to the Economic Research Service or USDA.
11

Interest in the CAR Data Product

• Users of the CAR include government agencies, 

policy-maker staff, academic research and extension 

staff, farmers, input providers, agricultural consultants, 

press, among others 

• Since 2013, CAR data have been used in more than 20 

extramural projects with U.S. land grant universities

• Web statistics indicate that between May 1, 2015 and 

April 30, 2016:

-CAR data were viewed 53,119 times

-CAR corn data were downloaded 7,845 times

-CAR dairy data were downloaded 903 times
12

Improving in the CAR Data Product

• Moving to fewer Excel files with:

-pivot tables including estimates for the U.S. and all 

regions by year—pivot between U.S. and regions

-machine readable data sheets—all metadata can be 

downloaded in a machine readable format

• Evaluating methods of smoothing estimates between 

surveys—anchoring estimates between surveys to both 

prior and latter survey data

• Exploring how to add crop CAR reports to the ARMS 

Crop Production Practice (CPP) data tool

- allows data users to associate crop production 

costs with production practices

- provides data users the CV of cost estimates
13

Information about ARMS and CAR Estimation

• ARMS data product:

www.ers.usda.gov/data-products/arms-farmfinancial-and-crop-production-practices.aspx

• Commodity CAR data product:

www.ers.usda.gov/data-products/commoditycosts-and-returns.aspx

• E-mail:

wmcbride@ers.usda.gov
2018 Chicago Data Users’ Meeting 

Session: Voluntary and Mandatory Reporting 

Presenter: Taylor Cox – Agricultural Marketing Service 

Note: The following write-up presents a full transcription of the session, it has been lightly 

edited for clarity and readability. 

What I want to update you with this afternoon is what we're currently working on so I'll just go 

through the list. Starting with swine and pork. Over the past couple of months we released a 

weekly average weight of barrows and gilts PDF report. Many of you are familiar with the 

voluntary version where we simply provided an average weight of Iowa and Minnesota hogs. 

This new report is expanded into regions as well as the national, using LMR (livestock 

mandatory reporting) data. The old voluntary report concentrated on the average live weight, the 

new report shows the live animal weight, but we concentrated more on the carcass hog, because 

we see more hogs traded on a carcass basis as opposed to live. It's data that is already sent to us 

by packers so we made a new report. 

We continue to evaluate the premiums on the swine 250 report. Out of the stakeholder meetings,

we added a new section where we lump together a series of premiums (ABF, gestation crate free, 

etc). We're still monitoring the antibiotic free and housing premiums to see if we can get that to 

pass through confidentiality on a regular basis. There's weeks that we’re close, we just don't want 

to pull the trigger on something just to turnaround and have it fail again. 

In Kansas City I was asked about the specialty pork report at the last Data Users’ Meeting. We're 

very close to releasing the report. We've been reporting pork for some years now in LMR, and 

the release of the new speciality pork reports will be very similar to box beef with their branded 

report, which is many G schedule type certified beef programs. Any new reports we do we're 

going to release it in our PDF format. We're still combing through the data, I had them run it for 

last week before I came to this meeting. Last week's data would have showed about 70 total 

loads of specialty pork products combined, so 70 total truckloads. We're going to have to take 

baby steps, we will start by releasing a comprehensive type specialty and then look and see if we 

can segregate items like ABF or maybe breed specific as we go along. At the same time, you 

have to remember when you're talking about box beef or pork reporting it's thousands of product 

codes that change often. Every time we hit a target, it's a moving target. Bear that in mind, but 

we're close and it will be a calendar year 2018 release on this new report. 

I was also asked about the comprehensive pork report, again right in line with the beef. The 

comprehensive pork report will also be a calendar 2018 year release. We're just finishing some 

programming at this time with our contractor matching up some calculations. So we'll have a 

comprehensive report out over the next coming months which I know a lot of people wanted. In 

addition, on that comprehensive we've never done anything with the sow and boar meat because 
it doesn't pass confidentiality, but we can show volumes on that, no prices yet but we're going to 

put the sow and boar meat totals that we’re capturing on that comprehensive barrow and gilt 

meat report.

We continue to onboard plants in LMR on the beef and the pork side. Of course pork’s moving 

very progressively, another big plant in Iowa coming online in 2018. Again, all these companies 

stay in very close contact with us. So we expect a smooth onboarding and to have them reporting 

very close to start up like the other plants that started up in 2017.

I already touched on the stakeholder meetings, we're still looking at a producer/packer sold query 

on the 201 report where we combine producer and packer sold data into one category. The 

request was generated from the folks at the CME to add more volume and price to their index. 

We're still waiting for industry feedback to finalize that so that remains on our books from the 

stakeholder meetings. 

We're still looking at establishing a definition for a commodity hog as we've seen a lot of 

changes from the past 40 years, especially for the last 20 years. Since LMR started 20 years ago 

that commodity hog may not be now what it was then. If we can define what a commodity hog is 

today it's plausible that we'd be able to back out some premiums or some indicators to get back 

to that base hog on our reports, and make that base hog price more useful. That's an idea that still 

on the books and we will continue to vet.

We continue to review pork cutlines. We're always reviewing product codes, they never stop 

coming and never stop changing. We're reviewing them trying to package them, get as much data

passing through confidentiality as we can. We will be updating pork and beef yields. We will 

be sending a yield email out to our participating packers in LMR very soon to update that first of 

the year 2019 as we do every year now on lamb, beef and pork.

On the cattle side, it was alluded to during the open session the comprehensive report. I hope you 

all are viewers and users of the report and you're familiar with it. If you're not, I’ll give you my 

card and please reach out, I'll get it to you. It's a very nice report it does a lot with the data that 

we never did anything with. The idea was to show a comprehensive value of fed cattle each 

week. It evolved from that, but that was the basis of report. It was requested from the industry 

that we work on 0 to 14 and 15 to 30 day delivered negotiated cattleprice splits. Right now we're 

reporting negotiated cattle with a combined 0 to 30 day delivery period. We evaluated the 

national side and were able to pass independently the 0 to 14 and 15 to 30 day delivered cattle 

on the comprehensive report. About one month ago is began failing the confidentiality guideline, 

and then recentlystarted passing again, so you are seeing the confidentiality guideline we use in 

play there. We've been asked to look at 0 to 14 and 15 to 30 day delivery periods in the five area 

regions, we haven't released anything yet but we have noticed it follows the national price trends

closely. So if you're seeing a 0 to 14 and 15 to 30 day split on the national you can almost lay 

that on top of the five area and it's extremely close. 
In addition, we've been asked to look at publishing just 0 to 14 day delivered negotiated cattle. 

We're still looking at that, which would open up the question what do we do with the 15 to 30

day delivered cattle? We've already addressed what we do with the more than 31 day delivered 

cattle, we put them on the forward contract report. Over the past six months the new 

comprehensive report has been very well received. We're getting a lot of feedback, it is fluid, we 

want to make sure everybody knows if you have ideas for input on that, let us know and we're 

open to making those additions or changes.

We don't talk a lot about cow reporting, but we're looking at a net slaughter movement report 

adding this on our boneless cow weekly summary. We'd show net movements from region to 

region. It would show cattle being procured in one area moving to another. We thought cows

would be a good place to start, it's pretty easy on a weekly basis. We want to look at this net 

slaughter movement, maybe apply that to our hog and lamb reports as well.

I’ll finish with the lambs. We did a lot of work already with the lambs this year. We got, as Mike 

mentioned, the formula price passing confidentiality on the purchase report as we were able to 

scrub the volumes and just show that weighted average price. The next big item will be on the 

weekly comprehensive carcass report. It has historically been a packer sales report, we capture 

the carcass sale, we are adding to that the purchase side. We're going to retitle that as a purchase 

and sale report to try to get that weekly comprehensive carcass lamb report to start passing 

confidentiality. We know that's very important and the preliminary looks like we would be able 

to possibly break it into three weight breaks, 65 pound carcasses and down, 65 to 85, and 85 and 

up. It’s not the same 10 pound carcass spreads as you're used to, but the current report is all 

blank so it would certainly be a big improvement for the lamb industry.

That's pretty much where we are on LMR since our last meeting in Kansas City. And again, I 

encourage you to go to our stakeholder page. It's more bedtime reading than you could ever want 

all the stakeholders notes are there. We're very transparent, we had some studies come out of 

that. They just released a study that went along with our comprehensive reporting and composite

reporting, but some additional ideas as well. So I encourage you to go and read those and reach 

out to us with any questions or comments. With that anybody have any specific questions for 

Russ and I with either voluntary or mandatory reporting?

Attendee: On the weekly comprehensive report, you're talking about a cattle confidentiality 

issue was that in the 15 to 30 or was that in the 0 to 14?

AMS: It was in 15 to 30. It is what we thought it would be, you're going to see packers 

out further and it's over that 60 day period so you're going to see some lag there at 

times before it passes (confidentiality) again. It's currently passing again last I 

looked so we'll go through another wave, but we had a pretty good run and at least 

we're showing it when we can. 
Attendee: That's fine because I think the industry really was more concerned about 0 to 14.

From a CME standpoint, that's what we're worried about but if you talked to the 

different cattlemen out there that's really where their focus is. If we can get 15 to 

30 to start passing again or it's maybe it's spotty, we will live with that. This is a 

great report, I can't thank you guys enough for doing that.

Attendee: One question on that report, the data on some of the charts, is that available to 

download? 

AMS: There’s a CSV file along with it. It does have all of the data points for the chart.

Attendee: And that’s going to go into MARS eventually?

AMS: Eventually it would go into MARS.

Attendee: With regard to the HG201 any ideas on the timeline about combining those two, 

packer and producer sold?

AMS: It's not going to be combining them, we’re leaving it as is but we would take the 

transactions in the packer sold and let's say there's 10,000 yesterday, 5,000 of 

them are going to go into the SPMF, 2,000 into negotiated and the rest in the other 

appropriate buckets. So it's continuing to be broken out at packer sold so you can 

see if there's any differences, but it's taking those transactions and including them 

up top. The top of the report would be producer and packer sold combined if this 

idea is enacted. Packer sold would stay broken out as is because people have 

indicated they like that. What we're hoping is that it wouldn't be a big task and it 

also would keep the report as is as far as packer sold. 

We initially tried to get the packer sold to pass confidentiality across those five 

purchase types and only the formulas would pass. That was the original ask to 

break them both out because right now we do a comprehensive on the packer 

sold. Can you just break it out like the producer? We tried, all you would have

pass is formulas, which for the CME guys that's important because that'd be, an 

additional 5,000 to 7,000 a day just for the formulas going into the index. But for 

us, you'd be black holing a lot of data. This is the next best thing, let's just 

combine them and still leave it broken out. I don't have a timeline for you. 

Attendee: Would that change any CME formula index pricing?

Attendee: No, it just adds more head into the index. 

Attendee: Do you get a lot of packer pushback?

AMS: No.
Attendee: We've (CME) talked with a couple different packers they were very nervous 

about all the newer packers that are coming on board. There's been real 

nervousness among the established packers, they wanted to see how that number 

evolved. They said just give us a year to and let us watch that. Then we personally 

had some phone calls from different groups that were just like, “Hey, give this a 

year” and Jim came up with a compromise here just saying leave it out there, let's 

see what develops but then start dropping it into the buckets up above. And so 

we're not double counting, we're just throwing it back up into the buckets where 

they fall and if it falls into the ones we're counting that's great we’re just adding 

more volume to it and it allows everyone to see that category still split out, which 

is which we thought would be a really cool compromise. But we've even had 

having trouble getting people to say do it. We need more industry support. 

Attendee: I have a question about the comprehensive pork coming up. On the beef side they

show the export sales and they break them out by NAFTA and non-NAFTA. Is 

that going to be possible for pork?

AMS: The comprehensive, the best way to describe it on pork, it's just everything in. It 

won’t be broken out.

Attendee: It’s really important especially on the pork side with Mexico being the biggest 

export market.

AMS: For pork, NAFTA is considered domestic. Whereas on beef it’s always 

considered to be separate. They were very specific that NAFTA is included as 

domestic for pork. 

Attendee: I'm from Australia and I sit with great admiration and envy at your reporting

service. We don't have compulsory reporting. My question is really about 

reporting export and as I understand that is not compulsory, particularly beef, is

that something at some stage in the future will be considered?

AMS: For domestic reporting, per the Act, it was to capture domestic sales it doesn't 

cover the export. We do some international reporting on the voluntary side. It's a 

lot of volume and live animals and those sort of things. It’s more movement. 

Attendee: Hogs, I believe, that’s 26 percent export so obviously that has a huge impact on

the value of the carcass.

AMS: Under the Act that we fall under on LMR, we're capturing domestic sales.

Attendee: But on pork on the non-NAFTA one you do capture the sales which is kind of 

interesting because it gives you some idea as to what's actually going to export.

But you don't get that on beef, the symmetry is not there.
AMS: No, they're not twins. 

Attendee: So it's a function of the rulemaking why we're getting some on the pork but not on 

the beef?

AMS: To some extent, with beef it’s just export overseas.

Attendee: The comprehensive will have the number of loads that were sold but it doesn't tell 

you know what primal it was like you do for pork. For pork you can break it down 

by primal, how many primals went to overseas markets. 

Attendee: Primals from reporting packers.

Attendee: From reporting packers but at least you have a sample to work with. 

Attendee: We do have very detailed primal cuts export information out in Australia. It's 

derived from our export documentation, it’s broken down by category. It may be 

something in the future that the requirement for export by documentation that you 

could acquire that information.

Attendee: I wanted to come back and get a little bit more detail on the grade discussion we 

were having earlier. You mentioned the select with the way that production is 

going it's becoming a little bit more antiquated. Do you see that potentially getting 

phased out in favor of a choice, high choice, and if so, what would be the timeline 

on that or is select going to stick around in the mandatory reporting?

AMS: In our conversation earlier we were talking about select, choice, high choice, 

spreads, and the cut out. I think we would envision if we were to go to choice, 

high choice still having that select, you'd have all three. The problem is in looking 

at the forms in the mandatory data, the live animal grading they’re reporting 

percent choice. It's not a requirement so we have to look at that. We have to really 

dig into the forms and see what we could do. With that grading percentage really 

skyrocketing to 70 or 75 percent, there's a lot of choice and high choice beef on 

the market. But the choice/select spread is the standard bearer. Everybody's 

familiar with that, you have to remember it wasn't too many years ago they 

changed the carcass weights on the cutout. Some things like that have changed 

over the years. I would envision we'd have all three as long as there was still data 

there. I think people would still want that historical series, we wouldn’t just drop

select. We talked about G schedule beef before, there are some packers you can 

see on that website that have some select programs. 

Attendee: It's to the point where I think people in the industry are talking about should select 

even be priced off select or should it be a choice minus. There's a lot of concern 

about the value of that data if it's going to be spotty or extremely low volumes and 
how can you price a program if you’re reporting low volumes. That's why I'm 

curious about that and what we can expect to see from choice/high choice.

AMS: I couldn’t give you any kind of timeline. We haven’t done that deep of a dig on 

choice/high choice. I would envision as long as we have select data it will be 

there. We’re data reporters, at the end of the day, we collect data and report it. If 

there’s data there, we try to get it in front of you. 

Attendee: To have those types of changes, you have to get the packers buy in. It’s not just 

that AMS can do this, it would have to have support from the packers as part of 

the process of doing this. It takes some groundwork, some people to people calls, 

and I can guarantee almost the first answer from the meat packers will be no. A

lot of times you have to lay the groundwork for a few years at meetings like this 

to get these things on the table so that they're part of the industry wide discussion.

AMS: I think that at soon as you do that, you run the risk of a couple years from now

maybe the trend changes, for example if you get really high feed costs and people 

are marketing their cattle at a lighter weight and you have a lot more select. So we 

certainly don't want to get into a situation where don’t have the capacity to report 

select. 

Attendee: Grid pricing systems are based off of select discount. You’ve got to feed this into 

the system so people understand it, you can’t change it overnight. 

AMS: Not to pile on the packers but our largest data providers, the ones who submit to 

us are also some of our largest data users. That's important to not just put the 

packer in a corner. We exist because of the law and they're required to report to 

us, but they're also major consumers and users of that data. Maybe 20 years ago 

there was a little more push back on new ideas and maybe we don't even want to 

do this, to where we've shown things and we've done things with the data. And 

you're right, we have to wean into it, we have to take our time and get out in front 

of people, have stakeholder meetings. But it seems to be much more palatable 

these days, even with the packing community in most instances.

Attendee: ABF premiums, do you collect them on the swine?

AMS: We get ABF premiums.

Attendee: You do get that information, it's just a matter of how you report it. 

AMS: Are you talking about live swine or the meat?

Attendee: The non-carcass type premiums.

AMS: Every Monday the swine packer is required to report to us all their premiums. 

ABFs, gestation crate free, feed programs, transportation. We would like to show
all of those. But again, with the evolving markets, you've got some major packers 

with five premiums tied into one premium. They could have this, this, this and 

this in one price plus 12. They're not required to break that out, they’re required to 

report to us the premium. We try to do the best with the data, that's why we added 

the new category to the premium report where we just lumped all the premiums 

together and we show what they are. It shows all those premiums just with one 

spread. We'd like to take any one of those if we can pass it by itself, we will. But 

again we're seeing more of that with some major packers offering one big 

premium. And so we just put that in the other, that's why you see that big 

premium spread on that 250 every Monday, because right now it doesn't require 

them to break that out.

Attendee: In the end it's one of those things if you’re an analyst or even if you're a buyer, 

you look at it as an indicator and even if it was a monthly number where you have 

a monthly bucket at least gives you an indication this is what the premium level 

was. Maybe if you have a monthly number you can accumulate enough data to 

put it together. 

AMS: That's why the commodity hog discussions going to be more prevalent in the 

coming months. I expect at the pork Expo in June we'll talk a lot about the 

commodity hogs. To your point, with all those premiums, what fits in that box 

anymore? It's like what’s going in the 201, that other category is growing, you 

have to figure out how to classify that stuff that's going into the other. Originally

the other was just if it doesn't fit the other categories you just put it in there to 

where it's grown into some of these premium programs. They don't fall with the 

formula definitions so we have to figure out what that commodity hog is and can 

you back those out and enhance that formula or negotiate a trade. It'll be a much 

more prevalent conversation as the months and years go by.

Attendee: When you look at that premium hog category, two or three years ago that was an

outlier. But when is that normal? When does that come into the mainstream? If 

you'd asked me this six months ago, oh, you're two, three years out. You're closer 

now and you're right, this is going to be a big discussion in Des Moines at the 

World Pork Expo because they’ve really got to decide what is a premium hog?

What is that number? Is gestation free now real is that just a real part of the 

market? If they become a bigger part of the market, they need to fall into our 

index and need to fall into pricing. I don't know the answer that I just know that's 

an excellent question of where that’s going to lay. 

AMS: You can make that point further with the specialty pork report. They don't 

consider that specialty your premium. 
Attendee: Well, that makes your job that much harder because you really got to try to focus 

people in the industry on what's more mainstream now that used to be not 

mainstream before. It's like in beef when you’re selling the upper two thirds 

choice and all these programs that go into those. You've got one packer that's got 

14 programs and you sit there and you're just trying to put that in your mind that 

they're doing that and how does that impact things? That's a great question on 

select because there's packers scrambling to sell enough select. Now $6.00 corn

that's going to solve a lot those questions. 

Attendee: The interesting thing is that even when the weights are lower your higher grade. 

That is what kind of triggered us to say this is maybe more of a long term trend.

Attendee: These premiums are a big point, these are not additive and increasingly they are 

part of a package. At the CME especially, you are trying to use these to change, it 

doesn’t work as well as it did with the grid system. 
2018 Chicago Data Users’ Meeting

Breakout Session – Marketing Analysis and Reporting System (MARS) 

Presenters: John Gallagher – Agricultural Marketing Service, Jessica Crum –

Agricultural Marketing Service 

Note: The following write-up presents a full transcription of the session, it been lightly edited 

for clarity and readability. 

I know everybody's time is important, so thank you all for choosing to come to this session. We 

are excited to share with you some of the things we've been developing. My name is John 

Gallagher, I'm the IT manager for both the LMR system and the MARS system. I've been with 

AMS for about 15 years. My coworker Jessica Crum is here, we're part of a larger IT team that's 

been developing MARS the last four years. Again, we're excited to show you some things today 

that we think will revolutionize the way Market News does business. The focus on this 

presentation is really not about us; it's about empowering our users with new tools and 

technologies that will help you use our data more. We're going to share with you more data,

higher quality data, and on a more real time basis. The focus of this presentation is giving you 

those tools and techniques to use the data in ways you didn't think you could. We developed an 

API, an API is an Application Protocol Interface. It is a machine technology, developers know 

what it is, but we also made it so that basic users can use this tool. We’re going to show you 

some things here that we think will really change the way in which you use Market News data. 

This is a basic Excel spreadsheet and what I'm about to show you works for both Microsoft 2013 

and 2016. We took a basic spreadsheet and using the Data tab on the top we connected this 

spreadsheet to our MARS API. We have five tabs across the bottom. The first tab is going to 

connect to the MARS server, it's going to pull down a table of contents of all the reports that you 

can access anytime and get all of the data on demand. Right now this is currently production, this 

is not development. All of these dairy reports, all of the feeder cattle reports are available now. 

The title doesn't populate we'll fix that later, but the slug number and the ID off to the left are 

important. We put together four different samples across the bottom. We're going to do two dairy 

and two livestock reports. We're got a cold storage weekly, a National Retail, an Oklahoma 

National Stockyards feeder cattle and Joplin Regional Stockyards. Off to the right we have four 

data sources, these things take 30 seconds five clicks, you configure it and it’s there. You come 

in in the morning, you boot up your computer, you get your coffee, and you hit the click Refresh 

button. You look off to the right and it’s connecting to the API and all of those reports sorted 

most current to oldest are on your machine. Now this is today's data all the way across to the 

right. This is a local copy you can manipulate it, you can mine it for data, you can chart it, you 

can graph it, you can export it. You can do whatever you want to it. You can also link it to a 

master spreadsheet that would refresh if you refresh these spreadsheets. You can use all of your 

Excel commands, sorting, filtering, etc. If you want to get a little more technical, you could 
create sheets that just pull in butter and another sheet that pulls in dry whey. We've empowered 

you through the API and documentation to do this. We think that most users would find value in 

using this versus going and getting a daily text report or a daily PDF report from their bookmark. 

How far back do we go? Right now we have it throttled at 500 records because we're trying to 

better gauge the demand from our user community. In the long run, once we have that capacity, 

you'll be able to do up to two years at a time. We are willing to share both the sample 

spreadsheet and our online documentation before you leave today so that you can go home, 

knowing that this would work on your machine. The first time you set this up, you've got to 

create an account. This is My Market News, this is part of MARS. MARS is a bigger solution, 

but the public facing component of it is this interface. You would have to create an account, we 

give you a key because when you pull this data, we want to know who's pulling the data because 

we need to better learn our user community. So the key gives us the ability to know who you are. 

That's it. Under my profile this is the key that's associated with me, so you would plug this in an 

Excel file and one-time fire and forget; you don't have to worry about it. Those are the things that 

we're bringing to market. This is a different way of accessing our data but like I said before it 

was static text and PDF. Here we're giving you the ability to analyze the data and empower you 

to do things with the data that you never thought you could do before. 

Right now dairy is online, part of livestock, cotton will be joining us in the spring of this year, 

poultry will be through the summer, livestock in the fall, and grain in the winter followed up by 

specialty crops, which is fruit and vegetables. MARS is an enterprise solution, it is cloud ready. 

AMS took the initiative to standardize both our business processes and our data collection 

processes. Before we had stovepipe legacy systems that didn't talk to each other, had different 

business rules and really didn't empower us to be mature in our processes. We took three years, 

we built our MARS interfaces on the left and it replicates aggregated data to the My Market 

News on the right. The MARS interface mandates that the AMS reporters collect higher quality 

data. This helps you with higher quality data, more tools, and better access to our data. This API 

that we showed you today is version one. Version two, which will probably come out in 2019, 

will be commodity specific, right now it's report based but we're going to allow you to say give 

me all the feeder cattle in one big swoop.

We also built in Customer Relationship Management (CRM) into MARS that our reporters who 

have relationships both in the voluntary and the mandatory side can manage that data in a one 

stop shop and we can matrix folks around through markets as we do more with less. We want to 

assure you that the way in which you get your data from either the Market News portal or the 

LMR system remains the same. We're going to have the same access through this transition. We 

want to engage our user community and get feedback. There might be some transitional issues 

that we are going to ask your patience in. Jessica and I are here, we have an email that you can 

send us direct questions to and we watch it every day and we respond back to our user groups.
Jessica is going to demo the My Market News interface and then I'm going to come back at the 

end and talk briefly about LMR web services. Right now we have a Market News portal which 

has dairy, cotton, livestock, and specialty crops. Those are legacy systems and as all of dairy 

comes online on MARS, those systems will be sunset. They're expensive to maintain, they’re

older, and MARS offers higher level security. The SMEs for each commodity will work with the 

user communities to make sure that you're well aware of what we're doing before we do it.

I'm going to start by taking everyone to the homepage, which is the landing page of My Market 

News there are a lot of ways to get data without using the API which may not be the mode you 

want or need to get data. If there's a specific report that you know the report ID for, you can 

simply go to the Get Report Data section. I'm going to scroll down and get feeder cattle data for 

Joplin. If you click Go, it's going to take you to the data page for that specific report. Once we 

get to the data page there's lots of ways you can edit and slice and dice the data to get different 

outputs. First you can generate one data set at a time by simply clicking, for example, the first 

week of data, then you would just hit Get Data and it would pull the most recent report for you. 

You also have the ability to get a range of data. You can also save your search conditions, if it's a 

report that you get a lot and you are going to constantly go back and need to get it. Saving the 

search conditions is a registered user condition which means you have to create a login and there 

are several of those throughout the site and I'll point them out as I come to them. But once you 

save the search condition you would just go to your profile under saved queries and you would 

have all of your save conditions that you could go back to and easily access at any point in time.

Also on this page you have the ability to download the data into an XML or a CSV file and it 

will download directly to your computer. You also will see right here this link to the Joplin 

regional stockyards profile page and once you go there, it has all the data, an explanation as to 

what is on that report, it'll have a description, the market type where it comes from and contact 

an expert. Those are the people that work on the report and you can contact them directly and it 

will have their email address and phone number.

You can also subscribe. These two features that I'm going to talk about right now are once you 

have your login page created then you can subscribe to a specific report and when you do that 

your information is entered into the Gov delivery email site and you'll get any update 

notifications on that specific report. You can also add it to your favorites and when you add it to 

your favorites, it's going to come up first on the commodities page. All of these features are 

managed under your profile, so you can add or delete any of the ones that you've selected. Also 

on the homepage all the new data and new reports that come out, you can find right here. We 

also have a direct link to the API. If you go to the commodities tab, let’s select livestock for 

example, you're going to have all of the most recent news, that's going to include press releases, 

popular data and things of that nature. When you click the Favorites button it's going to come up 

in your popular data. All of the reports related to livestock, you can find under popular reports 

and you can query through them by clicking View More and searching for what you're looking 
for. If you scroll down you can see all the experts associated with this commodity and important 

resources.

The home page has a lot of great features on it too. It has all the documentation for the API. It 

has webinars, manuals, training sessions, and frequently asked questions. There's also a page of 

frequently asked questions related to My Market News specifically, it gives you the rundown on 

how to go through the site. More importantly, there's a feedback and discussions forum option. 

Those are also only options that you can use once you've logged in. Feedback is a direct 

connection to the MARS email address so anything, any comments that you email will come 

directly to us and we'll be able to respond to you. Discussion forums are where our users can

connect with each other and talk about different things.

Attendee: For the API you have only dairy so far. For this you have dairy, livestock and 

what else?

AMS: There is some livestock data and cotton hasn’t come on yet but it is supposed to 

by the end of spring. 

AMS: There are about 20 livestock auctions that are in there now. 

Attendee: So the grain will come on at the same time it does for the API?

AMS: The grain is scheduled to come on in the fall. 

Attendee: For both? Whether I am doing a query through API or My Market News?

AMS: Yes, they go together. 

Attendee: When is organic? Is that with the specialty commodities?

AMS: I am not sure about organic. 

Attendee: Are you going to show us how to build a connection with Excel?

AMS: I would love to if time permits. 

Attendee: When you publish a report how soon does it go into it?

AMS: When we publish the report, it’s immediately available. One other thing too, with 

certain reports where we do like an open and a mid session or preliminary data 
during the day, like an auction, if you're using API or pulling the data, there is one 

column that we've added now that’s called a final indicator to let you know if its 

preliminary data or final data.

Attendee: The veal report that you just pulled up was that just a demonstration or is that 

what the report is? 

AMS: It’s the report.

Attendee: I am looking at one of the auction reports. There is at least 150 data points in one 

of the auction reports. It’s breaks it down by fed cattle in a million different 

ways. What you’ve shown there is a very broad summary of what’s in the report.

AMS: There is a row that you can expand and you will get all of the data points. The 

aggregate row that you see on the report will be in the data set too. So steers, 

medium and large 1, 400 - 450 weights you get the price range, the weight range, 

the price point, the average weight.

Attendee: I was following along and looking at the query that you just put together and then 

downloaded the CSV. The CSV only has the top line row. I didn’t see any place 

where you could further refine the query. 

AMS: From the My Market News page you pulled up Joplin and you said you could 

download a CSV here and then you went on. He went and downloaded the CSV 

from here. What he is saying is that result appears to be different from what was 

pulled in [in the demo]. He may have found a problem, we will have to look at it 

first and verify it. 

AMS: If there is a specific report that you don't know about, say you just wanted to get 

information on feeder cattle, but you didn't know what reports were out there, you 

could go to this Commodity Information Bar and search. Click Go, and it's going 

to show you in the tab that pops up all the reports that are related to feeder cattle, 

which can be pretty useful.

Attendee: Did I hear you say that we are limited to 3 years’ worth of data points?

AMS: Right now we're limiting because we want to make sure the capacities of the 

servers are tweaked enough to handle the demand. Right now we're throttling it to 

500 records but we intend to increase it to multiple years’ worth of data for 

registered users level two, which is EAuth level 2.
AMS: All of our historical data is going to be migrated to MARS.

Attendee: My concern is if I want to build a 10 year database, I have to go through multiple 

steps to pull down the data.

AMS: At the end of the day we're here to help. This is a very easy to use system and we 

are happy to help you set up anything in an Excel spreadsheet. John and I are 

going to be here this evening and in the morning we would love to help you. Also, 

if you have any feedback for us. We'd love to hear it good and bad. You can either 

email this email address directly or you can click the feedback option in My 

Market News and it will direct you to this email address and we're happy to do

live demos by request.

AMS: We still have our legacy system up and running. We are building this system as 

we are using it. We couldn't wait to get it all done, we’re rolling it out in pieces. 

We aren’t shutting anything off in the old system until we know it’s working over 

here. Part of that is getting it out there in front of you all to get feedback and if

something's not working, we’re counting on you all to tell us so can fix that. One 

of the things that Jessica mentioned, the FAQs, we didn't come up with those 

questions ourselves those all came from users. 

Okay to the gentlemen over here in the far corner who asked how to do this. We're going to keep 

it simple. I run the table of contents, I see there's a new dairy report there, DA105. In our online 

documentation, on the API, we have gone to great lengths to walk you through how to do this. 

We also have a guide that we can hand out today. This is the root URL that you would call from 

your Excel file. I copied that into my clipboard, I go over to my Excel file, using the Data tab, I 

go up here to where it says From Web. I'm going to copy that string directly into there. It’s going 

to go now and get that data. It’s going launch what they call the Query tab that's part of the Data 

tab. I'm going to do a right click drill down, there's four records. I'm going to do a right click to 

table. Right here is where you're able to decide what data do I want to bring in. This report only 

has four records. These are all the fields that are going to be available. For demonstration 

purposes I'm going to take out a few here just to show you that we can customize it. This is all 

editable after the fact, you're not committing to anything at this point. I'm going to hit okay and 

it's going to go down here and it's going to create a new worksheet. Now we went through that 

kind of quick but once you do once or twice, it's really pretty easy. 

The LMR system has been around with us longer than the MARS system and back in the day we 

developed a web service that at the time was bleeding edge. Now it's technically dated and we're 

working to bring that up to up to speed. Right now we're already replicating LMR data to 
MARS. What we have to do is modify our API to show that legacy data, but the intent is to allow 

you through this spreadsheet to bring voluntary and mandatory data into one spreadsheet. My 

intent today was to demonstrate through the web service how you can bring that into Excel. 

Attendee: In that list of fields available, I’m guessing that not every field will be populated 

for every report, is that correct?

AMS: That's right.

Attendee: Will there be a way on the list of fields to show which ones have data? 

AMS: The field might be null for some time period, but it might contain data at another 

time period. So I don't know if you want to not show it. If you're talking about 

putting a logical filter on it, you probably could.

Attendee: I’m thinking about consuming all of the digitized data so that I can use it in some 

of our products for charting and load it with other charts. So we’re going to be 

using data in mass quantities and this method won’t work for us. 

AMS: You can use the API to query the database. 

Attendee: I want every data set not just a single data set. 

AMS: In the long run, you're going to be able to get everything you want but you're 

going have to be patient while we transition through all of these legacy data sets.

In theory, as long as your spreadsheet or access can handle it, you could consume 

everything. We are giving you every field, we're not filtering any field, you are 

empowered to cut out what you don't want.

Attendee: I was just curious would every report have data in every field? Every type of 

report, like the dairy reports are going to have fields and the livestock reports. I 

know you have worked to normalize everything up and I’m just curious how 

that’s going to carry across the different data types. 

AMS: The data schema for dairy reports is unique to dairy reports we don't share with 

the livestock data set.

Attendee: Let's say we're here one week from now and you pull this up, the date column is 

4/23 now will it show up 4/30?
AMS: Okay, great question. Through the API you have the ability in the URL to filter 

based on what you want to see, but you also have the ability to sort. So if you 

want the sorted cheese latest and greatest or butter oldest to newest you could do 

it at the query level or you could let Excel do it.

Attendee: Where do you do that, where do you make those choices? 

AMS: We could do them right here on the query string. This, this sample right here is 

pulling a dairy report and it's sorting on category, which is a field in the database, 

but you could sort it on published date or another field. You can also use Excel 

and use the sorting capabilities in Excel. This API is written for basic users and 

advanced users. Big companies might have their developers program this and it's 

all behind the scenes but you guys have the tools and techniques to tweak it.

Attendee: Is that a function of Postman to build the initial query string?

AMS: Postman is a great product but not the best fit for our customer base, you could 

use post man to massage the query and say this is finally what I want, but you 

could also do it in Excel.

Attendee: To build that initial query string where does that happen?

AMS: You can do in Postman or you could do it in Excel, you could do it any way. I 

could take this attribute on the right here and go back and modify the dairy report 

I just did and pull it again and sort it differently.

Attendee: Right now certain reports I am thinking about the imported beef prices are not in 

the database. Is that information going to be captured?

AMS: All of our meat data is in the Data Mart. Everything that is collected from LMR 

is in the Data Mart. Long term voluntary and mandatory data are going to be 

available through the MARS API. Our task at the moment is to get all of the 

legacy data into MARS. Once we get it all over there and we’re comfortable that 

everything is there all of the historical data has been migrated then those systems 

will be sunset. 

The LMR data, nothing's going to happen there, it's still going to be accessible 

through Dart Mart and through the web service. Currently it’s being replicated 

over to MARS to My Market News but we haven’t turned that function on yet. 

They will and so the user would have access to get it through the API but that’s 

downstream, right now it’s just voluntary data, nothing is going to happen on the 
mandatory. As far as the future of the web service, that decision will be made at 

some point in the future after the API is up and running by then there might be 

some other acronym that we're using.

This is the Data Mart for LMR, it is supported by a web service. The web service by all accounts, 

based on our back end measurements, is the most popular way to get the data. This is the 

interface that most folks would pull from for the web service. It’s eight years old, it exports, the 

data as XML vs JSON. JSON is the way in which the MARS API works, which is a standard 

industry practice, XML is a little dated. This is how the data looks in XML on the LMR site, you 

hit a file save and save the file down to your desktop and you import it into Excel. The only 

drawback to this is it's not dynamic, you don't hit the refresh button to go get a new set of data,

you have to go through those manual steps for the time being, but we wanted to show you this,

so that you could bring voluntary and mandatory into one worksheet or workbook and 

manipulate the data. That's our presentation. Do you have any questions? 

Attendee: You have a tremendous amount of data in the databases. Do you run any filters 

on the acceptability of the data? Whether it’s nonsense or whether it makes sense?

I’ll reference the data series coming out of some of the dairy sector, so that you 

quite often release it one month and then you have quite sizable revisions in the 

next month and then a year later, have some revisions. Do you do any filtering to 

try to reduce the amount of lower quality standard data? Whatever data stream 

that you're getting do you do much evaluation on the data reported to you?

AMS: For mandatory, plants are allowed to report revisions up to four weeks and they're 

also audited. Based on what the audits find, they may have to submit revisions. So 

there will always be the capability for them to revise their data up to four weeks

for dairy. 

Attendee: Do you do any evaluation of data to say this doesn't make a lot of sense?

AMS: Yes, we have a whole process that we go through on evaluating the data, contact 

plants to verify the data.

Attendee: You call back and say this appears to be unusual? 

AMS: Yes, we do that.

Attendee: A long time ago in an earlier life, I was detailed for a year to Packers and 

Stockyards and we looked at 20 years of weekly reporting on about 25 different 

markets for fed steers and we developed price profiles and change amounts that 
occurred among them. More than 20% of the average weekly data were reporting 

errors. Whenever we had something that appeared to be unusual statistically 

saying this is three standard deviations out from expectations, quite often you’d

call a reporter and he would say, whoops I had a problem there. I'm surprised at 

the amount of changes that I'm seeing in the first release of monthly dairy stats 

versus the revisions that occur there. Some of them just from a statistical 

viewpoint you could identify it right away when you see it. Something doesn't 

ring right here.

AMS: I'm not exactly sure from the dairy market news on the voluntary side and 

reflecting on butter, cheese?

Attendee: This isn’t price reporting this is on the physical amount of cows, milk production, 

pounds of milk produced. 

AMS: That’s a NASS question. 

Attendee: From a data user standpoint, I might be able to help a little bit. We pull all sorts of 

stuff from NASS, AMS, etc, a lot of USDA data and other data from around the 

world into our data warehouse. Everything you've demonstrated within seconds of 

you publishing it it's in our data warehouse. I store every revision, so if you 

wonder how often revisions happen and what are they, I've got reports put 

together for any field that I can look at and tell you here's the list revisions, here’s 

what happened? And we do that because users will come to me and they say, Rob, 

last month you said production in Wisconsin was 3.2 billion pounds, now it's 

3.21. Why? Here's where the revision came from and your databases all track 

those revisions, when they happened, why they happened so that data is part of 

the metadata that's available with this, suck that into your data warehouse and use 

it there.

Attendee: One thing that we started with at the beginning was the timeline for when a lot of 

these reports are going to become live. Do you have a list of the actual reports?

AMS: No, for instance livestock auctions, we have a few up there now because in those

states, Oklahoma, Missouri, a few others, they've been using MARS as a data 

input, whereas the other states, they've been using our old legacy LSW program. 

And so that's why we have that data there now and as they start using MARS, that 

information will come. Grain and rice are pretty similar market types, that's all 

planned to come on at the same time. Poultry, the push is going to be to push all 

the poultry, push all the eggs.
Attendee: Ideally you are going to use the new MARS is going to be not just an output, but 

it's also going to be used as an input.

AMS: Yes, we didn't even do a demo of the input side of MARS, which is much more 

complex.

Attendee: It's going to be tied on both ends?

AMS: Yes. A reporter no matter if you are a specialty crops reporter walking the 

Terminal Market here in Chicago or you are a dairy reporter in Madison or any of 

our reporters around the country, you're going to be using the same system to 

input the data.

Attendee: Does this make it more difficult for them or no?

AMS: It's going to make it better for everybody because right now what we learned is 

we're all doing it a little bit differently. Even in my own division everyone's 

making phone calls to collect information. Some people are writing on a piece of 

paper and putting together a Word or text document, others are putting it in an 

Excel spreadsheet, others are using Access. That's why we had to move to the 

same system, data is all handled the same way, better reliability, better security 

better everything when data is all handled the same way. 

Attendee: You talked about the different levels when setting up an account. You have a 

level one or level two, how do I know which one I need?

AMS: Everybody does a level one right out of the gate house but level two requires a 

slightly higher level of authentication, the design is that if you are level two, we're 

going to allow you to pull larger data sets than you would if you were level one.

AMS reporters are very excited to see and use the MARS interface. This is one of the collect 

screens. Everyone in AMS who's a reporter will use one of three collect screens and keep in 

mind that some of our specialty crop folks are in coolers or chillers or auctions that don't have 

WiFi so this interface is designed to work offline and online. We have a tablet view for 10 key if 

you're walking through doing this, you have a tab across view if you're doing an auction and 

they’re going pretty quick, or you could use a standard view. Notice that the drop downs are 

conditional so based on what I type certain things refresh here and if they're color coded, that 

means that the fields are required. We're mandating that they input data all the time, we're doing 

our best to fill in all fields where applicable. This is our data control mechanism. We have all 

kinds of widgets off to the left here that allow the reporter on site to do plotting and data analysis 
to find outliers in the data that they've collected already. They can also pull down historical data 

to see where trends are changing. They can generate a preliminary report on site at an auction. If 

they're doing an auction in New Holland and they want to provide a preliminary report to the 

folk’s right there, they can do it. It's not a final, its preliminary, but we can at least share the data 

with them. There is a lot of charting capability, many more tools than they've ever had before. 

One of the coolest things is the Prepare tab. This is a comprehensive interface that allows us to 

manage our offices, our markets that we cover, the profiles of the market, the users, the 

cooperators and contacts that the voluntary folks have established over the years. We want 

everybody to write everything down here so that it's all here. We also have a map here. We pull 

from DTN automatically now. So instead of those folks manually going and getting that data we 

consume it here so that the quality of the data is higher, that gives you a flavor for under the 

hood. 

Empowering You

April 24, 2018

Market News is changing for the better!
Link to Excel File
A different way to...

• Access our data

• Analyze our data
Data Availability
Data Entry

mars.ams.usda.gov

Data Dissemination

mymarketnews.ams.usda.gov
MARS Helps You

• Customer relationship management

• More tools

• Improves data quality

• Better access

• Dynamic and customizable 
Don’t worry, be happy!

• Same data

• Same access during transition

• We’ll work through the learning curve
Lets go to 

My Market News 
Help!

• Resources on My Market News

 FAQs

 Examples

 Webinars

• mars@ams.usda.gov

• Live demos by request
LMPRS Web Service

• Public-facing Web Service

 Query for data reports

• Legacy data format

 8 years

 XML vs 

JSON

• Help Guide online

 Use URL 

• Future: API
LMR Web Service

https://mpr.datamart.ams.usda.gov/
LMR Web Service

Click > File, “Save As” 

and save file to 

workstation
Excel
Questions?
2018 Chicago Data Users’ Meeting 

Session: Foreign Production, Trade, and Import/Export Data

Presenters: Patrick Packnett – Foreign Agricultural Service (FAS), Warren Preston –

USDA, Office of the Chief Economist (OCE), Joseph DeCampo – U.S. Census Bureau 

Joseph DeCampo 

A little bit about how the Census Bureau collects foreign trade data. It is required by Customs 

that exporters or their designated third party filer, file their electronic export information (EEI) 

into a software system that's maintained by Customs then Census Bureau extracts it. We publish 

our FT900 indicator on the international trade in goods and services about five weeks after the 

end of the stat month. It has a joint release with the Bureau of Economic Analysis and also 

Statistics Canada. We have both the press release and our website https://usatrade.census.gov/. It 

has all sorts of customizable tables, search by commodity, search by port, search by country and 

it's also historical data back to the mid 90s. Every single record is required to be filed 

electronically unless it is an export going to Canada or the value for a Schedule B number is 

below $2,500. For those we have no value estimates. 

Attendee: You have a tremendous amount of good data. The problem is you can’t find it 

because you can’t figure out the codes. Is there some place you have an index of 

your codes? 

Census: Do you have an account on USA trade? Or if you want the big book of all the 

Schedule B numbers you can go to https://www.census.gov/foreigntrade/schedules/b/index.html, there'll be an index because the codes get updated 

every single year. There is also a search function so if you want to look for corn 

you type in corn and it will list out all of the Schedule B numbers for corn. 

Attendee: If you type in corn you can get 25 different records for corn hoping they're all the 

same place. 

Census: Sweet corn is not in the same place as dent corn which may or may not be in the 

same place as popcorn. 

Attendee: Sounds like the world we were always, constantly searching for data. 

Census: They should only change at the six digit level once every five years and that's 

something that happens with the World Trade Organization that happens globally 

if things are going to change at the six digit level. But they could change at the 

ten digit level any year. 

Attendee: Every five years, so when was the last year they were changed? 

Census: January 2017. 
Attendee: That's at what level? 

Census: The six digit Schedule B level. 

Attendee: So at the ten digit level it could change. 

Census: It could be once a year, could be twice a year. 

Attendee: It could be in the middle of the year?

Census: It would be January 1 or July 1 unless there's some sort of presidential 

proclamation that we have to create a number for something, then that could 

happen whenever it happens.

FAS: Census owns the trade data. At FAS, we have the Global Agricultural Trade 

System (GATS) where we're trying to make sense of all of that and organize it in 

a way that allows us to analyze global trade. We created categories putting all the 

corn together, all of the soybeans together, we're doing the best we can stay on top 

of the changes in the codes so that we can consistently look at time series 

analysis. If a soybean code changes this year, we're trying to grab that and make 

sure it’s a part of soybeans so that when you look at the ag data we have in GATS

you know it's consistent.

Patrick Packnett 

I think most of you are familiar with what we do in the Office of Global Analysis (OGA) at FAS. 

We’ve got a couple of main functions. First is to help analyze global markets and create global 

supply and demand data through our participation in the WASDE with the World Agricultural 

Outlook Board (WAOB) and the Interagency Commodity Estimates Committee (ICEC) process. 

As part of that we establish the ag reporting requirements for our FAS overseas offices. We tell 

them what reports we need and the time schedule for the attaché commodity reports that we're 

getting from overseas that we make available to the general public on our website.

Part of our responsibility at OGA is to administer and maintain these key databases. We do that 

for our own purposes, because we use them as well as making them available to the public. I’m

primarily referring to the PS&D (Production, Supply, and Distribution) database which is USDA 

data but FAS has the responsibility for organizing and maintaining that database as well as the 

GATS which we get the data from Census as I noted before. We are using all of this data that we 

help create and collect and organize to administer trade policy and help policy makers make 

trade policy decisions and to execute our market development programs and capacity building 

programs for the agency. When we get into the Q&A if you guys want to drill into any of those

areas a bit more we'll try to answer your questions. Nina Pham is from one of our Global Policy 

Analysis Division, she's the GATS expert. If you want to drill into anything specific on GATS,

Nina should be able to help us answer questions. I've got Ron Frantz here who is from our 

International Production Assessment Division.
Warren Preston 

I will be talking about the World Agricultural Outlook Board and our monthly World

Agricultural Supply and Demand Estimates (WASDE). As Patrick alluded to that’s a process that

is pulled together through the Interagency Commodity Estimates Committees. Those are 

USDA’s official forecasts on supply and demand prices. The core of the WASDE is the domestic 

supply and demand balance sheets and then the world's supply and demand balance sheets. In 

terms of foreign production, trade, and import/export data, through the ICECs we do pull in a lot 

of information with the Foreign Ag Service’s cooperation and the Economic Research Service is

a large contributor, it’s across all agencies contributing to that process. The balance sheets give 

us the aggregates for the world. And then as we talked about this morning breaks out some of the 

major importers and exporters, which this coming May will include Ukraine and Russia and not 

the FSU any longer. So in a nutshell that’s kind of what we do. I did verify that the whole 

content will be available in the historical databases that I talked about this morning. Every 

number that's published in the WASDE we will have going back to 1996.

Attendee: Do you look back at what you've done in the past to try to maintain a consistent

methodology when you revise your export forecast or is it always on a case by 

case basis?

WAOB: There is a lot of looking back in terms of analog years I would say more 

meteorologically then in terms of trade. One of the things it's always important to 

keep in mind with the work of the board is that we assume normal weather until 

proven otherwise and we assume policies currently in place until they change. For 

example, right now we have a duty on sorghum and pork in China, and that's 

going to be taken into account explicitly. We have some other stuff being talked 

about potentially open for negotiation there. Now the trade is looking at soybeans 

and it's looking at other commodities, even though we don't have duties yet 

imposed. So indirectly depending on what markets are doing that kind of 

information feeds into the WASDE but at this point, we still assume just the 

current duties, we’re not looking at the additional announced potential duties.

WAOB: We do pace analysis too, that’s one input we look at but it’s not the only criteria. 

Attendee: Is pace a big input into that forecast adjustment?

WAOB: It's just one of the criteria we look at. It depends on the market year and the 

competitor situation. 

Attendee: I think part of the answer here on these forecasts is what the (foreign) posts are 

telling you. I'd like to give kudos for the USDA-FAS GAIN reports being more 

accessible. This has become much more economic space somebody has provided 

some training and some perspective. They’ve got a more solid basis in terms of 

supply and demand. Third, since I’m the livestock person, the livestock and 
poultry world markets and trade every six months, adding the perspective of those 

articles back to that publication has really been valuable. They're short and 

concise and I've had some really good feedback from our members across 

industry and the universities that they're finding that insightful. So I just wanted to 

relay those comments. Keep up the training I know that's a constant turnover in 

the embassy offices of the ag staff. The in country people are very good and they 

vary by country but there has been some noticeable gains there.

FAS: Thank you for those comments because it has been a struggle for us but it's been 

an area that we put a lot of resources into. The American officers rotate overseas 

and we have we try to have more steady presence of locally employed staff but 

we get turn over there as well. We have had some concerted effort to conduct 

training in Washington, we recently sent out a team and we had a regional 

training program for our LES (locally employed staff) out in Southeast Asia, 

where a number of folks from Washington went out and did a week long 

workshop specifically on commodity reporting and analysis. We want to keep the 

quality of reports up and we're putting resources constantly into training, to try to 

make sure we continue to provide quality input to the to the WASDE process as 

well as to the public audience. So I appreciate those comments. I'll definitely take 

them back to the team that's working on the livestock circulars as well.

Attendee: Hopefully you're seeing some benefits in terms of forecasting exports too.

Attendee: I commend you, the consistency of the GAIN reports is much improved over the 

last five years. 

FAS: Great. Thank you. As I noted this morning, the GovDelivery system that you're 

using now has worked well for us, not just for the GAIN reports, but pushing a lot 

of additional information out of FAS in terms of press releases and program 

releases. The public can sign up for a variety of different topics that are coming 

out of FAS and when we release the new GAIN system, we're going to have a 

subscription option directly in GAIN that will give you an opportunity to be more 

specific on what you subscribe to. 

There was an offline question that was asked about when we could publish the 

reporting schedule for the GAIN reports so that people could anticipate and know 

when the reports are going to come in. I'll take that back and if that’s something 

we can do as well. We have the schedule, we know when they’re due. They don't 

always come in by the due date but usually it’s close. Sometimes resource 

constraints at the posts get in the way and we'll get in a request for an extension.

If we can allow an extension we will as long as it doesn't conflict with our 

responsibilities to the monthly Lockup. May primarily being the big one for field 

crops. 
Attendee: GAIN reports don't directly go into the WASDE. It's an input but the committee is 

going to decide, so you read it in the GAIN reports, it's not necessarily going to be 

Brazil.

FAS: That’s a good point, we used to have a disclaimer in those reports, I hope it's still 

there, but it's one of the inputs that our analysts use. We're using a whole host and 

variety of other information and data sets, remote sensing and the trade 

information. It's all sources kind of methodology we use to arrive at these 

numbers.

Attendee: I was going to ask specifically on the production side for the WASDE, I'm 

thinking of USDA vs CONAB numbers, is there any collaboration between your 

offices and their government reporting agency? For a while on the corn side

USDA was at 95 metric tons for Brazil, they were at 86 or 87 and now they've 

come up and USDA’s come down. It seems to work together, but I didn't know 

what the two methods are there and if there’s any collaboration to try to marry

those two up because it seems like if you just average it, kind of go in the middle,

a lot of times it gets you pretty close to where the ultimate number is, but I didn’t 

know if you could any context to that. 

WAOB: I’ve had a chance to visit with the attaché’s there. We're the only survey based 

production estimates around and so we evaluate the quality of the information that 

we get for production from foreign governments. We don't have a joint 

committee. We do have collaboration with other countries through the G20

Agricultural Marketing Information System (AMIS). That's a formal collaboration 

and trying to get a common denominator, where everybody's reporting production 

and stocks kind of numbers. But to get people up to doing survey based

[estimates], what we would call the gold standard, not everybody has the 

resources to do that. I know that Seth Meyer has an example he likes to give of 

our forecast of wheat in Australia. It was a few years ago and we were looking at 

the meteorological data we had which had a little bit broader base than what they 

were looking. There was a lot of skepticism about our number versus the 

Australian, but in the end we were more accurate. 

FAS: Our analysis is independent of any foreign organization. As I mentioned, we're 

using an all source methodology and I can ask Ron to give a very brief overview 

of how his division approaches arriving at these foreign production estimates. 

While there's not collaboration on forecasts, we do have occasions where we're 

working with a foreign government on methodologies and how they're using 

remote sensing and we're sharing information and techniques and things like that. 

FAS: It is an all sources methodology. Convergence of evidence is how we like to 

describe that. For Brazil, for example, CONAB is one of many reporting agencies 
that we are looking at. We do get all of those industry and government reporting 

numbers. When we see CONAB come out with a number that’s 2 million metric 

tons off of where we’re at it certainly makes us refocus and take a look at that.

That’s where we can pull some of the other sources, we can talk to post again 

with our attachés and LES and ask them about that change. We can apply remote 

sensing to that region and focus in on a particular area and a particular crop. We 

have crop masks with our remote sensing tools that we can look at particular crop 

types and focus in on more than just general NDVI (Normalized Difference 

Vegetation Index). We can look at NDVI specific for corn and soybeans. What 

about the weather, soil moisture, there's lots of different remote sensing products 

that we use as well beyond NDVI. That's just a part of the overall picture but yes 

the country reports are very important to us, the industry reports are very 

important. As far as formal truing up with them, no. 

We travel at least annually to our regions of responsibility. Our analyst just got 

back from Brazil a couple of weeks ago. She meets with CONAB, and with the 

big organizations and discusses their methodologies. We share what we do as 

well. 

When our analysts on the production side have a number in mind that we are 

ready to defend, we go to the ICEC which consists of ERS, World Board and the 

Joint Agricultural Weather Facility (JAWF) and ourselves, we sit around and it’s 

consensus driven. The combination of looking at it from an agronomist scientific 

perspective which is more my division and looking at it from an economic 

perspective and then shaking out the number based on that. 

Attendee: Would you say that the remote sensing is more heavily used internationally than it 

is here?

FAS: NASS uses it heavily we're actually the providers of that imagery too. They use 

higher resolution imagery for the U.S. and it’s more frequent observations and 

overpasses of satellites than we get for foreign production regions. They have a 

lot of coverage of the U.S. On the foreign side, we use it more for certain 

countries areas where we don’t have good reporting. Pakistan is a good example, 

cotton in Pakistan we tend to rely a little bit more on that. It’s really country 

specific. And I think as was mentioned earlier remote sensing really helps when 

you’ve got a feast or famine situation to be able to focus in on a different region. 

Particularly for droughts or flooding events. 

Attendee: You talked about the production focus. I'm always curious about how you come 

up with what you think is an equitable ending stocks or beginning stocks number. 

It goes through constant adjustments, sometimes we go back a year or more. How 

comfortable are you with those numbers because I also went through the time 

where we had total reassessment of Chinese numbers back about 15 years ago.
How do you define really what those stocks are because those sometimes really 

become critical factors.

FAS: The way we're divided we intentionally keep production separate from the trade 

balance sheet. That’s our sister division, we dump that production number on 

them and now they have to do the hard work and figure where this is all going. So 

I'm going to defer to Patrick to answer that.

FAS: I'm fairly far removed from the ICEC meetings. You’re right, it’s a challenge 

especially when you deal with foreign countries and don’t actually have good 

information. No country in the world has the collection of information reporting

on stocks that we have in the U.S. So I think it's each committee having

methodology within their committee of how they arrive at stocks. We get the 

reporting from the attachés, they send us a balance sheet in the GAIN report and 

that's their best assessment. It varies from country to country how much of that is 

really solid data versus a residual type calculation. In some countries, it might 

venture a little bit closer to what we have available in the U.S. In other cases it's 

probably more of a residual.

WAOB: Sometimes we'd look at where the trade is going. Like Argentine wheat, the way 

they're exporting, production has to be higher. Stocks are just heading down the 

pipeline and sometimes that's resulted in us raising production. Burmese rice, they 

haven’t exported this much since the 40s, but what do we know about Burma?

Basically nothing in terms of the quality of the government data. 

FAS: We have an analyst there right now.

WAOB: We do as we speak, first time in 30 to 40 years.

Attendee: This is about stocks again specifically Argentina and soybeans. What would you 

have to see from Argentina in order to reduce their soybean stocks and when 

would that reduction happen? You mentioned trade flows?

WAOB: You look at usage and you go from there. A lot of silo bags, a lot of stocks on 

farms there so I don’t know that we have good numbers about when farmers are 

going to let that go. It’s a matter of looking at what you can see and then trying to 

make some inferences about what you don’t have good estimates on. 

Attendee: Talking about soybeans and trade flows, some of my colleagues work closely on a 

global trade model and they’re using your data. How well are we doing with the

exports? So when we talk about Argentina and Brazil and soybeans specifically, 

there are trade flows that go between those countries and then how well can you

actually tell how much of that is new product and much of that is imported and 

reexported somewhere else so that when you look at a global balance how 
confident are we in that number? Are there safeguards you use to prevent double 

counting exports?

FAS: Reexports between Argentina and Brazil?

Attendee: Let’s say Argentina produced soybeans that they exported to Brazil and Brazil 

takes a little bit out of that and then exports them to China or somewhere else. Do 

we have any methods of tracking that or do you just assume that’s new product?

WAOB: I will relay that question to the oilseeds chairperson.

Attendee: Around the world as it stands right now you’ve got Europe, Canada, Australia, we 

think we have a pretty good handle on what they’re doing. As we go into the 

Latin Americas, into Africa, we know with China that we still have issues trying 

to get solid production numbers. I think we've got a least a sense that they’ve got 

plenty of supply. Is there any place that you’ve seen positive things happening in 

the world of analysis?

FAS: There's one example I’m aware of. In the case of Argentina with the change in 

government there, we've had some teams come in to visit with our folks and with 

the World Board and I think we've seen some changes in their desire to have 

actual unbiased data and to actually get it right, rather than politically influenced 

numbers which we've seen in some cases.

It was mentioned earlier the Agricultural Marketing Information System is an 

effort to globally produce better global supply and demand information and data.

They have programs, one is to try to get better and more usage of remote sensing 

information for crop estimation. They've also had a lot of training programs for 

the members of AMIS, this is coordinated out of the FAO where they're actually 

doing workshops to help countries better develop their statistical systems for 

estimating supply and demand. There’s efforts out there to help the world globally 

do a better job, we’re involved through our participation in programs like AMIS, 

as well as teams coming through Washington and sitting down with them and 

talking about what we do. 

WAOB: Our boss was on a trip to China and India to go to some situation and outlook 

talks but also talking with government officials. We're always out there trying to

encourage getting that information out there. When we're flush in all commodities 

people maybe aren't quite as interested. But when you start running into shortages 

that's when other countries realize this is significant, maybe we do benefit from 

having more information so we don't run into food security crises as much. That’s 

the message we try to get out there. I think the more exposed that countries are to 

trade, I think then their industries start to demand it as well, they want to have that 

information. We have a pretty good relationship with Argentina with government 
folks, but also with the exchanges too and so we've got a lot of interaction there 

with folks like the Bolsa’s. 
2018 Chicago Data Users’ Meeting

Breakout Session – National Agricultural Statistics Service (NASS) Programs

Presenters: Lance Honig – NASS, Travis Averill – NASS, Jody McDaniel – NASS

Note: The following write-up presents a full transcription of the session, it been lightly edited 

for clarity and readability. 

[The recording missed the very beginning of Lance Honig’s opening remarks.]

...There are reports on stocks, prices received by farmers for the crop commodities, we do a little 

thing called Crop Progress and Condition you might heard something about that in the other big 

session. But we cover a wide scope of crops from field crops to fruits, vegetables, what we 

would call specialty crops, which is another way of saying things that didn't fit in those 

categories I mentioned already, and that's kind of what we focus on from a crop perspective.

I’m Travis Averill, the Chief of the Livestock Branch. We're responsible for livestock, poultry, 

and specialty commodities for NASS. That entails monthly reports, weekly broilers, we publish 

194 reports in the branch, 33 reports over that whole year period. We have cold storage, mink,

slaughter, poultry, livestock, cattle, hogs, quarterly milk production, sheep and goats. We also do 

prices for livestock, bee and honey, cost of pollination, quarterly loss for colonies. And so we 

have numerous reports, Cattle on Feed is another one. So like Lance said we'll take questions but

we wanted to give you an introduction to each of our branches.

Good afternoon, my name is Jody McDaniel, for those of you that came today to data users,

you'll notice that I'm the branch chief that gets the least amount of questions and I'm okay with it. 

Part of that is because if it doesn't have legs Travis doesn’t really count it, if it doesn't grow out 

of the dirt Lance doesn’t really count it and I get essentially everything else. It's a wide spectrum 

it’s the Environmental, Economic and Demographics branch. We have the opportunity to take a 

front run on the Census of Agriculture. So anytime you hear about the number of farmers or any 

demographic group, there's a good opportunity that the staff in my branch actually take care of 

that. We also focus on the Current Ag Industrial Reports which are going to cover your grain 

crushings, fats and oils, cotton, as well as flour and milling. The Agricultural Resource 

Management Survey (ARMS), if you had an opportunity to sit in on the Economic Research 

Service meeting you heard quite a bit about that, it’s one of the main drivers into their income 

forecasting. So my staff has the opportunity to work with that. We also cover agricultural labor,

farms, land in farms, prices, parody prices and then a lot of the more special studies, not 

necessarily FRIS (Farm and Ranch Irrigation Survey) or Aquaculture that Dave mentioned 

earlier but the Local Foods Marketing, which we did the first survey of its kind, released a 

couple of years ago, as well as the TOTAL which is the land ownership and tenure. So pretty 

wide swath of unique things. Looking forward to any questions you might have.

Hopefully that gives you an idea of what we can cover while we're here, as I mentioned, the one

topic we want throw out there was how we tailor our program around the Census of Agriculture.
You've probably noticed that we don't necessarily have annual estimates of every single 

commodity that's produced in the U.S. or if you've looked at a particular commodity, you've 

probably noticed that there's rarely 50 states that we estimate on an ongoing basis. The reason for 

that is mostly driven by budget because to do every commodity and every state ongoing every 

year would be a daunting task. So what we do is we use the Census of Agriculture to evaluate 

where we need to focus our efforts in those in between years or on an ongoing basis. Our goal is 

typically to cover somewhere around 90 to 95 percent of production with the states that we cover 

in a commodity. That is not an exact number, if you can get, 85 percent of the production with 

four states and if you want to go to 90 you've got to add 40 more that's probably not a great use 

of our resources to get that extra 5 percent. That's just a snapshot of how we will use the Census

to help tailor what we do on an ongoing basis. So if you had any questions about that hopefully 

that gives an intro into that conversation, but as I mentioned, we really want this to be about you 

sharing either ideas or concerns with us or asking any additional questions you have. 

Attendee: From a CME Group perspective we're very agnostic or neutral on this topic, we 

don't have a stance, but it's about report release times during the trade session. 

Just so you know, we get lots of feedback. NCBA (National Cattlemen’s Beef 

Association) actually has policy on it. National Pork Producers Council does not 

but supports their viewpoint. Four or five years ago we never had a report 

released during the trade session, a couple years ago we had one, last year we had 

a couple, this year we've got three or four. What their request is can we push those 

reports now after the trading session, the day before when it needs to be 

depending on how it falls on the calendar. Where does it all mesh into that? Being 

a person who worked for Cargill for many, many, many years, I know the grain 

side is mostly comfortable with reports released during the session, their trading 

hours are very different than ours and their liquidity is very different than the 

livestock complex. So that's where we're getting most of the pushback. Is there a 

way on these holiday weeks where we're having these issues where the reports are 

coming out during the trade session, where we can either postpone it to where it 

was after the close or push it to the day before, or maybe push it to the day after, 

but again after the trading session? That's their request.

NASS: Your number one concern it's just the 3:00 p.m. normal timeframe that most of 

the livestock reports come out. If it's a day before or a day after, it’s just the 

timeframe at 3:00 p.m. not noon. 

Attendee: It doesn’t have to be on a Friday. I know the NCBA would love all the reports to 

be on Fridays at 3 p.m. Eastern time. It could be on a Wednesday after close if it 

needs to come out on that day, that's fine. I'm a little more bending than probably 

they are but their message is we just don't want it during the trading session.
Attendee: Inconsistency right? They don't want to have this one Friday where blows up with 

all the other reports released at 3 p.m.

Attendee: I know that’s really difficult on you all, because I know we talked about the 

bunching, as a person who used to analyze these reports and do it in my brokerage 

business, nothing's more demanding than having slaughter, Cold Storage, Cattle 

on Feed all on the same day. I understand that, but we'd sure like to move

slaughter a day before something along that line. 

NASS: Is bunching mostly a problem if they're all either all crops or all livestock, or is it 

a combination problem as well?

Attendee: In some cases yes. What we mostly follow is livestock but if it's going to be on a 

major crop production report, Grain Stocks comes out on Friday sometimes, Cold 

Storage I would lump in there, Hogs and Pigs, Acreage. We’re not necessarily 

worried about it falling with Rice Stocks. 

Attendee: From our standpoint and from what I hear from the livestock side and I've heard 

this also on the grain side about the bunching. Typically livestock is not a huge 

issue about bunching, it's a rare event. Their deal is about release times just after 

markets. I know we had three livestock reports last year on one day. I know you're 

limited on your time frames, I get that I didn't understand but now I understand 

that.

NASS: March was a perfect example with Hogs and Pigs and Prospective Plantings on 

the same day. That was a busy day for us. 

Attendee: Is there a benefit to you as an exchange? If you issue a report on Friday after 

close, foreign exchange competition and so forth, I remember when we first went 

to 23 hour trading one of the reasons that we took contracts to that long time, I 

come back to rice it’s what I know best, was because if we didn't have it available 

there that someone else was and then it became obvious that that wasn't a threat. 

So now rice is back to a 7:00 – 9:00 night session which I think was very much 

necessary as an exchange. But if you start issuing reports at 3 o’clock on a Friday 

and isn't changing that a potential competitive disadvantage?

Attendee: That definitely comes into play. One of the reasons we [CME] try to stay agnostic 

in this thing is that we understand they’re under constraints, but the marketplace is 

too. Hence why we have no problem with grain markets getting the reports 

released during the day is grains kind of trade a little bit. You see our hours of 

grain trade. It's kind of skittish but it's at different times during the night, you can 

trade overnight. That’s why they like those grain reports because they put them 

into the volume of the trade data and capture that at the CME.
Attendee: In the grain reports you've got an unequal dispersion of information. Computers 

have access to all the information before any private entity does, that's your

massive competitive disadvantage that didn't exist prior to electronics. In the 

country where I work, the people on grain reports are very much standoffish 

because by the time it gets to me, the computers have already chewed me up. So 

I'd rather get to the sidelines if I’ve got any concerns because I don't have the 

same shot as they do to put it in the market. If you close the market for 10 minutes 

from Eastern time 12:00 p.m. to 12:05 p.m. or 12:00 p.m. to 12:10 p.m. where 

that data can be consumed by everybody and then everybody is on equal footing. 

That would generate more trades. But I know that would put you guys in a very 

difficult scenario. You're not in the business of being worried about trading you’re 

in the business of putting out very good numbers. But the numbers are not 

distributed evenly in the country and that puts the country participants at a 

massive disadvantage, that's why country participants are moving more and more 

away from futures and options and closer and closer to doing cash contracts. 

That's an exchange issue, but I can tell you in five years we will be sitting in this 

room having a very different conversation if we stay the way we are today.

Attendee: Downstairs I just heard that ESMIS is going to be releasing files in csv, text, and 

pdf off of NASS's request. Do I take that as a sign that you guys will not be doing 

away with text files anytime soon the way AMS has? 

NASS: I don’t know that we have a timeline, I think we're committed to work toward 

getting more of our data out in a database format and try to at least draw back on 

these 40-50 page pdf documents for a multitude of reasons. One, I think, most 

folks want to just consume the numbers, it's not necessarily conducive to grabbing 

it quickly out of these publications. From an internal standpoint, it's a fairly 

significant effort for us to take all of those numbers and now create this 40 or 50 

page document. We'd rather spend our time analyzing those numbers and less 

time creating these pdf documents, but we don't have a specific timeline on any of 

those formats going away, we're just working toward streamlining what we do 

and making it more about the numbers and less about how many pages a 

document looks like. I don’t think you are going to lose your text files. 

NASS: The only thing I might add to that is that we’ve discussed a time table that we 

would like to see things happen and realistically we know it’s not going to happen 

but if I had my druthers, by next January we would be doing everything on short 

pubs and getting everything onto Quickstats on time for all reports, not just for the 

crop reports. If you heard about the delay downstairs, sometimes 8 minutes, 12 

minutes, 15 minutes. In a perfect world when a report is released, whether it’s 

noon or 3:00 pm, the data would be there. In the short run, I’d say within 3-5 
years, we’d have a short report maybe 2-4 pages long with the highlights but in 

the long run that would be gone. A lot of it has to do with the budget.

NASS: The bottom line is that 40-50 page publications have 35 tables in them and then 

proofing them out is resource intensive. It’s not just producing a number it’s also 

making sure you don’t have any typos. If we can put those on a diet and release 

the numbers in a way that is easily machine readable and in a timely fashion then 

we think this is a good product for our data users and it’s also a better use of 

resources internally. We have to continue to come up with ways to be more 

productive with the staff we have if we are going to preserve the programs. 

Attendee: I know we're not there yet but one thing that I'd be concerned with is how we get 

those revisions, like for the five year, how are we notified? Is that a manual thing, 

do I change my API to capture it all, etc. And even just for the year before, 

making sure everyone's aware there was a change. 

NASS: We also know one of the things we talked about is we have lots of footnotes. The 

data we collect is messy sometimes. Whether it's in California, the season is

different for oranges than it is in some other places or marketing year. Whatever 

we evolve to, if we are not producing the paper publications needs to reflect those 

footnotes so our data users understand what's going on with that data cell. Right 

now we have both Quickstats and a publication we feel pretty good about it but if 

we have some items that are no longer in a publication, that’s more problematic. 

Attendee: Are you guys going start reporting a premium hay price?

NASS: We're in the process right now of getting that stood up and going. It'll be a 

premium alfalfa price.

Attendee: Should I be worried about how many states that's going to include? Are we going 

to be able to get enough data to be able to report that price?

NASS: That is a great question I was hoping maybe you had the answer to that. Honestly, 

until we begin collecting that data, we can't guarantee exactly how much data

we’re going to get.

Attendee: It’s premium alfalfa, not premium and higher? It’s not going to be supreme or 

supreme dairy or some of these other terms they use with all of the hay options?

NASS: We're looking at two terms right now which is supreme and premium which we 

will most likely combine. But we've got to get in there and collect the data to see 

exactly how much data we're going to get and therefore, how many states we're 

going to be able to publish.
Attendee: And if it works really well your next question will be can you do that for fair or 

good because there's not that many AMS reports that cover other hay options.

NASS: We’re going to take it one step at a time. This is what we've been asked to do.

NASS: The latest modification in the Farm Bill requested NASS to do this for 5 states, 

not all states. Not even a U.S., the 5 highest milk producing states.

Attendee: Anybody know what the alfalfa market is like in the fifth highest state? California 

should be ok to get a price.

NASS: The way it's written it’s based on milk production. But the current budget says do 

the premium alfalfa for the U.S. and so we're looking at a lot of states right now, 

but we just don't know how many will be able to publish until we get the data.

Attendee: I looked at some crop data and I thought when I pulled the number it pulled in the 

most current crop year for either yield or acreage. Are the preliminaries included 

if I pull that down or if I said in Quickstats I want to pull your crop acres from

now until five years back? Does that include whatever the preliminary is?

NASS: If it's been published then yes.

Attendee: Even if it was just referring to planting? 

NASS: Yes. We have modified Quickstats in recent years to where you will be able to 

identify that specifically as March intentions. Right now you can only go back 

five or six years to specifically get the March intentions. It's going to take a little 

bit more of an effort for us to populate more history point in time, but moving 

forward we're trying to make sure that when you pull the latest you'll get that but 

then it will be forever marked as March intentions as well. 

Attendee: Do you have a crop progress graph for cotton in Louisiana?

NASS: Those are coming out or our Research Division. 

Attendee: The question is do you have those data for all states?

NASS: All of that data is in Quickstats. If you look at the National Crop Progress release, 

you will see a certain set of data. In some cases, there will be state level data that's 

not included in those tables because of various arrangements that we have at the 

state level. That data is all being loaded to Quickstats as well. So anything that's 

been published, whether it's a national report or just coming out of our regional 

offices that's all loading to Quickstats and the information in that graphic that you 

just highlighted all that progress information should be available in Quickstats.

NASS: Our research division produces that set of graphics and you see progress, in the 

bottom panel it looks like a bunch of S’s showing planting or squaring or 
harvesting and in the top it shows conditions across time. Those are available for 

each state that asks that set of questions along with a 5 year average. 

Attendee: The reason I was asking is that I am with the EPA Office of Pesticide Programs 

and there’s potential utility for us in this region for modelling when does this 

application occur and so we can use these curves.

NASS: Depending on which group in EPA you are with, we actually do work with them. 

Feel free to reach out and I can put you in contact with our chem use staff.

Attendee: We’ve been reporting on it for many years but we are not seeing much correlation 

between the crop condition throughout the year and final yield and production. 

We’re at a point where we’re wondering if it’s of value to our customers to report 

on this. We’re seeing poor conditions throughout the year and then at the end of 

the year we are seeing record yields. Is there any talk about finding a way to 

connect those numbers to production?

NASS: I think it's kind of like I touched on in the in the large session, you're almost 

talking about two different products. The goal of the weekly updates is just that,

to give you a weekly update of what we are seeing. As far as not seeing a 

correlation, most of the time most folks see a lot more correlation than for 

example they saw last year. Obviously this reared its ugly head last year because 

the two didn’t seem to be synchronized like they usually are. Now with that said, I 

have talked to some folks who have said, well, I've got a model that used that data 

and it was right in line with where the yields were coming so I presume it could 

have something to do with how that data is modeled to the yields as well. I'm 

certainly not suggesting that some folks just don't have the right model. I'm just 

saying there's so many ways you can slice and dice that data and try to link it up 

but internally we've never considered it to be how we're going to measure yield

because it has its limitations. It was never designed to measure yield. It’s designed 

to do what it says, it's a subjective reporting of conditions, progress, things of that 

nature from folks on the ground and the frequency is probably one of its greatest 

advantages. 

Attendee: One of the concerns of our commodity analysts was that some farmers are making 

marketing decisions based on what they are seeing throughout the year in the crop 

conditions. 

NASS: My guess would be after last year, there won't be as many. 

Attendee: Instead of doing market analysis around it, we are just reporting it.
NASS: There is another data product out there that comes from a remote sensing product, 

there’s Cropscape but also Vegscape that measure vegetative vigor and change to 

that. 

Attendee: But that also does not guarantee what the yield correlation will be.

NASS: This product may give some visibility and a different look at a similar 

phenomenon.

Attendee: Where is the vegetative data at?

NASS: It’s on our website. If you search for Vegscape or Cropscape it should come up.

Attendee: Last year was an outlier. Late July, August, September weather conditions and 

total moisture availability was significantly higher and high temperatures were 

lower than historical. Your crop condition indication was good but last year you 

kind of throw it out the window in August because of the way August played out. 

NASS: I would be careful not to think because of one strange season that that’s the new 

normal.

Attendee: This is a rice question. On your long grain export sales on the March 29th report 

you’re still at 71 million. You get the WASDE number on April 10 they dropped 

that export sales number down to 68, a three million reduction and yet we're 

currently 100% of last year's numbers which would be a 78 million pace in 

exports in rice. Who would I be able to talk to reconcile things better to give me 

some indication of what I'm missing?

NASS: I mean the export side of things, that's all in the WASDE we don’t have anything 

to do with that. But we can definitely get you in touch with the folks that can 

answer that for you. 
United States Department of Agriculture (USDA)

Agricultural Marketing Service (AMS)

Market Analysis & Reporting Services (MARS) 

API & Livestock Mandatory Price Reporting in 

Microsoft Excel

User Guide, v1.1 

U.S. Department of Agriculture 

Agricultural Marketing Service

1400 Independence Avenue SW 

Washington DC 20250

April 2018 
MARS API & LMPRS in Microsoft Excel

April 2018 DEPARTMENT OF AGRICULTURE Page 2 of 25

Table of Contents

1 MARS To Excel Overview.....................................................................................................4

1.1 Purpose of MARS API to Excel Help Guide ....................................................................................... 4

1.2 Document Audience ........................................................................................................................... 4

2 Usage .....................................................................................................................................4

2.1 Starting a new MARS API request ..................................................................................................... 4

2.2 Pulling a specific report from the MARS API.................................................................................... 13

3 Livestock Mandatory Price Reporting System Web Service to Excel............................22

3.1 Overview........................................................................................................................................... 22

3.2 Implementation ................................................................................................................................. 22
MARS API & LMPRS in Microsoft Excel

April 2018 DEPARTMENT OF AGRICULTURE Page 3 of 25

Change History

Date Change Version

11 APR 18 Initial Draft 1.0

17 APR 18 Added Livestock Mandatory Price Reporting Web Service 

to Excel Section

1.1
MARS API & LMPRS in Microsoft Excel

April 2018 DEPARTMENT OF AGRICULTURE Page 4 of 25

1 MARS To Excel Overview

This overview provides instructions to basic Excel users on how to connect to the Market 

Analysis & Reporting Services (MARS) Application Programming Interface (API) and access 

the desired data. This document goes step by step. 

1.1 Purpose of MARS API to Excel Help Guide

Both Microsoft Excel 2016, and 2013 (with the optional Power Query Tab installed) support data 

calls to web based API. The Microsoft 2013 Power Query Tab can be downloaded here1. In 

Excel 2016, the Tab is called “Data”. These instructions show how to link data sources to the 

MARS API from Excel file and automatically refresh to pull the latest data.

Figure 1 MS Excel Data Tab Toolbar

1.2 Document Audience

This overview was written to assist basic Excel users in configuring their Microsoft Excel 2013 

or 2016 to pull MARS data automatically into their own environment or network. 

2 Usage

In order to know specifically what reports are available, it is recommended that a Table of 

Contents be pulled from the MARS API before beginning your query.

2.1 Starting a new MARS API request

To start a new MARS API connection, click “From Web” on the Data Tab in Excel. 

 1 https://www.microsoft.com/en-us/download/details.aspx?id=39379
MARS API & LMPRS in Microsoft Excel

April 2018 DEPARTMENT OF AGRICULTURE Page 5 of 25

Enter the following URL to retrieve the table of contents from the MARS API: 

https://marsapi.ams.usda.gov/services/v1/reports. Click “OK”. The screen will pause for a few 

seconds while the request is made to the MARS API. The screen will refresh. 

Figure 2 “From Web" Menu.

The first time a Data Source is configured to access the MAR API, a key is required. 

Figure 3 Access Web Content Form

Select the “Basic” Tab and enter the MARS API key that is associated with your E-Auth account

into the User name field. Each regierestered user has a personal API key that can be found at My 
MARS API & LMPRS in Microsoft Excel

April 2018 DEPARTMENT OF AGRICULTURE Page 6 of 25

Market News2 in the user profile. Click “Connect”.

Figure 4 Basic Authentication Form

 2 https://mymarketnews.ams.usda.gov
MARS API & LMPRS in Microsoft Excel

April 2018 DEPARTMENT OF AGRICULTURE Page 7 of 25

The page will refresh and the Query Editor will launch.

Figure 5 Microsoft Query Editor
MARS API & LMPRS in Microsoft Excel

April 2018 DEPARTMENT OF AGRICULTURE Page 8 of 25

Place your mouse of the column heading titled “List”. Right click your mouse and select “Copy 

Entire List”

Figure 6 Click Copy Entire List

Click the “To Table” button

Figure 7 Click To Table Convert
MARS API & LMPRS in Microsoft Excel

April 2018 DEPARTMENT OF AGRICULTURE Page 9 of 25

A menu will appear. Click “OK”

Figure 8 To Table Selection Menu

The screen will refresh. There will be an icon to the right of “Column1”.

Figure 9 Column Selection
MARS API & LMPRS in Microsoft Excel

April 2018 DEPARTMENT OF AGRICULTURE Page 10 of 25

Click the “Double Arrow” icon. The screen will refresh. Click “OK”.

Figure 10 Column Selection Options
MARS API & LMPRS in Microsoft Excel

April 2018 DEPARTMENT OF AGRICULTURE Page 11 of 25

The screen will refresh again and show the following:

Figure 11 All Columns Shown

Click the “Close and Load” button. This will load all data into a new Excel worksheet. 

Figure 12 Close and Load Button
MARS API & LMPRS in Microsoft Excel

April 2018 DEPARTMENT OF AGRICULTURE Page 12 of 25

The worksheet will pause for a few second while the data is pulled from the MARS API. The 

screen will refresh and look like: 

Figure 13 Columns of Data Shown In Excel Worksheet
MARS API & LMPRS in Microsoft Excel

April 2018 DEPARTMENT OF AGRICULTURE Page 13 of 25

Now that we have a complete list of all available reports, you can now pull a specific report. 

Let’s go ahead and pull the “Cold Storage Weekly Report”. 

2.2 Pulling a specific report from the MARS API

Click on a new Worksheet in the Excel file. Click “From Web” on the Data Tab. 

Using the example on the MyMarketNews website, we will use this string 

(https://marsapi.ams.usda.gov/services/v1/reports/1095). The 1095 is Slug ID for the Cold 

Storage Weekly Report. If you wish to pull a different report, replace the Slug ID with the 

desired report number. Click “OK”.

Figure 14 From Web Input Form

This screen will show, while all the data is pulled:

Figure 15 Connecting Status Menu
MARS API & LMPRS in Microsoft Excel

April 2018 DEPARTMENT OF AGRICULTURE Page 14 of 25

The Query Editor menu will display:

Figure 16 Microsoft Query Editor Displayed
MARS API & LMPRS in Microsoft Excel

April 2018 DEPARTMENT OF AGRICULTURE Page 15 of 25

Place your mouse on the column titled “List” in green and right click. Select “Drill Down”.

Figure 17 Selection of Drill Down

The screen will refresh and display as below.

Figure 18 Transform Tab of Query Editor
MARS API & LMPRS in Microsoft Excel

April 2018 DEPARTMENT OF AGRICULTURE Page 16 of 25

Right click your mouse over the Column titled “List” and select “Copy Entire List”

Figure 19 Transform Tab "Copy Entire List" Option

Click the “To Table” button

Figure 20 To Table Convert Button
MARS API & LMPRS in Microsoft Excel

April 2018 DEPARTMENT OF AGRICULTURE Page 17 of 25

A menu will appear. Click “OK”

Figure 21 "To Table" Menu

The screen will refresh and display as below. 

Figure 22 Query Editor with data shown
MARS API & LMPRS in Microsoft Excel

April 2018 DEPARTMENT OF AGRICULTURE Page 18 of 25

The screen will refresh. There will be an icon to the right of “Column1”. Click the “Double 

Arrow” icon. The screen will refresh. Click “Ok”.

Figure 23 Click Double Arrow to expand
MARS API & LMPRS in Microsoft Excel

April 2018 DEPARTMENT OF AGRICULTURE Page 19 of 25

The screen will refresh and show. Click “Ok”. If you want to exclude columns from importing, 

de-select the columns here and click “Ok”

Figure 24 Column Selection Menu
MARS API & LMPRS in Microsoft Excel

April 2018 DEPARTMENT OF AGRICULTURE Page 20 of 25

The screen will refresh and appear like this.

Figure 25 Columns of Data Shown

Select “Close and Load” button. 
MARS API & LMPRS in Microsoft Excel

April 2018 DEPARTMENT OF AGRICULTURE Page 21 of 25

The screen will refresh and appear as below. You have successfully pulled data from the MARS 

API. You are free to manipulate, modify, or delete data here. This is your local copy of this data. 

At any time, if you want to refresh the data, click the “Refresh” button. The Excel file will 

automatically pull the data again from the MARS API. 
MARS API & LMPRS in Microsoft Excel

April 2018 DEPARTMENT OF AGRICULTURE Page 22 of 25

3 Livestock Mandatory Price Reporting System Web Service to Excel

3.1 Overview

The Livestock Mandatory Price Reporting System (LMPRS) offers a public facing Web Service 

that allowd users to query for publicly released aggregated data reports. The Help Guide for this 

web service is available https://mpr.ams.usda.gov/mpr/webServiceGuide.pdf. 

This web service is dated, has limited filtering capabilities, and only offers XML data extraction. 

It is expected that later in 2018, LMPRS data will be available in MARS via My Market News3. 

Once on MARS, our customers can pull this data through the API in a Json format. 

This guide explains how to create query strings using the URL to request data and provides 

provides several examples on how to tailor the data to the desired results. The examples use the 

LM_CT100 5 Area Daily Weight Average Direct Slaughter Cattle – Negotiated report, but these 

instructions apply to any LMPRS report. 

3.2 Implementation

By referencing the Help Guide (https://mpr.ams.usda.gov/mpr/webServiceGuide.pdf) , there are 

several working examples to pull from. Users are free to tweek the URL to the data query they 

desire. 

The URL for pulling “LM_CT100, 5 Area Daily Weighted Average Direct Slaughter Cattle –

Negotiated” for data between 01/08/2018 and 01/12/2018, below is: 

https://mpr.datamart.ams.usda.gov/ws/report/v1/cattle/LM_CT100?filter={"filters":[{"fieldName

":"Report%20date","operatorType":"BETWEEN","values":["01/08/2018","01/12/2018"]}]}

 3 https://mymarketnews.ams.usda.gov/
MARS API & LMPRS in Microsoft Excel

April 2018 DEPARTMENT OF AGRICULTURE Page 23 of 25

Select File >> Save As

Figure 26 LMPRS Data shown in XML format

The filename will populate with your selected data set. Save to a local location. Select “Save”.

Figure 27 Save XML File down to your local machine

Open Microsoft Excel with a blank Worksheet. 
MARS API & LMPRS in Microsoft Excel

April 2018 DEPARTMENT OF AGRICULTURE Page 24 of 25

Figure 28 Excel with blank worksheet

Select File >> Open >> Browse >> Select XML file. Select Open.

Figure 29 Select XML file

A menu will appear. Leave the default selection and select “Ok”.
MARS API & LMPRS in Microsoft Excel

April 2018 DEPARTMENT OF AGRICULTURE Page 25 of 25

Figure 30 Leave default selection and select OK

Data will automatically import and display as below: 

Figure 31 Imported XML Data showing.

The import of LMPRS data into the spreadsheet is static. It is not dynamically connected to 

LMPRS, as is the MARS API is. 

The AMS team is working to incorporate LMPRS data into the MARS API so that in the future, 

users will be able to have both voluntary and mandatory in one location. This tie in should be 

completed before the end of 2018.