PartsAndPlanes in Excel

Type part numbers down a column. Paste one formula beside the first one. Fill down. Excel fills in what DLA last paid for each stock number, the date, how many solicitations are open on it now, and the NSN. Live from the record, free, no key, no add-in. Ten minutes if you have never done it.

Works in Excel for Windows and Microsoft 365 desktop. Excel on the web and Excel for Mac lack the WEBSERVICE function; use the Google Sheets steps at the bottom there.

Step 1: a new sheet

Open a blank workbook. In A1 type Part number. In A2, A3 and A4 type three part numbers, one per cell. Try these three, which show a priced result, a stock number, and a part with no priced award:

MS20470AD4-6
5310-00-807-1474
NAS1149F0363P

Step 2: the first formula (last paid)

Click B1 and type Last paid. Click B2, paste this exactly, press Enter:

=IFERROR(VALUE(WEBSERVICE("https://endpoint.partsandplanes.com/feed/cell?q="&ENCODEURL(A2)&"&f=last_paid")),"")

Excel asks the site for that one number and shows it. A blank means DLA has no priced award on record for that number; the NSN and the open count still fill in.

Step 3: the other columns

Headers in C1 to G1, formulas in C2 to G2:

C1  Last award date
C2  =WEBSERVICE("https://endpoint.partsandplanes.com/feed/cell?q="&ENCODEURL(A2)&"&f=last_date")

D1  Open DLA RFQs
D2  =IFERROR(VALUE(WEBSERVICE("https://endpoint.partsandplanes.com/feed/cell?q="&ENCODEURL(A2)&"&f=open_dla")),"")

E1  NSN
E2  =WEBSERVICE("https://endpoint.partsandplanes.com/feed/cell?q="&ENCODEURL(A2)&"&f=nsn")

F1  Approved sources
F2  =IFERROR(VALUE(WEBSERVICE("https://endpoint.partsandplanes.com/feed/cell?q="&ENCODEURL(A2)&"&f=approved_sources")),"")

G1  Page
G2  =HYPERLINK(WEBSERVICE("https://endpoint.partsandplanes.com/feed/cell?q="&ENCODEURL(A2)&"&f=url"),"open")

Step 4: fill down

Select B2 through G2. Point at the small square in the bottom-right corner of the selection (the cursor becomes a thin black plus) and double-click it. Excel copies the formulas down as far as column A has part numbers. Or select B2:G200 and press Ctrl+D.

Step 5: make it a table (optional)

Click any cell in the block, press Ctrl+T, tick “My table has headers”, OK. New part numbers typed at the bottom get the formulas by themselves.

Things to know

• Each cell is one request. A sheet of 300 part numbers with six columns makes 1,800 requests the first time it calculates; the limit is 3,000 an hour and 5,000 a day from one address (write to [email protected] for a key above that). Excel recalculates when you open the file or change a cell; to stop that, Formulas → Calculation Options → Manual, then F9 when you want fresh numbers.
• #VALUE! means the site could not be reached (no internet, or a firewall that blocks Excel). An empty cell means the record has no value for that field; that is the answer, not an error.
• Numbers arrive as text unless wrapped in VALUE(), which is why the price and count formulas have it and the date, NSN and page formulas do not.
• Data, not advice: last_paid is one award's unit price on one date; quantity and terms differed.

Every field you can ask for

Change the part after f= in any formula:

Google Sheets (Mac, web, phone)

The quick way: in B2 paste this and every field lands across the row (header row too), then copy it down:

=IMPORTDATA("https://endpoint.partsandplanes.com/feed/cell.csv?q="&ENCODEURL(A2))

Or a PNP() function for one clean value per cell. In the sheet: Extensions → Apps Script, delete what is there, paste this, click the save icon:

function PNP(part, field) {
  field = field || "last_paid";
  var url = "https://endpoint.partsandplanes.com/feed/cell?q=" + encodeURIComponent(part) + "&f=" + encodeURIComponent(field);
  var t = UrlFetchApp.fetch(url, { muteHttpExceptions: true }).getContentText().trim();
  return (t !== "" && !isNaN(t)) ? Number(t) : t;
}

Back in the sheet: =PNP(A2, "last_paid"), =PNP(A2, "open_dla"), =PNP(A2, "nsn"). The first time, Google asks you to authorize the script (it is your own script reading a public page); allow it. Fill down as in Excel.

Technical reference, including the CSV endpoint and the API: /developers. Other ways in: integrations. Stuck? [email protected], and I will walk you through it.