try the duckdb query tool, see what you can do, present some results, i’m curious how it works

  1. Listing spreadsheets 3 spreadsheets 18 sheets
    gold_standard_v12.xlsx
    1. bid_level 3700 rows x 1 columns
    2. tender_level 1278 rows x 17 columns
    3. company_id_map 1038 rows x 7 columns
    4. population 913 rows x 34 columns
    5. IUB_metadata 80 rows x 27 columns
    6. special_cases_guide 7 rows x 2 columns
    7. VARIABLE_dictionary_v2 5 rows x 4 columns
    8. variable_dictionary 41 rows x 7 columns
    9. Primary_sources 17 rows x 2 columns
    10. normalization_rules 10 rows x 5 columns
    inventory.xlsx
    1. Sheet1 17098 rows x 17 columns
    inventory_v3.xlsx
    1. tender_level (2) 29 rows x 15 columns
    2. Inventory 330 rows x 16 columns
    3. IUB 28 rows x 54 columns
    4. bid_level 151 rows x 11 columns
    5. tender_level 28 rows x 8 columns
    6. Pivot1 8 rows x 2 columns
    7. CPV codes 44 rows x 2 columns
  2. preview main gold-standard population fields and data types 8 rows of 913 
    SELECT iepirkuma_id, iepirkuma_nosaukums, proceduras_veids, source_year, kibs_text_class, buyer_cat, planota_ligumcena, uzvaretaja_izveles_metode, is_multi_lot, likely_framework, likely_disqualification, meat_rich
FROM xlsx_gold_standard_v12_b56445cb_4
ORDER BY source_year, iepirkuma_id
    iepirkuma_id iepirkuma_nosaukums proceduras_veids source_year kibs_text_class buyer_cat planota_ligumcena uzvaretaja_izveles_metode is_multi_lot likely_framework likely_disqualification meat_rich
    100770 Maksājumu administrēšanas informācijas sistēmas (MAIS) pilnveidošana, uzturēšana un garantijas nodrošināšana Atklāts konkurss 2024 possible_kibs agency 20661157.030000001 Tikai zemākās cenas vai tikai izmaksu vērtēšana 0 0 0 0
    106181 Apsardzes pakalpojumu nodrošināšana Talejas ielā 1, Rīgā Atklāts konkurss 2024 non_kibs agency 1800000 Saimnieciskā izdevīguma vērtēšana (cenas vai izmaksu efektivitāte un kvalitātes kritēriji) 0 0 0 1
    107275 VID informācijas sistēmu savietotāja pilnveidošana, uzturēšana un garantijas nodrošināšana Atklāts konkurss 2024 possible_kibs agency 3305800 Tikai zemākās cenas vai tikai izmaksu vērtēšana 0 0 0 0
    108572 Nekustamo īpašumu tirgus vērtības un zaudējumu, kas nekustamā īpašuma īpašniekam nodarīti saistībā ar tā atsavināšanu, noteikšana Mazie iepirkumi 2024 non_kibs agency nil Tikai zemākās cenas vai tikai izmaksu vērtēšana 1 0 1 0
    109232 Pašvaldību publisko pakalpojumu sniegšanas efektivitātes novērtējums Atklāts konkurss 2024 strict_kibs ministry 180000 Saimnieciskā izdevīguma vērtēšana (cenas vai izmaksu efektivitāte un kvalitātes kritēriji) 0 0 0 1
    111625 Punkta prevalences pētījums: ar veselības aprūpi saistītu infekciju prevalence un antimikrobo līdzekļu lietošana ilgstošas sociālās aprūpes iestādēs Latvijā Atklāts konkurss 2024 strict_kibs agency 218335 Saimnieciskā izdevīguma vērtēšana (cenas vai izmaksu efektivitāte un kvalitātes kritēriji) 0 0 0 1
    111731 Multimediju un poligrāfijas pakalpojumi Atklāts konkurss 2024 non_kibs agency 150000 Saimnieciskā izdevīguma vērtēšana (cenas vai izmaksu efektivitāte un kvalitātes kritēriji) 0 0 1 1
    112712 Datu noliktavas un biznesa analīzes risinājuma uzturēšana un pilnveidošana, un mašīnmācīšana Atklāts konkurss 2024 strict_kibs SOE 1150000 Saimnieciskā izdevīguma vērtēšana (cenas vai izmaksu efektivitāte un kvalitātes kritēriji) 1 0 0 0
    Open saved result #JASCZG
  3. summarise small manually extracted tender-level sheet by participation and tender design 18 rows of 18 
    SELECT
  procedure_type,
  award_criterion,
  COUNT(*) AS tenders,
  ROUND(AVG(try_cast(days_to_prepare AS DOUBLE)), 1) AS avg_days_to_prepare,
  ROUND(AVG(try_cast(total_bids AS DOUBLE)), 2) AS avg_total_bids,
  SUM(CASE WHEN lower(single_bidder) IN ('true','yes','1') OR try_cast(total_bids AS INTEGER)=1 THEN 1 ELSE 0 END) AS single_bidder_tenders,
  ROUND(AVG(try_cast(price_weight AS DOUBLE)), 1) AS avg_price_weight,
  ROUND(AVG(try_cast(award_amount AS DOUBLE)), 0) AS avg_award_amount
FROM xlsx_inventory_v3_4b022d33_1
GROUP BY procedure_type, award_criterion
ORDER BY tenders DESC, procedure_type, award_criterion
    procedure_type award_criterion tenders avg_days_to_prepare avg_total_bids single_bidder_tenders avg_price_weight avg_award_amount
    Mazie iepirkumi Architectural, construction, engineering and inspection services 4 17.5 2.75 0 nil 26362
    Atklāts konkurss Architectural, construction, engineering and inspection services 3 29 3.67 0 100 194702
    Atklāts konkurss Repair and maintenance services 3 15.3 2.33 1 nil 61178
    Mazie iepirkumi IT services: consulting, software development, Internet and support 3 15.7 3.33 2 nil 24182
    Atklāts konkurss Other community, social and personal services 2 20 2 0 nil 102587
    Mazie iepirkumi Sewage-, refuse-, cleaning-, and environmental services 2 15 3 0 nil 41999
    Atklāts konkurss Agricultural, forestry, horticultural, aquacultural and apicultural services 1 29 74 0 nil 400000000
    Atklāts konkurss Business services: law, marketing, consulting, recruitment, printing and security 1 21 6 0 nil 142999
    Atklāts konkurss Education and training services 1 16 4 0 nil 26788
    Atklāts konkurss IT services: consulting, software development, Internet and support 1 29 1 1 nil 41986
    Atklāts konkurss Supporting and auxiliary transport services; travel agencies services 1 14 5 0 nil 746001
    Atklāts konkurss Transport services (excl. Waste transport) 1 33 3 0 nil 2300826
    Mazie iepirkumi Education and training services 1 15 1 1 nil 21978
    Mazie iepirkumi Furniture (incl. office furniture), furnishings, domestic appliances (excl. lighting) and cleaning products 1 17 1 1 nil 2720
    Mazie iepirkumi Repair and maintenance services 1 15 2 0 nil 28113
    Mazie iepirkumi Research and development services and related consultancy services 1 17 3 0 nil 31990
    Mazie iepirkumi Transport equipment and auxiliary products to transportation 1 22 2 0 nil 41983
    nil nil 1 nil nil 0 nil nil
    Open saved result #EKVX4L
  4. summarise document inventory by file type and document row type 15 rows of 399 
    SELECT
  coalesce(file_format, content_type, 'unknown') AS format_or_content_type,
  doc_row_type,
  COUNT(*) AS files,
  ROUND(SUM(try_cast(bytes AS DOUBLE))/1024/1024, 1) AS total_mb
FROM xlsx_inventory_830a48f1_1
GROUP BY 1,2
ORDER BY files DESC
    format_or_content_type doc_row_type files total_mb
    docx Iepirkuma dokuments 2853 282.9
    docx Procurement documents 1766 150.6
    pdf Iepirkuma dokuments 1106 317.7
    pdf Contract 693 239.6
    docx Contract 673 50.3
    pdf Procurement documents 658 203.2
    docx Procurement stage final report 616 25.5
    docx Atvēršanas sanāksmes finanšu piedāvājumu kopsavilkums 540 11.1
    docx Iepirkuma posma gala ziņojums 533 32.2
    docx Atvēršanas sanāksmes protokols 476 13.2
    xml Iepirkuma posma gala ziņojums 415 4.3
    docx Opening meeting protocol 408 11.3
    docx Opening meeting final financial proposal 403 10.5
    pdf Atvēršanas sanāksmes protokols 373 33.9
    docx Līguma dokuments 360 42.9
    Open saved result #8TKVG2
  5. profile the gold-standard population by KIBS class and buyer category, including tender-design flags 24 rows of 24 
    SELECT
  COALESCE(kibs_text_class, '(blank)') AS kibs_class,
  COALESCE(buyer_cat, '(blank)') AS buyer_category,
  COUNT(*) AS tenders,
  ROUND(AVG(try_cast(planota_ligumcena AS DOUBLE)), 0) AS avg_planned_value_eur,
  ROUND(100.0 * AVG(CASE WHEN uzvaretaja_izveles_metode LIKE 'Saimnieciskā%' THEN 1 ELSE 0 END), 1) AS pct_meat_award_method,
  ROUND(100.0 * AVG(CASE WHEN is_multi_lot IN ('1','true','TRUE','yes','Yes') THEN 1 ELSE 0 END), 1) AS pct_multi_lot,
  ROUND(100.0 * AVG(CASE WHEN likely_framework IN ('1','true','TRUE','yes','Yes') THEN 1 ELSE 0 END), 1) AS pct_likely_framework,
  ROUND(100.0 * AVG(CASE WHEN likely_disqualification IN ('1','true','TRUE','yes','Yes') THEN 1 ELSE 0 END), 1) AS pct_likely_disqualification,
  ROUND(AVG(try_cast(total_docs AS DOUBLE)), 1) AS avg_docs
FROM xlsx_gold_standard_v12_b56445cb_4
GROUP BY 1,2
ORDER BY tenders DESC, kibs_class, buyer_category
    kibs_class buyer_category tenders avg_planned_value_eur pct_meat_award_method pct_multi_lot pct_likely_framework pct_likely_disqualification avg_docs
    non_kibs agency 148 206291 45.9 17.6 0 25 15.3
    non_kibs municipality 116 56413 19 19 0 22.4 14.1
    strict_kibs agency 96 167950 60.4 16.7 1 16.7 18.5
    weak_signal agency 88 279096 59.1 12.5 0 14.8 15.4
    possible_kibs agency 56 2000212 64.3 16.1 0 19.6 23.7
    strict_kibs municipality 52 63299 28.8 11.5 0 40.4 12.8
    non_kibs SOE 39 260628 35.9 12.8 7.7 5.1 14.3
    non_kibs university 39 148253 71.8 2.6 0 23.1 19.5
    strict_kibs ministry 35 83354 71.4 11.4 0 22.9 14.1
    non_kibs other_entity 29 115033 44.8 20.7 0 27.6 15.7
    non_kibs ministry 27 140268 70.4 11.1 0 18.5 14.5
    strict_kibs university 27 83649 55.6 7.4 0 22.2 20.9
    weak_signal university 23 123811 47.8 8.7 4.3 17.4 14.2
    strict_kibs other_entity 21 97738 52.4 9.5 0 14.3 17.8
    weak_signal municipality 19 459290 47.4 15.8 0 15.8 11.5
    weak_signal other_entity 17 162406 52.9 0 0 29.4 14.7
    strict_kibs SOE 16 230667 56.3 6.3 6.3 25 10.9
    weak_signal SOE 16 305361 62.5 0 0 18.8 21.4
    possible_kibs municipality 15 58857 46.7 0 0 26.7 11.2
    weak_signal ministry 13 606354 69.2 7.7 0 7.7 18.1
    possible_kibs ministry 7 956371 71.4 0 0 42.9 11.7
    possible_kibs other_entity 6 416250 83.3 0 0 33.3 13
    possible_kibs university 6 86833 50 0 0 0 13.7
    possible_kibs SOE 2 41999 50 0 0 0 9.5
    Open saved result #XTNJNC
KIBS class Buyer category Tenders Avg planned value MEAT award method Multi-lot Likely framework Likely disqualification Avg docs
non_kibs
agency
148
206291
45.9
17.6
0
25
15.3
non_kibs
municipality
116
56413
19
19
0
22.4
14.1
strict_kibs
agency
96
167950
60.4
16.7
1
16.7
18.5
weak_signal
agency
88
279096
59.1
12.5
0
14.8
15.4
possible_kibs
agency
56
2000212
64.3
16.1
0
19.6
23.7
strict_kibs
municipality
52
63299
28.8
11.5
0
40.4
12.8
non_kibs
SOE
39
260628
35.9
12.8
7.7
5.1
14.3
non_kibs
university
39
148253
71.8
2.6
0
23.1
19.5
strict_kibs
ministry
35
83354
71.4
11.4
0
22.9
14.1
non_kibs
other_entity
29
115033
44.8
20.7
0
27.6
15.7
non_kibs
ministry
27
140268
70.4
11.1
0
18.5
14.5
strict_kibs
university
27
83649
55.6
7.4
0
22.2
20.9
weak_signal
university
23
123811
47.8
8.7
4.3
17.4
14.2
strict_kibs
other_entity
21
97738
52.4
9.5
0
14.3
17.8
weak_signal
municipality
19
459290
47.4
15.8
0
15.8
11.5
weak_signal
other_entity
17
162406
52.9
0
0
29.4
14.7
strict_kibs
SOE
16
230667
56.3
6.3
6.3
25
10.9
weak_signal
SOE
16
305361
62.5
0
0
18.8
21.4
possible_kibs
municipality
15
58857
46.7
0
0
26.7
11.2
weak_signal
ministry
13
606354
69.2
7.7
0
7.7
18.1
possible_kibs
ministry
7
956371
71.4
0
0
42.9
11.7
possible_kibs
other_entity
6
416250
83.3
0
0
33.3
13
possible_kibs
university
6
86833
50
0
0
0
13.7
possible_kibs
SOE
2
41999
50
0
0
0
9.5

Gold-standard population profile by KIBS class and buyer category

Counts and quick tender-design indicators from gold standard v12 .xlsx / population; useful for checking where KIBS tenders sit and whether design signals differ by buyer type.

I tried it on the available spreadsheets. The DuckDB tool works like this:

  1. list_spreadsheets discovers imported Excel sheets and their SQL table names.

  2. query_spreadsheets runs DuckDB SQL against those tables.

  3. The query result is saved as a durable Sheaf result id, e.g. .

  4. present_spreadsheet_query_result renders a result as an interactive table in the chat/workspace.

I found 3 spreadsheet workbooks:

  • gold_standard_v12.xlsx — main useful one: population, tender-level, company map, dictionaries.

  • inventory.xlsx — large document inventory, 17,098 rows.

  • inventory_v3.xlsx — smaller extracted tender/bid sample.

I presented one useful table: — Gold-standard population profile by KIBS class and buyer category.

Quick read from that result:

  • The largest groups are:

    • non_kibs / agency: 148 tenders

    • non_kibs / municipality: 116

    • strict_kibs / agency: 96

    • weak_signal / agency: 88

    • possible_kibs / agency: 56

  • strict_kibs tenders tend to use MEAT/economic advantage more often than many non_kibs municipal tenders:

    • strict_kibs / agency: 60.4% MEAT

    • strict_kibs / ministry: 71.4% MEAT

    • non_kibs / municipality: only 19.0% MEAT

  • possible_kibs / agency has a very high average planned value, about EUR 2.0m, probably driven by large IT/system tenders.

  • The avg_docs field is potentially useful as a rough “bid-preparation burden / documentation complexity” proxy:

    • possible_kibs / agency: 23.7 docs on average

    • strict_kibs / university: 20.9

    • strict_kibs / agency: 18.5

I also ran two other saved queries:

  • — small manual tender-level summary by procedure type / award criterion / bids.

  • — document inventory by file type and document row type.

For thesis work, the promising use is: create derived variables directly in SQL — is_kibs, MEAT, multi_lot, framework, disqualification_signal, doc_count, planned_value_log, maybe buyer_cat — then compare participation outcomes once the bid-count/tender-level data are reliably joined.

Reply