CSV Data in Google Sheets & Excel

OpenFacet publishes daily CSV price lists for spreadsheet workflows. These files are the simplest way to bring OpenFacet pricing into Google Sheets or Microsoft Excel without using JSON or custom scripts.

If you want ready-to-use, formula-driven examples (Excel-style and Google Sheets), see:

Data Explorer spreadsheet demo — demonstrates XLSX formula-driven computation of arbitrary carat sizes from OpenFacet CSV data across different shapes.
Google Sheets public template (make a copy)

When you open these templates, you may see this prompt while CSV files are fetched:

Warning: Some formulas are trying to send and receive data from external parties.

Click Allow access so the shape CSV tabs can load and the interpolation formulas can calculate prices.

Unlike the JSON matrix endpoints, the CSV files already contain USD per-carat prices in flat tabular form:

carat,color,clarity,price
0.3,D,FL,1959
0.3,D,IF,1889
0.3,D,VVS1,1700
0.3,D,VVS2,1716
0.3,D,VS1,1682
0.3,D,VS2,1648
0.3,D,SI1,1615

CSV URLs

The shape files follow this pattern:

https://data.openfacet.net/list_<shape>.csv

Examples:

https://data.openfacet.net/list_round.csv
https://data.openfacet.net/list_cushion.csv
https://data.openfacet.net/list_radiant.csv
https://data.openfacet.net/list_emerald.csv
https://data.openfacet.net/list_oval.csv
https://data.openfacet.net/list_pear.csv
https://data.openfacet.net/list_marquise.csv
https://data.openfacet.net/list_heart.csv

What the CSV columns mean

carat: carat anchor in the daily list
color: color grade
clarity: clarity grade
price: USD per carat, not total stone price

So if a row says price = 4490 and your stone is 1.23ct, then the total stone price is:

$$ \text{total price} = \text{carat} \times \text{price per carat} $$

Important note about interpolation

The CSV files expose USD per-carat prices, but OpenFacet’s interpolation logic is derived from log-price space.

That means when your target carat falls between two listed carat anchors, the spreadsheet formula should match the model by using:

$$ \lambda = \frac{c - c_1}{c_2 - c_1} $$

$$ P(c) = \exp\left((1-\lambda)\ln(P_a) + \lambda\ln(P_b)\right) $$

Where:

$c$ = requested carat
$c_1$ = lower matching carat in the CSV
$c_2$ = upper matching carat in the CSV
$P_a$ = lower USD per-carat price
$P_b$ = upper USD per-carat price

So even though the CSV holds USD values, the correct interpolation is geometric/log-linear, not a simple straight-line average in dollar space.

Google Sheets

If you only need round prices, you can start with:

=IMPORTDATA("https://data.openfacet.net/list_round.csv")

If you want to build a more flexible sheet that can handle all shapes and do interpolation, here’s a recommended setup:

1) Import the CSV

Put the shape in Inputs!B1, for example round.

Put this in Inputs!B2:

="https://data.openfacet.net/list_" & LOWER(B1) & ".csv"

Then in Data!A1:

=IMPORTDATA(Inputs!B2)

This spills the full table automatically.

2) Inputs

Use these cells:

Inputs!B1 = shape
Inputs!B2 = CSV URL
Inputs!B3 = target carat, for example 1.23
Inputs!B4 = color, for example G
Inputs!B5 = clarity, for example VS2

For convenience, make Inputs!B1 a dropdown via Data → Data validation with these options:

round
cushion
radiant
emerald
oval
pear
marquise
heart

3) Price per carat

This version follows the OpenFacet logic using LN() and EXP() on the USD prices:

=LET(
  ct, Inputs!B3,
  col, Inputs!B4,
  clr, Inputs!B5,
  cars, FILTER(Data!A2:A, Data!B2:B=col, Data!C2:C=clr),
  prices, FILTER(Data!D2:D, Data!B2:B=col, Data!C2:C=clr),
  lo, MAX(FILTER(cars, cars<=ct)),
  hi, MIN(FILTER(cars, cars>=ct)),
  pa, XLOOKUP(lo, cars, prices),
  pb, XLOOKUP(hi, cars, prices),
  lam, IF(hi=lo, 0, (ct-lo)/(hi-lo)),
  EXP((1-lam)*LN(pa) + lam*LN(pb))
)

Because lam becomes 0 when hi=lo, this same formula also works for exact carat matches.

4) Total stone price

=ROUND(Inputs!B3 * Inputs!B6, 0)

Assuming Inputs!B6 contains the interpolated per-carat result.

Notes

IMPORTDATA auto-refreshes periodically and also on open/edit.
Import the CSV once into a helper sheet, then reference that range in formulas.
Avoid repeating IMPORTDATA() inside many cells.
If you prefer one tab per shape, the dashboard can route by shape to the correct tab before doing the same pa / pb interpolation.

Microsoft Excel

Option A — Power Query

This is the correct Excel workflow.

1) Load the CSV

Go to Data → Get Data → From Web
Paste the CSV URL, for example: https://data.openfacet.net/list_round.csv
Load the result into a table, for example named CSVData

2) Inputs

Use these cells:

B1 = shape
B2 = target carat
B3 = color
B4 = clarity

You can also make B1 a dropdown with Data Validation and the same shape list:

round
cushion
radiant
emerald
oval
pear
marquise
heart

If you want a helper URL formula:

="https://data.openfacet.net/list_" & LOWER(B1) & ".csv"

3) Price per carat

For arbitrary carat values, use the same log-linear interpolation approach:

=LET(
  ct, B2,
  col, B3,
  clr, B4,
  cars, FILTER(CSVData[carat], (CSVData[color]=col)*(CSVData[clarity]=clr)),
  prices, FILTER(CSVData[price], (CSVData[color]=col)*(CSVData[clarity]=clr)),
  lo, MAX(FILTER(cars, cars<=ct)),
  hi, MIN(FILTER(cars, cars>=ct)),
  pa, XLOOKUP(lo, cars, prices),
  pb, XLOOKUP(hi, cars, prices),
  lam, IF(hi=lo, 0, (ct-lo)/(hi-lo)),
  EXP((1-lam)*LN(pa) + lam*LN(pb))
)

Again, pa and pb avoid accidental clashes with spreadsheet-style cell references such as P1 and P2.

Because lam becomes 0 when hi=lo, the same formula also handles exact carat matches.

4) Total stone price

=ROUND(B2 * B6, 0)

Assuming B6 contains the interpolated per-carat result.

Refresh behavior

Manual: Data → Refresh All
Automatic: configure refresh in Query Properties
Power Query is preferred because it correctly handles CSV parsing and large files

Option B — Formula-only Excel import

Excel can sometimes fetch small CSV payloads with formulas, but this is not the robust approach.

=LET(
  raw, WEBSERVICE(B2),
  norm, SUBSTITUTE(raw, CHAR(13), ""),
  TEXTSPLIT(norm, ",", CHAR(10))
)

If you want a one-shape-per-tab workbook, place the shape-specific import in A1 of each shape tab. Example for a Round tab:

=LET(
  raw, WEBSERVICE("https://data.openfacet.net/list_round.csv"),
  norm, SUBSTITUTE(raw, CHAR(13), ""),
  TEXTSPLIT(norm, ",", CHAR(10))
)

Then in the dashboard, use the shape | CT | color | clarity | price/ct | total price layout and the LET + INDIRECT formulas shown earlier in Shape-routed dashboard rows. That keeps each row independent and allows mixed shapes across rows without helper columns.

Limitations:

not suitable for large files
can break on quoted CSV
not available in all Excel environments
weaker refresh behavior than Power Query

Use this only for small controlled cases.

Bottom line

Google Sheets: IMPORTDATA() is the fastest setup
Excel: Power Query is the reliable import path
Price column: already USD per carat
Interpolation: still use LN() and EXP() if you want spreadsheet results aligned with the OpenFacet model

For JSON endpoints and the broader API, see the API documentation.

Shape-routed dashboard rows

If you keep one shape per tab (Round, Cushion, Radiant, etc.), you can make the first sheet customer-friendly with only:

shape | CT | color | clarity | price/ct | total price

In this layout, each row is independent, so rows can use different shapes and carats.

`price/ct` formula (row 2 example)

Put this in E2 and fill down:

=IFERROR(
  LET(
    shape,$A2, ct,$B2, color,$C2, clarity,$D2,
    sheet,PROPER(shape),
    carats,INDIRECT("'"&sheet&"'!A2:A1200"),
    colors,INDIRECT("'"&sheet&"'!B2:B1200"),
    clarities,INDIRECT("'"&sheet&"'!C2:C1200"),
    prices,INDIRECT("'"&sheet&"'!D2:D1200"),

    IF(OR(shape="",ct="",color="",clarity=""),"",
      LET(
        lower,MAX(FILTER(carats*1,(colors=color)*(clarities=clarity)*(carats*1<=ct))),
        upper,MIN(FILTER(carats*1,(colors=color)*(clarities=clarity)*(carats*1>=ct))),

        pa,INDEX(prices,MATCH(1,(carats*1=lower)*(colors=color)*(clarities=clarity),0)),
        pb,INDEX(prices,MATCH(1,(carats*1=upper)*(colors=color)*(clarities=clarity),0)),

        lambda,IF(upper=lower,0,(ct-lower)/(upper-lower)),

        ROUND(EXP((1-lambda)*LN(pa)+lambda*LN(pb)),0)
      )
    )
  ),
"")

What this does:

sheet = PROPER(shape) maps input values like round to tab names like Round.
INDIRECT(...) selects the four columns (A:D) from the chosen shape tab only.
FILTER(...) narrows rows to matching color + clarity and finds lower/upper carat anchors.
INDEX/MATCH(...) gets pa and pb (USD/ct at those anchors).
EXP((1-lambda)*LN(pa)+lambda*LN(pb)) performs the required log-linear interpolation.

`total price` formula (row 2 example)

Put this in F2 and fill down:

=IFERROR(LET(ct,$B2,pricePerCt,$E2,IF(OR(ct="",pricePerCt=""),"",ROUND(ct*pricePerCt,0))),"")

Important naming rule

This formula assumes tab names are exactly:

Round, Cushion, Radiant, Emerald, Oval, Pear, Marquise, Heart

and that column order on each shape tab is:

A=carat, B=color, C=clarity, D=price/ct.

Disclaimer: The Diamond Composite Index (DCX) is a model-based benchmark constructed from public retail listings. It does not represent executable prices or financial advice. All data is filtered, interpolated, and smoothed for index use. Use at your own discretion. GIA is a registered trademark of the Gemological Institute of America. This site is not affiliated with, endorsed by, or associated with GIA.

CSV Data in Google Sheets & Excel

CSV URLs

What the CSV columns mean

Important note about interpolation

Google Sheets

1) Import the CSV

2) Inputs

3) Price per carat

4) Total stone price

Notes

Microsoft Excel

Option A — Power Query

1) Load the CSV

2) Inputs

3) Price per carat

4) Total stone price

Refresh behavior

Option B — Formula-only Excel import

Bottom line

Shape-routed dashboard rows

price/ct formula (row 2 example)

total price formula (row 2 example)

Important naming rule

`price/ct` formula (row 2 example)

`total price` formula (row 2 example)