United States Department of Agriculture (USDA) 

Agricultural Marketing Service (AMS) 

Livestock Mandatory Price Reporting (LMPR), 

Dairy Products Mandatory Reporting Program (DPMRP) & 

Federal Milk Marketing Orders (FMMOS)

Application Programming Interface (API) User Guide, v2.3 

U.S. Department of Agriculture 

Agricultural Marketing Service

1400 Independence Avenue SW 

Washington DC 20250 

December 2020 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 2 of 58

Table of Contents

1 Overview.............................................................................................................................. 4

1.1 Purpose of LMPR & DPMRP API ....................................................................................................... 4

1.2 Overuse .............................................................................................................................................. 4

1.3 Record Limit........................................................................................................................................ 4

1.4 “Report Date” vs. “Report End Date”.................................................................................................. 4

1.5 Document Audience ........................................................................................................................... 5

1.6 Definitions ........................................................................................................................................... 5

2 Design.................................................................................................................................. 5

2.1 Goals................................................................................................................................................... 5

2.2 Implementation ................................................................................................................................... 6

2.3 Important Note .................................................................................................................................... 6

2.4 Corrections ......................................................................................................................................... 6

2.5 Using Excel for an API request........................................................................................................... 7

2.6 Using Dynamic Parameters in Excel ................................................................................................ 12

3 Examples ...........................................................................................................................13

3.1 Livestock Report Examples .............................................................................................................. 14

3.2 Important Note on Report Section Names........................................................................................ 15

3.3 Dairy Examples................................................................................................................................. 26

3.3.1 Dairy Product Mandatory Reporting Program (DPMRP) Examples............................................ 26

3.3.2 Federal Milk Marketing Order Statistics (FMMOS) Examples..................................................... 39

3.4 Examples of Corrections................................................................................................................... 53

4 Report Holidays.................................................................................................................58
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 3 of 58

Change History

Date Change Version

06 APR 20 Initial Draft 1.0

07 APR 20 Updated to include examples for “AllSections” 1.1

29 APR 20 Section added to help identify the correct Section names 1.2

06 MAY 20 Includes updated Dairy examples, brief discussion on 

parameters, and an explanation for using Excel 

1.3

08 MAY 20 Added video link that explains using Excel Data Query 

“parameters” to make is easier to query dynamically. 

Add clarification on O365 in Section 2.4

1.4

01 JUN 20 Added Section 1.3 Record Limit clarification statements. 1.5

02 JUN 20 Added Section 1.4 offering clarification on 

“Report_Date” and “Report_End_Date” 

1.6

10 JUN 20 Updates to API to support a between clause for

“Published_Date” 

1.7

25 JUN 20 Updates to API to support using multiple variables 

together such as Report Year and Report Month 

1.8

27 JUL 20 Updates made to Section 2.4 regarding Microsoft Excel 

2016, (32 bit) versions. 

1.9

13 AUG 20 Updated to include examples for “Final Prices” for the 

National Dairy Products Sales Report

2.0

4 SEP 20 Updated National Dairy Products Sales Report examples 2.1

10 NOV 20 Added two new reports to the list of FMMOS reports in 

Section 3.3.2 

2.2

21 DEC 20 Added new examples of enhanced API features related 

to “Corrections” under Section 2.4. Examples are 

provided under Section 3.4. 

2.3
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 4 of 58

1 Overview

1.1 Purpose of LMPR & DPMRP API

The LMPR & DPMRP & FMMOS API allows public access to Livestock Mandatory Price 

Reporting (LMPR), Dairy Products Mandatory Reporting Program (DPMRP), and Federal Milk 

Marketing Orders (FMMOS) market report information. 

The output of the LMPR API is JavaScript Object Notation (JSON). JSON is an open standard 

format and data interchange format. This file format uses human-readable text to store and 

transmit data objects consisting of attribute–value pairs and array data types (or any other 

serializable value). It is a very common data format and easily consumable in various 

applications. 

1.2 Overuse

Overloading the LMPR API with high frequency automated requests unnecessarily taxes 

computing resources. High frequency requests consume all the network bandwidth, create 

website performance issues often causing the website to crash, and reduce data availability to 

other customers attempting to reach the site. To mitigate overloading the systems, AMS will 

temporarily block IP addresses found taxing the systems with high frequency requests. 

If you find DataMart unavailable, or notice performance issues on your LMPR API request, it is 

possible your IP address has been temporarily blocked. Often these high frequency requests 

occur due to simple coding errors. Please email Wash.LPGMN@ams.usda.gov for assistance 

restoring your access. 

1.3 Record Limit

Both the LMR and MyMarketNews API’s limit data calls record results to 100,000 per request. 

This is done so as to not overwhelm the system and ensure that the systems remain operational, 

responsive and available to all parties. 

1.4 “Report Date” vs. “Report End Date” 

AMS would like to make users aware that certain reports that, since inception, have slightly 

different “Report Date” offerings. Certain Summary reports may not offer “Report Date”, but 

will offer “Report End Date”. Examples of this would be in, but not limited to, are LM_CT106, 

LM_CT109, & LM_CT168. Pending future budgetary conditions, AMS may be able to work 

towards standardizing this, but it is not in the immediate future. AMS recommends checking the 

“Summary” section in DataMart, LMR Web Service XML or LMR API to determine if the 

report uses “Report Date” or “Report End Date”. 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 5 of 58

1.5 Document Audience

This document is technical in nature. This document was written to assist technical support staff 

in configuring LMPR API to pull data into their own environment or network. This document 

contains technical information and is not intended for non-technical audience(s). 

1.6 Definitions

Abbreviation Definition

AMS Agricultural Marketing Service

DPMRP Dairy Product Mandatory Reporting Program 

FMMOS Federal Milk Marketing Order Statistics

HTTPS Hypertext Transfer Protocol Secure

JSON JavaScript Object Notation 

LPGMN Livestock, Poultry, and Grain Market News 

LMPR Livestock Mandatory Price Reporting 

MN Market News

REST Representational State Transfer

URL Uniform Resource Locator

USDA United States Department of Agriculture

2 Design

2.1 Goals

The LMPR API is designed to meet these goals:

• Simple 

• Lightweight 

• Flexible

• Intuitive

• Extendable 

• Integration available

• Minimal development effort required 

• Structurally predictable 

• Consumable 

Every effort has been made to ensure the LMPR API works in the same fashion as the 

MyMarketNews API; however the LMPR data structure is different than MyMarketNews. 

LMPR has a slightly different underlying database structure than MyMarketNews. These 

differences may show themselves in LMPR API request that yield slightly different data set 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 6 of 58

results. 

2.2 Implementation

The LMPR API does not require a user key like the MyMarketNews API requires. User request 

are unrestricted with no limit on record counts or restrictions. 

Market News will monitor the usage of the LMPR API for abuse. If the system shows excessive 

taxation where other user request are being affected, LPGMN will move to limit, restrict, or 

block abusive user request. 

The LMPR API is set to Central Standard Time (CST) time zone. Standard and daylight time 

rules apply. 

2.3 Important Note

The LMPR API offers access to the same data set that is available at

https://mpr.datamart.ams.usda.gov/ and the LMPR Web Service. If the report is not on the 

DataMart website or in the current Web Service, then it is not available via the LMPR API. 

LPGMN uses both Postman and Microsoft Excel to show data sample in this User Guide. 

LPGMN does not endorse either product but references them to aid in articulating expected 

results. 

Before creating API requests either in Postman or Excel, there are few filter parameter standards 

that a data user must know. First a common way to limit the data received from your API request 

is limiting it by a time threshold. The most common variables used to limit your data by time are 

report_date, report_year and report month. To add this to your request use the following syntax: 

?q=time variable=value (e.g. ?q=report_year=2018 or ?q= report_date=09/15/2017) 

If you want to limit your API request to a range of values use a colon in the value portion of the 

parameter (e.g. ?q=report_year=2012:2014). 

There are two other useful parameters that are commonly used for API requests. The first is the 

&sort=variable (e.g. &sort=report_date). This sorts the results of your API request based on the 

variable you supply in the &sort parameter. The second is the &allSections=True parameter. 

Almost all the reports that use the LMPR API have different sections to the report. You can use 

your API request to pull a certain section of the report or use the &all Sections=True parameter 

to pull all sections of the report at one time. There are helpful examples later in this 

documentation that illustrate how to use both the &sort= and the &allSections=True parameters. 

2.4 Corrections 

In late December 2020, the LMR API introduced support for identifying and consuming Report 

Corrections. Additionally, enhanced syntax was introduced that allows for consuming X amount 

of days of data and X amount of a particular report. Samples of that syntax is listed in Examples 

section titled “Examples of Corrections”
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 7 of 58

Users now have the abilty to: 

1. List reports that are “Correction”

2. List reports that are “Correction” since X amount of days. 

3. List reports in the last X amount of days. 

4. List X amount of a particular report. 

2.5 Using Excel for an API request 

Both Microsoft Excel 2016 (64 bit), 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 

here (https://www.microsoft.com/en-us/download/details.aspx?id=39379). 

Note: Users who have Office 365 may have slightly different menus or slighty different steps 

than the ones shown below. 

In Excel 2016, the Tab is called “Data”. By following the instructions included in this document, 

you will be creating linked data sources to the LMR API from an Excel file. Clicking the 

“Refresh” button automatically connects to the LMR API and pulls the latest publicially 

available data. 

To start a LMR API connection, click “From Web” on the Data Tab. For Microsoft Excel 2016 

(32 bit), users should select “Get Data” >> “From Other Sources” >> From Web. 

Enter the URL for the report you would like to get. For this example we will pull the data for the 

Table of Contents (https://mpr.datamart.ams.usda.gov/services/v1.1/reports/). Click “Ok”. The 

screen will pause for a few seconds while the request is made to the LMR API. The screen will 

refresh. 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 8 of 58

The page will refresh, and the Query Editor will launch. 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 9 of 58
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 10 of 58

Place your mouse of the column heading titled “List”. Right click your mouse and select “Copy 

Entire List”

Click the “To Table” button 

A menu will appear. Click “Ok” 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 11 of 58

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”.
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 12 of 58

The screen will refresh again and show 

Click the “Close and Load” button. This will load all data into a new Excel worksheet. 

2.6 Using Dynamic Parameters in Excel 

There are ways to dynamically pass parameters to the Data Query Editor on the fly to pull 

different data. A YouTube video outling how to do that is located here: 

https://www.youtube.com/watch?v=sdR2BI2e5Y8&feature=youtu.be
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 13 of 58

3 Examples

The LMPR API offers a table of contents of all published reports accessible @

https://mpr.datamart.ams.usda.gov/services/v1.1/reports/. 

Legacy Slug-ID were added to the Report Title for ease of identification. All specific report drill 

down queries should be done using the new Slug-ID column denoted below in column A. 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 14 of 58

3.1 Livestock Report Examples

To pull the Summary Section of the “5 Area Daily Weighted Average Direct Slaughter Cattle – 

Negotiated (LM_CT100)”, the sample syntax would be: 

https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2466

Note the usage of the slug_id to access this particular report. Results by default show the most 

recent report first. 

Expected results would be:
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 15 of 58

3.2 Important Note on Report Section Names

The sections of each report differs depending on the commodity. The recommended way to 

identify unqiue “Section” names is to query the Summary first. Examples are below. 

Example 1: 

https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2668/?q=report_date=03/09/2020&allSe

ctions=true

Example 2: 

https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2511/?q=report_date=03/09/2020&allSe

ctions=true

Example 3: 

https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2676/Summary?q=report_date=03/09/2

020&allSections=true

Example 4: 

https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2993?q=week_ending_date=3/28/2020

&allSections=true
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 16 of 58

To pull the Summary for this same report (“5 Area Daily Weighted Average Direct Slaughter 

Cattle – Negotiated (LM_CT100)”), but for only one report_date, the sample syntax would be: 

https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2466/Summary?q=report_date=08/05/2

019

Expected results would be: 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 17 of 58

To access the Detail section of “5 Area Daily Weighted Average Direct Slaughter Cattle – 

Negotiated (LM_CT100)” for the same report_date, the sample syntax would be: 

https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2466/Detail?q=report_date=08/05/2019

Expected results would be: 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 18 of 58

To pull All Sections of the “5 Area Daily Weighted Average Direct Slaughter Cattle – 

Negotiated (LM_CT100)” at one time, the sample syntax would be: 

https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2466?q=report_date=3/30/2020&allSect

ions=true

Expected results would be:
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 19 of 58

To access the Summary section of “5 Area Daily Weighted Average Direct Slaughter Cattle – 

Negotiated (LM_CT100)” with a published date of 03/25/2020, the sample syntax would be: 

https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2466?q=published_date=03/25/2020

Special note. The published date query also accepts HH:MM:SS as shown below. 

The published date query accepts either, and on any section of a report 

https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2466?q=published_date=03/25/2020 

10:45:06

Expected results would be: 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 20 of 58

To access the Summary section of “"National Daily Pork FOB Plant - Negotiated Sales - 

Afternoon (PDF) (LM_PK602)” with a published date between 05-01-2020 and 05-06-2020, the 

sample syntax would be: 

https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2498/Summary?q=published_date=2020

-05-01:2020-05-06

Expected results would be: 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 21 of 58

To access the Detail section of “5 Area Daily Weighted Average Direct Slaughter Cattle – 

Negotiated (LM_CT100)” for the report_date range of 08/05/2019 to 08/06/2019, but add a Sort 

filter on previous_day_head_count field, the sample syntax would be: 

https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2466/Detail?q=report_date=08/05/2019:

08/06/2019&sort=previous_day_head_count 

Expected results would be: 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 22 of 58

To pull the Detail section of “5 Area Daily Weighted Average Direct Slaughter Cattle – 

Negotiated (LM_CT100)” for the report_date range of 08/05/2019 to 08/06/2019, but only select 

class_description of STEER with a selling_basis of LIVE DELIVERED, the sample syntax 

would be: 

https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2466/Detail?q=report_date=08/05/2019:

08/06/2019;class_description=STEER;selling_basis_description=LIVE DELIVERED

Expected results would be: 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 23 of 58

To pull the Detail section of “5 Area Daily Weighted Average Direct Slaughter Cattle – 

Negotiated (LM_CT100)” for the report_date range of 08/05/2019 to 08/06/2019, but only select 

class_description of either STEER or HEIFER, the sample syntax would be: 

https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2466/Detail?q=report_date=08/05/2019:

08/06/2019;class_description=STEER,HEIFER

Expected results would be: 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 24 of 58

To pull the Detail section of “5 Area Daily Weighted Average Direct Slaughter Cattle – 

Negotiated (LM_CT100)” for the report_date range of 08/05/2019 to 08/10/2019, but only select 

class_description of STEER with a selling_basis of LIVE DELIVERED sorted with the oldest 

published_date first, the sample syntax would be: 

https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2466/Detail?q=report_date=08/05/2019:

08/10/2019;class_description=STEER;selling_basis_description=LIVE 

DELIVERED&sort=published_date

Expected results would be: 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 25 of 58

To pull the Detail section of “5 Area Daily Weighted Average Direct Slaughter Cattle – 

Negotiated (LM_CT100)” for the report_date range of 08/05/2019 to 08/10/2019, but only select 

class_description of STEER with a selling_basis of LIVE DELIVERED sorted with more recent 

published_date first, the sample syntax would be: 

https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2466/Detail?q=report_date=08/05/2019:

08/10/2019;class_description=STEER;selling_basis_description=LIVE DELIVERED&sort=-

published_date

Note the “-“ before the published_date sort syntax. Expected results would be: 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 26 of 58

3.3 Dairy Examples 

3.3.1 Dairy Product Mandatory Reporting Program (DPMRP) Examples

To pull the Summary Section of the National Dairy Products Sales Report, the sample syntax 

would be: 

https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2993

Denote the usage of the Slug_ID to access this particular report. Results by default show the 

most recent report first. In addition, the summary section for Dairy reports will not show any 

data. 

Expected results in Postman: 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 27 of 58

Expected result in Excel:
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 28 of 58

The “National Dairy Products Sales Report” is comprised of six sections, including Summary 

(above), Butter, Cheddar 40s, Cheddar 500s, Dry Whey, and Nonfat Dry Milk Sections. To pull 

the each Section of the “National Dairy Products Sales Report”, the sample syntaxes would be: 

- Butter - https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2993/Butter Prices and 

Sales

- Cheddar 40s - https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2993/40 Pound 

Block Cheddar Cheese Prices and Sales

- Cheddar 500s - https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2993/500 Pound 

Barrel Cheddar Cheese Prices, Sales, and Moisture Content

- Dry Whey - https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2993/Dry Whey 

Prices and Sales

- Nonfat Dry Milk - https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2993/Nonfat 

Dry Milk Prices and Sales

Denote that there are six sections to this report. When setting up your workbook you will need to 

pull one section per tab. For these examples the URL is the same for Postman and Excel.

Expected results in Excel for butter: 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 29 of 58

To pull the Butter section for this same report “National Dairy Products Sales Report” but for 

only one report date, the sample syntax for Postman and Excel would be: 

https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2993/Butter Prices and 

Sales?q=week_ending_date=03/14/2020

Expected results in Postman: 

Expected results in Excel:
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 30 of 58

To access the Butter section of “National Dairy Products Sales Report” for the date range of 

02/22/2020 to 03/14/2020, but Sort with the oldest date first, the sample syntax for Postman and 

Excel would be: 

https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2993/Butter Prices and 

Sales?q=week_ending_date=02/22/2020:03/14/2020&sort=published_date

Expected results in Postman: 

Expected results in Excel: 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 31 of 58

To pull the all sections of the National Dairy Products Sales Report, but for only one report date 

the sample syntax for Postman and Excel would be: 

https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2993?q=week_ending_date=3/28/2020

&allSections=true

Expected results:
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 32 of 58

The “National Dairy Products Sales Report” report allows revisions to the four weeks of data 

prior to the current reporting week. To pull final price and volume information that includes all 

revisions, the sample syntaxes would be: 

- Butter – https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2993/Final Butter Prices 

and Sales

- Cheddar 40s - https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2993/Final 40 

Pound Block Cheddar Cheese Prices and Sales

- Cheddar 500s - https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2993/Final 500 

Pound Barrel Cheddar Cheese Prices, Sales, and Moisture Content

- Dry Whey - https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2993/Final Dry 

Whey Prices and Sales

- Nonfat Dry Milk - https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2993/Final 

Nonfat Dry Milk Prices and Sales

Denote that for these examples the URL is the same for Postman and Excel. 

Expected results in Excel for dry whey: 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 33 of 58

To pull the Detail section for the “Announcement of Class and Component Prices”, the sample 

syntax would be: 

https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2991/detail

Denote that for this example the URL is the same for both Postman and Excel. 

Expected results in Postman: 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 34 of 58

Expected results in Excel: 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 35 of 58

To pull the Detail section for the “Announcement of Advanced Prices and Pricing Factors”, the 

sample syntax would be: 

https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2989/detail

Denote that for this example the URL is the same for both Postman and Excel. 

Expected results in Postman: 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 36 of 58

Expected results in Excel: 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 37 of 58

To pull the Detail section for the “Announcement of Advanced Prices and Pricing 

Factors” for the entire year of 2020 but Sort with the oldest date first, the sample syntax 

would be: 

https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2989/Detail?q=week_ending_date

=01/04/2020:01/02/2021&sort=published_date

Denote that the week_ending_date parameter is used in a range form of 01/04/2020 to 

01/02/2021. This range encompasses all the data for year 2020. The URL is the same for 

both Postman and Excel. 

Expected results in Postman: 

Expected results in Excel:
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 38 of 58

To pull the Detail section for he “Announcement of Advanced Prices and Pricing 

Factors” for only one report date, e.g. the May Advanced Prices and Pricing Factors

report, the sample syntax would be: 

https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2989/Detail?q=week_ending_dat

e=04/18/2020

Denote the URL is the same for both Postman and Excel. 

Expected results in Postman: 

Expected results in Excel: 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 39 of 58

3.3.2 Federal Milk Marketing Order Statistics (FMMOS) Examples

Listed below are some easy ways to pull FMMOS data by section of a particular report using the 

example syntax: 

 

https://mpr.datamart.ams.usda.gov/services/v1.1/reports/nnnn/ssssssss

nnnn = Slug_ID of the desired report. 

sssssssss = section name

On the following page, please refer to a table of the FMMOS reports, Slug_ID’s and section 

names.
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 40 of 58

Slug_ID Report Report Sections

3345 Class I 

Prices

Summary Milk Butterfat Skim

3346 Class I 

Utilization

Summary Milk Utilization Butterfat NFS

3347 Class II 

Utilization

Summary Milk Utilization Butterfat NFS

3348 Class III 

Utilization

Summary Milk Utilization Butterfat Protein Other 

Solids

3349 Class IV 

Utilization

Summary Milk Utilization Butterfat NFS

3350

(Pool 

Data 

Prior to 

October 

2020)

Total 

Receipts of 

Producer 

Milk

Summary Producers Receipts Avg Daily Butterfat

NFS Protein Other 

Solids

SomCell

3461 

(Pool 

Data 

starting 

with 

October 

2020)

Producer 

Receipts

Summary Producers Receipts Avg Daily

3462 

(Pool 

Data 

starting 

with 

October 

2020)

Producer 

Milk 

Components 

Summary Receipts Butterfat NFS Protein

Other 

Solids

SomCell

3351 Uniform 

Milk Prices

Summary Milk Butterfat Skim PPD

3352 Price and 

Pool – 

Monthly

Summary Price and Pool Monthly

3353 Price and 

Pool – 

Annual

Summary Price and Pool Annual

3354 Advanced 

Prices by 

Order

Summary Advanced Class Prices by Order

3355 Class Prices 

by Order

Summary Final Class Prices by Order
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 41 of 58

3356 Retail Prices Summary Conventional Whole 

Milk

Conventional Reduced Fat

Organic Whole Milk Organic Reduced Fat

3357 Mailbox 

Milk Prices

Summary Mailbox Milk Prices

3358 Estimated 

Fluid Milk 

Sales

Summary Estimated Total U.S. Sales of Fluid Milk Conventional Products

Estimated Total U.S. Sales of Fluid Milk Organic Products

Total Package Sales of Fluid Milk Products

Estimated Total U.S. Sales - Conventional, Organic and Total

Estimated U.S. Sales of Conventional Fluid Milk Products by 

Month

Estimated U.S. Sales of Organic Fluid Milk Products by Month

Total Package Sales of Fluid Milk Products by Month

3359 Regulated 

Pool Plant 

Lists

Summary Distributing Plants by Month Supply Plants by Month

To pull the Milk Section of the “Class I Utilization”, the sample syntax for Postman would be: 

https://mpr.datamart.ams.usda.gov/services/v1.1/reports/3346/Milk

Denote the usage of the Slug_ID to access this particular report. 

Expected results in Postman: 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 42 of 58
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 43 of 58

To use the API request in Excel, start by following the first few steps on page 7 above. You will 

then see the following: 

Next, click on the Into Table button on the top left part of the screen. Next you will see: 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 44 of 58

At this point, right click on the List cell in the second column. Click on Drill Down on the menu 

that pops up. Next you will see a list of records: 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 45 of 58

Again, you will click on the To Table button in the upper left part of the window. Click Ok when 

prompted. Your list will now look like this:

Click on the double arrows icon that is circled in red above. Click OK when prompted. This will 

all the variables associated with the records from your API request as shown below. 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 46 of 58

At this point, you may sort your data as you choose, and you can remove any columns of data 

that are not needed for your analysis purposes. I have sorted and reduced the number of variables 

for the Expected results in Excel snaps you see on the next number of pages. 

Expected results in Excel: 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 47 of 58

To pull the 2018 Butterfat Section of the “Class I Prices”, the sample syntax for Postman would 

be: 

https://mpr.datamart.ams.usda.gov/services/v1.1/reports/3345/Butterfat?q=report_year=2018

Denote the usage of the Slug_ID and the report_year variables to access this particular report. 

Expected results in Postman: 

Expected results in Excel: 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 48 of 58

To pull the Summary Section for years 2015-2018 of the “Uniform Milk Prices”, the sample 

syntax for Postman would be: 

https://mpr.datamart.ams.usda.gov/services/v1.1/reports/3351/Summary?q=report_year=2015:20

18

Denote the usage of the Slug_ID and report_year range to access this particular report. NOTE: 

This pull would only give you confirmation of the years selected but not any actual data from 

this report. 

Expected results in Postman: 

Expected results in Excel: 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 49 of 58

To pull all the report sections for 2017 of the “Total Receipts of Producer Milk”, the sample 

syntax for Postman would be: 

https://mpr.datamart.ams.usda.gov/services/v1.1/reports/3350?q=report_year=2017&allSections

=true

Denote the usage of the Slug_ID, report_year, and allSections variables to access this particular 

report. 

Expected results in Postman: 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 50 of 58

Expected results in Excel: 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 51 of 58

At certain times a data user may want to pull a report for just one month of a particular 

year. In that situation you would use both the report_year and report_month parameters. 

To pull the Price and Pool Monthly section for August 2018 of the “Price and Pool - 

Monthly”, the sample syntax for Postman would be: 

https://mpr.datamart.ams.usda.gov/services/v1.1/reports/3352/Price and Pool 

Monthly?q=report_year=2018;report_month=aug 

Denote the usage of the Slug_ID, report_year, and report_month (in three characters, e.g. 

report_month=feb) variables to access this particular report.

Expected results in Postman: 

Expected results in Excel:
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 52 of 58

Please note that the requesting of just one specific month for a particular year will only work on 

the following reports that have a unique report for each month: 

 3352 – Price and Pool - Monthly 

 3354 – Advanced Prices by Order 

 3355 – Class Prices by Order

 3358 – Estimated Fluid Milk Sales

The remainder of the FMMOS reports simply build a year-to-date table that eventually has all 

monthly data included. For the remainder of the tables, any request can only use 

report_month=dec. 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 53 of 58

3.4 Examples of Corrections

To pull Corrections only of the Detail section of “5 Area Daily Weighted Average Direct 

Slaughter Cattle - Negotiated (PDF) (LM_CT100)” the sample syntax would be: 

https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2466/Detail?correctionsOnly=true

Expected results would be: 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 54 of 58

To pull Corrections only of the Detail section of “5 Area Daily Weighted Average Direct 

Slaughter Cattle - Negotiated (PDF) (LM_CT100)” since 04 July 2020, the sample syntax would 

be: 

https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2466/Detail?correctionsOnly=true&any

ChangesSince=7/4/2020

Expected results would be: 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 55 of 58

To pull the Detail section of “5 Area Daily Weighted Average Direct Slaughter Cattle - 

Negotiated (PDF) (LM_CT100)” for the last one hundred days, the sample syntax would be: 

https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2466/Detail?lastDays=100

Expected results would be: 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 56 of 58

To pull last five reports of the Detail section of “5 Area Daily Weighted Average Direct 

Slaughter Cattle - Negotiated (PDF) (LM_CT100)”, the sample syntax would be: 

https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2466/Detail?lastReports=5

Expected results would be: 
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 57 of 58
Title: LMPR API User Guide v2.3 

December 2020 DEPARTMENT OF AGRICULTURE Page 58 of 58

4 Report Holidays

There are six national holidays that are usually observed when reports are not issued. Reports 

resume following these holidays. The observed dates do not follow actual dates for the holiday, 

but are a subset of observed holidays derived from the U.S. OPM Federal Holiday schedule. 

The six holidays normally selected from this schedule are as follows: 

1. New Year’s Day

2. Memorial Day

3. Independence Day

4. Labor Day

5. Thanksgiving Day 

6. Christmas Day

Besides the holidays above, extenuating circumstances may also impact the dates when reports 

are issued.