Oil refinery production optimization (+PowerBI)

oil_refining_powerbi.ipynb Open In Colab Hits

Description: In this document, we present an enhanced approach to oil refining optimization by integrating Power BI for improved decision-making and data visualization. For a full description of the model, you can read more about it here.

This notebook showcases how Power BI, when combined with mathematical optimization, delivers powerful, actionable insights. By leveraging the dynamic reporting features of Power BI and the optimization capabilities of AMPL, users can interact with data in real-time and make well-informed decisions more effectively.

Tags: Oil production, Production optimization, Profitability, Refinery, mip, highs, powerbi, industry, scheduling, data-science, data-analysis, decision-making

Notebook author: Mikhail Riabtsev <mail@solverytic.com>

1. Introduction

In this document, we present an enhanced approach to oil refining optimization by integrating Power BI for improved decision-making and data visualization. For a full description of the model, you can read more about it here.

This notebook showcases how Power BI, when combined with mathematical optimization, delivers powerful, actionable insights. By leveraging the dynamic reporting features of Power BI and the optimization capabilities of AMPL, users can interact with data in real-time and make well-informed decisions more effectively. This innovative method offers several key benefits:

  • Interactive Dashboards: Power BI’s user-friendly interface allows easy exploration of scenarios and results, without requiring advanced technical knowledge.

  • Real-time Data Integration: Dynamic data inputs ensure optimization is always based on the most current information, keeping decisions timely and relevant.

  • Scalability: Power BI’s ability to handle large datasets makes it ideal for implementing optimization models on a larger scale.

  • User-Friendly Reporting: Clear and intuitive visual representations of optimization results enhance understanding, facilitating smoother communication across teams. Together, AMPL and Power BI create a streamlined process, bridging the gap between model development and real-world decision-making.

Partner with the AMPL team to transform complex problems into optimized solutions. AMPL consulting services combine deep technical knowledge with industry-leading insights, helping you unlock the full potential of optimization within your organization.


1. Download Necessary Extensions and Libraries

Ensure you have a registered Power BI Service account and uploaded Oil_refining.pbix file to your Power BI Service.

# Install dependencies
%pip install -q amplpy powerbiclient
from powerbiclient import Report, models
from io import StringIO
from ipywidgets import interact
import requests
import pandas as pd
import numpy as np
# Google Colab & Kaggle integration
from amplpy import AMPL, ampl_notebook

ampl = ampl_notebook(
    modules=["highs"],  # modules to install
    license_uuid="default",  # license to use
)  # instantiate AMPL object and register magics

2. Authorize in Power BI Service

Complete the necessary authorization steps.

# Import the Device Code Login Authentication class to authenticate against Power BI and initiate the Microsoft device authentication
from powerbiclient.authentication import DeviceCodeLoginAuthentication  # Import the class for interactive authentication from the Power BI client
device_auth = DeviceCodeLoginAuthentication()                           # Create an instance of InteractiveLoginAuthentication which triggers the login process

### TO LOG IN TO THE MS Power BI Service, USE NEXT CREDENTIALS:
    # Authentication Code:  see below in the python service message
    # MS Power BI account:  [email protected]
    # Password:             C)715409648097ar
# OR upload Oil_refining.pbix (link above) on you own Power BI account to work with model.
Performing device flow authentication. Please follow the instructions below.
To sign in, use a web browser to open the page https://microsoft.com/devicelogin and enter the code C9D4A8URG to authenticate.

Device flow authentication successfully completed.
You are now logged in .

The result should be passed only to trusted code in your notebook.

3. Embed the Power BI Report with Data

Integrate your Power BI report to Jupyter Notebook.

# Get the group and report ID from PowerBI service
group_id="c498f9cc-fb27-4ba4-96b6-d4c1813624c2"     # YOU HAVE TO PUT HERE YOUR POWER BI GROUP ID OR WORKSPACE ID
report_id="7500c9bc-ac5e-4e91-a8ff-bba318f3d6a9"    # YOU HAVE TO PUT HERE YOUR POWER BI REPORT ID

# Creating an instance of the Report class by passing group_id, report_id, and interactive_auth
report = Report(group_id=group_id, report_id=report_id, auth = device_auth)
#report.set_size(900, 1240)                         # This line is commented out but would set the size of the report's output.

report                                              # Show the power BI report with data.

4. AMPL Model Formulation

Define and set up the optimization model.

%%ampl_eval
reset;
### SETS
 set CRUD ;       # Types of crude oil (Crude1, Crude2)
 set DIST ;       # Distillation products
 set REF ;        # Reforming products
 set CRACK ;      # Cracking products
 set LUBR ;       # Lubricating products
 set PROD ;       # Final products
 set STAT ;       # Delivery stations
 set D_MODE ;     # Operating modes of the equipment of the Distillation installation
 set R_MODE ;     # Operating modes of the equipment of the Reforming installation
 set CR_MODE ;    # Operating modes of the equipment of the Cracking installation
 set L_MODE ;     # Operating modes of the equipment of the Lubrication process
 set POLLUT ;     # Types of pollutants

 set DISTILLATION within {D_MODE, CRUD, DIST} ;   # Pairs of the Distillation process
 set REFORMING within {R_MODE, DIST, REF} ;       # Pairs of the Reforming process
 set DIST_R := setof{(m,d,r) in REFORMING}(d) ;   # List of included components
 set CRACKING within {CR_MODE, DIST, CRACK} ;     # Pairs of the Cracking process
 set DIST_CR := setof{(m,d,cr) in CRACKING}(d) ;  # List of included components
 set LUBRICATING within {L_MODE, DIST, LUBR} ;    # Pairs of the Lubricating process
 set DIST_L := setof{(m,d,l) in LUBRICATING}(d) ; # List of included components
 set BLENDING within                              # Pairs of Intermediate and final products involved in blending
  {DIST union REF union CRACK union LUBR, PROD} ;
 set INTERMED:= setof{(i,j) in BLENDING}i ;       # Set of Intermediate products before blending

### PARAMETERS
  param nPeriod >= 0 ;                            # Number of weeks in the planning period
  param nPeriodByYear >= 0 ;                      # Number of nPeriods in the Year

 ## Crude oil
  param crude_Min{CRUD} = 0 ;                    # Minimum supply limits for each crude oil type
  param crude_Max_capacity{c in CRUD} >= crude_Min[c] ; # Maximum supply limits for each crude oil type
  param crude_Cost{CRUD, 1..nPeriod} >= 0 ;       # Cost of crude oil per week

 ## Distillation
  param distill_Yield{DISTILLATION} >= 0 ;        # Yield of products
  param distill_Pollute{D_MODE, CRUD, POLLUT} ;   # Pollutant emissions
  param distill_Cost{D_MODE, CRUD} >= 0 ;         # Cost of process
  param distill_Waste_Cost{D_MODE, CRUD} >= 0 ;   # Residue disposal cost
  param distill_Equipment_Setup_Period{D_MODE} >= 0 ;   # Equipment setup period
  param distill_Equipment_Setup_Cost{D_MODE} >= 0 ;     # Equipment setup cost
  param distill_Max_capacity{D_MODE} >= 0 ;             # Maximum capacity

 ## Reforming
  param reform_Yield{REFORMING} >= 0 ;            # Yield of products
  param reform_Pollute{R_MODE, DIST_R, POLLUT} ;  # Pollutant emissions
  param reform_Cost{R_MODE, DIST_R} >= 0 ;        # Cost of process
  param reform_Waste_Cost{R_MODE, DIST_R} >= 0 ;  # Residue disposal cost
  param reform_Max_capacity{R_MODE} >= 0 ;        # Maximum capacity
  param reform_Equipment_Setup_Period{R_MODE} >= 0 ;    # Equipment setup period
  param reform_Equipment_Setup_Cost{R_MODE} >= 0 ;# Equipment setup cost

 ## Cracking
  param crack_Yield{CRACKING} >= 0 ;              # Yield of products
  param crack_Pollute{CR_MODE, DIST_CR, POLLUT} ; # Pollutant emissions
  param crack_Cost{CR_MODE, DIST_CR} >= 0 ;       # Cost of process
  param crack_Waste_Cost{CR_MODE, DIST_CR} >= 0 ; # Residue disposal cost
  param crack_Max_capacity{CR_MODE} >= 0 ;        # Maximum capacity
  param crack_Equipment_Setup_Period{CR_MODE} >= 0 ;    # Equipment setup period
  param crack_Equipment_Setup_Cost{CR_MODE} >=0 ; # Equipment setup cost

 ## Lubrication
  param lube_Yield{LUBRICATING} >= 0 ;            # Yield of products
  param lube_Pollute{L_MODE, DIST_L, POLLUT} ;    # Pollutant emissions
  param lube_Waste_Cost{L_MODE, DIST_L} >= 0 ;    # Residue disposal cost
  param lube_Cost{L_MODE, DIST_L} >= 0 ;          # Cost of process
  param lube_Max_capacity{L_MODE} >= 0 ;          # Maximum production of lube oil
  param lube_Equipment_Setup_Period{L_MODE} >= 0 ;# Equipment setup period
  param lube_Equipment_Setup_Cost{L_MODE} >= 0 ;  # Equipment setup cost
  param lube_limit_Min = 0 ;                      # Minimum production of lube oil
  param lube_limit_Max >= lube_limit_Min ;        # Maximum production of lube oil

 ## Intermediate components
  param Intermed_Octane{INTERMED} >= 0 ;          # Octane number
  param Intermed_VaporPressure{INTERMED} >= 0 ;   # Vapor pressure

 ## Blending
  param blending_Cost{PROD} >= 0 ;                # Cost of blending

 ## Products
  param prod_Octane_Min{PROD} >= 0 ;              # Minimum octane number for final products
  param prod_VaporPressure_Max{PROD} >= 0 ;       # Maximum vapor pressure for final products
  param prod_Premium_Regular_Gas_Min >= 0 ;       # Minimum production of premium gas
  param prod_FuelOil_Ratio{INTERMED} >= 0 ;       # Ratios for fuel oil production components

 ## Storage
  param storage_Capacity{PROD} >=0 ;              # Storage capacity for each product
  param storage_Cost{PROD} >= 0 ;                 # Storage cost per product
  param storage_Waste{PROD} >= 0 ;                # Waste during storage

 ## Product delivery
  param delivery_Cost{PROD, STAT} >= 0 ;          # Delivery cost per product to each station

 ## Plant
  param plant_Shutdown_Period >= 0 ;              # Equipment setup period
  param plant_Shutdown_Cost >= 0 ;                # Equipment setup cost
  param plant_Const_Cost >= 0 ;                   # Plant fixed costs

 ## Market
  param seasonal_Base_Demand{PROD, 1..nPeriod} >= 0 ; # Base demand for products per week.
  param seasonal_Base_Price {PROD, 1..nPeriod} >= 0 ; # Base price for products per week.
  # Price elasticity
  param nStep integer > 0 ;                       # Number of steps for price elasticity
  param price_nStep_Value{1..nStep+1} ;           # Step values for price elasticity
  param demand_nStep_Value{1..nStep+1} ;          # Step values for price elasticity

 ## Finance
  param discount_Rate >= 0 ;                      # Discount rate for future cash flows
  param initial_Cash  >= 0 ;                      # Initial cash available

 ## Loans
  set LOANS;                                      # Set of loan periods
  set LOAN_param;                                 # Parameters of loans (term, interest, Max_Money)
  param loan{LOANS, LOAN_param} >= 0 ;            # Conditions for obtaining credit

### VARIABLES
 ## Plant working
  var Plant_Working{t in 1..nPeriod} binary;                  # 1 if the plant is running. 0 if the plant is shutdown

 ## Distillation
  var Crude_Supply{D_MODE, CRUD, 1..nPeriod} >= 0 ;           # Amount of crude supplied
  var Distill_X{D_MODE, 1..nPeriod} binary ;                  # Additional binary variable for selecting the operating mode of the equipment

 ## Reforming
  var Distill_to_Reforming{R_MODE, DIST_R, 1..nPeriod} >= 0 ; # Quantity of distillation products used for Reforming
  var Reform_X{R_MODE, 1..nPeriod} binary ;                   # Additional binary variable for selecting the operating mode of the equipment

 ## Cracking
  var Distill_to_Cracking{CR_MODE, DIST_CR, 1..nPeriod} >= 0 ;# Quantity of distillation products used for Cracking
  var Cracking_X{CR_MODE, 1..nPeriod} binary ;                # Additional binary variable for selecting the operating mode of the equipment


 ## Lubricating
  var Distill_to_Lubricating{L_MODE, DIST_L, 1..nPeriod} >= 0 ;# Quantity of distillation products used for Lubricating
  var Lubricating_X{L_MODE, 1..nPeriod} binary ;              # Additional binary variable for selecting the operating mode of the equipment

 ## Blending:
  var Blending{BLENDING, 1..nPeriod} >= 0 ;                   # Amount of ingredients mixed to obtain final products

 ## Demand
  var Demand{PROD, STAT, 1..nPeriod, 1..nStep} >= 0 ;         # Demand for each pr oduct at each station over time
  var X{PROD, STAT, 1..nPeriod, 1..nStep} binary ;            # Additional binary variable for demand steps (1 if for product p in period t the price is selected at step nStep, or 0 otherwise)

 ## Storage
  var Storage_Fraction{p in PROD, t in 1..nPeriod} =          # Amount of each product in Storage each period
    sum{tt in 1..t} (sum{(i,p) in BLENDING} Blending[i,p,tt]
    - sum{s in STAT, n in 1..nStep} Demand[p,s,tt,n]) * (1-storage_Waste[p]/100) ;

 ## Loan
 # Amount of loan taken every period
  var Loan_In{l in LOANS, 1..nPeriod-1} >= 0 , <= loan[l,'Max_Money'] ;

 # Amount of loan taken every period
  var Loan_Out{l in LOANS, t in 2..nPeriod} = Loan_In[l,t-1] * (1+(loan[l, 'interest']/nPeriodByYear)/100);

 ## Pollutant emissions
 var Waste_Pollutant{p in POLLUT, t in 1..nPeriod} =
  # Pollution from waste disposal from the Distillation process
  sum{m in D_MODE, c in CRUD} Crude_Supply[m,c,t] * (1 - sum{d in DIST}distill_Yield[m,c,d]) * distill_Pollute[m,c,p]
  # Pollution from waste disposal from the Reforming process
  + sum{m in R_MODE, c in DIST_R} Distill_to_Reforming[m,c,t] * (1 - sum{d in REF} reform_Yield[m,c,d]) * reform_Pollute[m,c,p]
  # Pollution from waste disposal from the Cracking process
  + sum{m in CR_MODE, c in DIST_CR} Distill_to_Cracking[m,c,t] * (1 - sum{d in CRACK} crack_Yield[m,c,d]) * crack_Pollute[m,c,p]
  # Pollution from waste disposal from the Lubricating process
  + sum{m in L_MODE, c in DIST_L} Distill_to_Lubricating[m,c,t] * (1 - sum{d in LUBR} lube_Yield[m,c,d]) * lube_Pollute[m,c,p];

 ## Cash flow with incomes and costs
  var CashFlow{t in 1..nPeriod} =
   # sales income
    sum{p in PROD, s in STAT, n in 1..nStep} Demand[p,s,t,n] * (seasonal_Base_Price[p,t] * (1 + price_nStep_Value[n]/100) - delivery_Cost[p,s])
   # minus the cost of purchasing crude oil	+ the costs of the Distillation process	+ costs for disposal of waste from the Distillation process
    - sum{m in D_MODE, c in CRUD} Crude_Supply[m,c,t] * (crude_Cost[c,t] + distill_Cost[m,c] + (1 - sum{d in DIST}distill_Yield[m,c,d]) * distill_Waste_Cost[m,c])
   # minus the costs of the Reforming process	+ costs for disposal of waste from the Reforming process
    - sum{m in R_MODE, c in DIST_R} Distill_to_Reforming[m,c,t] * (reform_Cost[m,c] + (1 - sum{d in REF} reform_Yield[m,c,d]) * reform_Waste_Cost[m,c])
   # minus the costs of the Cracking process	+ costs for disposal of waste from the Cracking process
    - sum{m in CR_MODE, c in DIST_CR} Distill_to_Cracking[m,c,t] * (crack_Cost[m,c] + (1 - sum{d in CRACK} crack_Yield[m,c,d]) * crack_Waste_Cost[m,c])
   # minus the costs of the Lubricating process	+ costs for disposal of waste from the Lubricating process
    - sum{m in L_MODE, c in DIST_L} Distill_to_Lubricating[m,c,t] * (lube_Cost[m,c] + (1 - sum{d in LUBR} lube_Yield[m,c,d]) * lube_Waste_Cost[m,c])
   # minus the costs of reconfiguring equipment (if available)
    - (if t > 1 then          /*Nonliner piece*/
      # Distillation equipment
        sum{m in D_MODE} (if Distill_X[m,t] - Distill_X[m,t-1] > 0 then distill_Equipment_Setup_Cost[m] else 0)
      # Reforming equipment
        + sum{m in R_MODE} (if Reform_X[m,t] - Reform_X[m,t-1] > 0 then reform_Equipment_Setup_Cost[m] else 0)
      # Cracking equipment
        + sum{m in CR_MODE} (if Cracking_X[m,t] - Cracking_X[m,t-1] > 0 then crack_Equipment_Setup_Cost[m] else 0)
      # Lubricating equipment
        + sum{m in L_MODE}(if Lubricating_X[m,t] - Lubricating_X[m,t-1] > 0 then lube_Equipment_Setup_Cost[m] else 0)
        else 0)
   # minus the cost of the Blending
    - sum{(i,p) in BLENDING} Blending[i,p,t] * blending_Cost[p]
    # minus cost of shutdown
    - (1 - Plant_Working[t]) * plant_Shutdown_Cost
   # minus the storage cost
    - sum{p in PROD} Storage_Fraction[p,t] * storage_Cost[p]
   # minus fixed plant costs
    - plant_Const_Cost
   # plus the amount of the loan received
    + (if t = nPeriod then 0 else sum{l in LOANS} Loan_In[l,t])
   # minus the amount of repaid loans with accrued interest
    - (if t = 1 then 0  else sum{l in LOANS} Loan_Out[l,t]);

### OBJECTIVE FUNCTION
 # Maximize the total profit considering all incomes and costs, discounted by the discount rate
  maximize Total_Profit:
    sum{t in 1..nPeriod} CashFlow[t] / (1 + (discount_Rate/nPeriodByYear)/100)^t ;

### CONSTRAINTS
 subject to
 ## Distillation
  # Ensure that total supply of crude oil does not exceed the maximum capacity.
  Crude_Supply_Min_Max{c in CRUD, t in 1..nPeriod}:crude_Min[c] <= sum{m in D_MODE} Crude_Supply[m,c,t] <= crude_Max_capacity[c];

  # Ensure distillation capacity does not exceed the maximum. Сapacity is reduced during downtime caused by equipment reconfiguring.
  DistillCapacity_Max {m in D_MODE, t in 1..nPeriod}: sum{c in CRUD} Crude_Supply[m,c,t] <= distill_Max_capacity [m]
    /* Nonliner piece*/ * (if t > 1 then (if Distill_X[m,t] > Distill_X[m,t-1] then 1 - distill_Equipment_Setup_Period[m] else 1) else 1) ;

  # Ensure only one mode per period
  ForEachPeriodOnlyOne_Distill_X { t in 1..nPeriod}: sum{m in D_MODE} Distill_X[m,t] <= 1 ;

  # Ensure that Crude_Supply > 0 only when Distill_X > 0
  Distill_{m in D_MODE, t in 1..nPeriod}: sum{c in CRUD} Crude_Supply[m,c,t] <= Distill_X[m,t] * 10e5;

 ## Reforming
  # Ensure reforming capacity does not exceed the maximum. Сapacity is reduced during downtime caused by equipment reconfiguring.
  Reforming_Capacity_Max {m in R_MODE, t in 1..nPeriod}: sum{c in DIST_R} Distill_to_Reforming[m,c,t] <= reform_Max_capacity[m]
    /* Nonliner piece*/ * (if t > 1 then (if Reform_X[m,t] > Reform_X[m,t-1] then 1 - reform_Equipment_Setup_Period[m] else 1) else 1);

  # Ensure only one mode per period
  ForEachPeriodOnlyOne_Reform_X { t in 1..nPeriod}:  sum{m in R_MODE} Reform_X[m,t] <= 1 ;

  # Ensure that Distill_to_Reforming > 0 only when Reform_X > 0
  Reform_{m in R_MODE, t in 1..nPeriod}:sum{c in DIST_R}Distill_to_Reforming[m,c,t] <= Reform_X[m,t] * 10e5;

 ## Cracking
  # Ensure cracking capacity does not exceed the maximum. Сapacity is reduced during downtime caused by equipment reconfiguring.
  CrackingCapacity_Max {m in CR_MODE, t in 1..nPeriod}: sum{c in DIST_CR} Distill_to_Cracking[m,c,t] <= crack_Max_capacity[m]
    /* Nonliner piece*/ * (if t > 1 then (if Cracking_X[m,t] > Cracking_X[m,t-1] then 1 - crack_Equipment_Setup_Period[m] else 1) else 1);

  # Ensure only one mode per period
  ForEachPeriodOnlyOne_Cracking_X { t in 1..nPeriod}: sum{m in CR_MODE} Cracking_X[m,t] <= 1 ;

  # Ensure that Distill_to_Cracking > 0 only when Cracking_X > 0
  Cracking_{m in CR_MODE, t in 1..nPeriod}: sum{c in DIST_CR} Distill_to_Cracking[m,c,t] <= Cracking_X[m,t] * 10e5;

 ## Lubricating
  # Ensure cracking does not exceed the minimum & maximum volume
  Lube_Oil_Min_Max {t in 1..nPeriod}:
  lube_limit_Min <= sum{(m,d,l)in LUBRICATING} Distill_to_Lubricating[m,d,t] * lube_Yield[m,d,l] <= lube_limit_Max  ;

  # Сapacity is reduced during downtime caused by equipment reconfiguring.
  Lube_Oil_Capacity_Max {m in L_MODE, t in 1..nPeriod}: sum{c in DIST_L} Distill_to_Lubricating[m,c,t] <= lube_Max_capacity[m]
    /* Nonliner piece*/ * (if t > 1 then (if Lubricating_X[m,t] > Lubricating_X[m,t-1] then 1 - lube_Equipment_Setup_Period[m] else 1) else 1); # Nonliner piece

  # Ensure only one mode per period
  ForEachPeriodOnlyOne_Lubricating_X { t in 1..nPeriod}: sum{m in L_MODE} Lubricating_X[m,t] <= 1 ;

  # Ensure that Distill_to_Lubricating> 0 only when Lubricating_X > 0
  Lubricating_{m in L_MODE, t in 1..nPeriod}: sum{c in DIST_L}Distill_to_Lubricating[m,c,t] <= Lubricating_X[m,t] * 10e5;

 ## Blending
  PremiumRegularGasRatio {t in 1..nPeriod}:                   # Premium gasoline production: at least 40% of regular gasoline production
    sum{(i,p) in BLENDING: p='Premium Gasoline'} Blending[i,p,t] >=
    sum{(i,p) in BLENDING: p='Regular Gasoline'} Blending[i,p,t] * prod_Premium_Regular_Gas_Min/100 ;

  OctaneNumberMin {p in PROD, t in 1..nPeriod: p = 'Premium Gasoline'}:# Ensure octane number requirements for final products
    sum{(i,p) in BLENDING} Blending[i,p,t] * Intermed_Octane[i] >=
    sum{(ii,p) in BLENDING} Blending[ii,p,t] * prod_Octane_Min[p] ;

  OctaneNumberMin_2 {p in PROD, t in 1..nPeriod: p = 'Regular Gasoline'}:# Ensure octane number requirements for final products
    sum{(i,p) in BLENDING} Blending[i,p,t] * Intermed_Octane[i] >=
    sum{(ii,p) in BLENDING} Blending[ii,p,t] * prod_Octane_Min[p] ;

  VaporPressure_Max {p in PROD, t in 1..nPeriod: p='Jet Fuel'}:# Ensure vapor pressure limits for products
    sum{(i,p) in BLENDING} Blending[i,p,t] * Intermed_VaporPressure[i] <=
    sum{(i,p) in BLENDING} Blending[i,p,t] * prod_VaporPressure_Max[p] ;

  FuelOilRatio {(i,p) in BLENDING, t in 1..nPeriod: p = 'Fuel Oil'}:   # Maintain the correct ratio for fuel oil production
    Blending['Residuum',p, t] = Blending[i,p,t] / prod_FuelOil_Ratio[i] ;

 ## Storage
 # Ensure storage fractions are non-negative and within capacity
  Storage_non_negative {p in PROD, t in 1..nPeriod}:
    0 <= Storage_Fraction[p,t] <= storage_Capacity[p];

 ## Financial calculations
  CashFlow_Balance{t in 1..nPeriod}:                  # Cash flow balance
    initial_Cash
    + sum{tt in 1..t}CashFlow[tt] >= 0;

 ## Ensure sufficient distillation output
  Distillation_Out {d in DIST, t in 1..nPeriod}:
    sum{m in D_MODE, c in CRUD} Crude_Supply[m,c,t] * distill_Yield[m,c,d]
    >=
    sum{m in R_MODE, dd in DIST_R: dd=d} Distill_to_Reforming[m,dd,t]
    + sum{m in CR_MODE, dd in DIST_CR: dd=d} Distill_to_Cracking[m,dd,t]
    + sum{m in L_MODE, dd in DIST_L: dd=d} Distill_to_Lubricating[m,dd,t] ;

 ## Balance INTERMED products before blending
  INTERMED_Balance{i in INTERMED, t in 1..nPeriod}:
    sum{(i,p) in BLENDING} Blending[i,p,t] <=
    sum{(m,c,d) in DISTILLATION:d=i} Crude_Supply[m,c,t] * distill_Yield[m,c,d]

    - sum{m in R_MODE, d in DIST_R: d=i} Distill_to_Reforming[m,d,t]
    - sum{m in CR_MODE, d in DIST_CR: d=i} Distill_to_Cracking[m,d,t]
    - sum{m in L_MODE, d in DIST_L: d=i} Distill_to_Lubricating[m,d,t]

    + sum{(m,d,r) in REFORMING: r=i} Distill_to_Reforming[m,d,t] * reform_Yield[m,d,r]
    + sum{(m,d,cr) in CRACKING: cr=i} Distill_to_Cracking[m,d,t] * crack_Yield[m,d,cr]
    + sum{(m,d,l) in LUBRICATING: l=i} Distill_to_Lubricating[m,d,t] * lube_Yield[m,d,l] ;

 ## Prices elastisity
  ForEachPeriodOnlyOne_X {p in PROD, s in STAT, t in 1..nPeriod}:
    sum{i in 1..nStep} X [p,s,t,i] = 1 ;            # Ensure only one price per product per period

  # Double restriction. Ensure demand is within the range dictated by price steps.
  # The constraint of type min must be set after solving a model without this constraint. #Use Min constraint after ensuring that it does not conflict with other model constraints.
  Demand_Min {p in PROD, s in STAT, t in 1..nPeriod, n in 1..nStep: p!='Lube Oil'}:
    Demand[p,s,t,n] >= X[p,s,t,n] * seasonal_Base_Demand[p,t] * (1+demand_nStep_Value[n]/100) ;
  Demand_Max {p in PROD, s in STAT, t in 1..nPeriod, n in 1..nStep}:
    Demand[p,s,t,n] <= X[p,s,t,n] * seasonal_Base_Demand[p,t] * (1+demand_nStep_Value[n+1]/100) ;

 ## Shutdown contstraints
  # The number of working periods is equal to the total number of periods under consideration minus the duration of the planned suspension
  Plant_Working_nPeriods: sum{t in 1..nPeriod - plant_Shutdown_Period + 1} Plant_Working[t] = nPeriod - plant_Shutdown_Period;

  # Additionally, there is a restriction in case of a longer (more than 1 period) plant shutdown.
  Shutdown_Distill{t in 1..nPeriod - plant_Shutdown_Period + 1}:
    sum{m in D_MODE, c in CRUD, tt in 0..plant_Shutdown_Period-1} Crude_Supply[m,c,t+tt] <= Plant_Working[t] * 10e5;

5. Data change

You can change model parameters directly in a Power BI report. alt text

5. Download Data from Power BI Report

Retrieve the required data for analysis.

pages = report.get_pages()                      # Retrieve the list of pages from the report object, storing them in the 'pages' variable.

# The following lines are commented out but would iterate through the list of pages if uncommented.
#for page in range(len(pages)):                 # Loop over each page index from 0 to the total number of pages.
    #print("displayName:", pages[page]['displayName']," name:", pages[page]['name']) # For each page, print its 'displayName' (the human-readable name) and 'name' (the internal identifier).

range_of_visual = {                             # List visuals for import data to AMPL parameters
    'a6ee369779ca1dbbe625': ['CRUD','crude_Max_capacity', 'crude_Cost'],
    'ReportSectione9e707e99075fef5f637': ['DIST', 'POLLUT', 'D_MODE', 'distill_Waste_Cost', 'distill_Max_capacity','distill_Equipment_Setup_Period','distill_Pollute','distill_Cost', 'distill_Yield', 'distill_Equipment_Setup_Cost'],
    '1f30731c20476b22e3b8': ['REF', 'R_MODE', 'reform_Max_capacity', 'reform_Equipment_Setup_Period', 'reform_Equipment_Setup_Cost', 'reform_Yield', 'reform_Cost', 'reform_Pollute', 'reform_Waste_Cost'],
    'baaf6d948d8009021852': ['CRACK', 'CR_MODE', 'crack_Yield', 'crack_Pollute', 'crack_Cost', 'crack_Waste_Cost', 'crack_Max_capacity', 'crack_Equipment_Setup_Period', 'crack_Equipment_Setup_Cost'],
    'df4105df5589821b0042': ['LUBR', 'L_MODE', 'lube_Max_capacity', 'lube_Equipment_Setup_Period', 'lube_Equipment_Setup_Cost', 'lube_limit_Max', 'lube_Yield', 'lube_Pollute', 'lube_Cost', 'lube_Waste_Cost'],
    '5f6474c4e0a5741fa523': ['PROD', 'prod_FuelOil_Ratio', 'prod_VaporPressure_Max', 'prod_Premium_Regular_Gas_Min', 'blending_recipe', 'prod_Octane_Min', 'Intermed_VaporPressure', 'Intermed_Octane', 'blending_Cost'],
    '824aaabf4036f0ccfbb2': ['STAT', 'storage_Capacity', 'storage_Cost', 'storage_Waste', 'delivery_Cost'],
    'a0c8f0fc396f1f12c937': ['plant_Const_Cost', 'plant_Shutdown_Period', 'plant_Shutdown_Cost', 'initial_Cash', 'discount_Rate'],
    '015cea0c3cc0be908cc3': ['LOAN_param','LOANS','loan'],
    'eac0f7120065aad83ca0': ['demand_nStep_Value', 'seasonal_Base_Price', 'seasonal_Base_Demand', 'price_nStep_Value'],
    }

ampl_ = dict()                             # Initialize an empty dictionary to store data for each visual param element

for key_s in range_of_visual:             # Loop through each page in the list of Power BI pages
    report.set_active_page(key_s)               # Set the current page as active in the Power BI report
    visuals = report.visuals_on_page(key_s)     # Get the list of visual elements on the active page

    for i in range(len(range_of_visual[key_s])):  # Loop through each visual element on the active page
        #print(range_of_visual[key_s][i])         # Debugging statement to print the current visual element name
        visual = next(filter(lambda visual: visual['title'] == range_of_visual[key_s][i], visuals))   # Find the visual element from the active page's visuals list that matches the name in range_of_visual_param
        summarized_exported_data = report.export_visual_data(key_s, visual['name'], rows=1000, export_data_type=models.ExportDataType.SUMMARIZED.value) # Export summarized data from the visual element

        data = StringIO(summarized_exported_data)   # Convert the exported data to a string format for processing
        ampl_[range_of_visual[key_s][i]] = pd.read_csv(data)   # Read the data as a CSV and store it in the dictionary

# After looping through all pages and visuals, reset to the main page
report.set_active_page(pages[0]['name'])        # Re-activate the main page

6. Load Data into AMPL

Seamlessly import your data into the AMPL system.

### SETS
ampl.set['DISTILLATION'] = ampl_['distill_Yield'].apply(lambda row: (row['D_MODE'], row['CRUD'], row['DIST']), axis=1)
ampl.set['REFORMING'] = ampl_['reform_Yield'].apply(lambda row: (row['R_MODE'], row['DIST_R'], row['REF']), axis=1)
ampl.set['CRACKING'] = ampl_['crack_Yield'].apply(lambda row: (row['CR_MODE'], row['DIST_CR'], row['CRACK']), axis=1)
ampl.set['LUBRICATING'] = ampl_['lube_Yield'].apply(lambda row: (row['L_MODE'], row['DIST_L'], row['LUBR']), axis=1)
ampl.set['BLENDING'] = ampl_['blending_recipe'].apply(lambda row: (row['INTERMED'], row['PROD']), axis=1)

### PARAMETERS
ampl.param['nPeriod'] = len(set(ampl_['crude_Cost']['nPeriod']))
ampl.param['nStep'] = len(set(ampl_['demand_nStep_Value']['nStep']))-1
ampl.param['nPeriodByYear'] = 12

sets_ = ['CRUD','DIST', 'POLLUT', 'D_MODE', 'REF', 'R_MODE', 'CRACK', 'CR_MODE', 'LUBR', 'L_MODE', 'PROD', 'LOAN_param','LOANS', 'STAT']

for key_dict in ampl_.keys():                       # Loop through all keys in the 'ampl_' dictionary
    if key_dict in sets_:                           # Check if the current key is one of the predefined sets
        if len(ampl_[key_dict]) > 1:                # If the DataFrame with more than one row
            ampl.set[key_dict] = set(ampl_[key_dict].squeeze()) # Set the parameter in 'ampl'
        else:                                       # If only one row, directly extract the value and create a set
            ampl.set[key_dict] = set(ampl_[key_dict][key_dict])
    else:
        df = ampl_[key_dict]                        # Get the dataframe corresponding to the current key from the 'ampl_param' dictionary
        set_of_columns = df.loc[:, df.columns != key_dict].columns.tolist()  # Get a list of indexing columns (excluding column 'key_var')
        if set_of_columns:                          # If there are any remaining columns in 'r_df'
            if key_dict not in {'blending_recipe'}: # Check if the current key is not 'blending_recipe'. we use 'blending_recipe' only for set
                ampl.param[key_dict] = df.set_index(set_of_columns)[key_dict] # Set the parameter in 'ampl' with 'key_var' as the index based on the remaining columns
        else:
            ampl.param[key_dict] = ampl_[key_dict].squeeze()    # Assign the squeezed version (i.e., reduced to lowest dimension) of 'ampl_param[key_var]' to 'ampl.param'

7. Solve the Problem

Run the optimization model to get your solution

%%ampl_eval
option solver cbc;          # Choosing a solver

# Defining Output Settings
option show_stats 1;        # (1) Show statistical information about the size of the problem. Default 0 (statistics are not displayed)
option display_1col 0;      # Data Display Settings
option omit_zero_rows 1;    # Hide rows with 0 values. Default (0)

solve;                      # Solve the model
Presolve eliminates 14 constraints.
Substitution eliminates 107 variables.
Adjusted problem:
1259 variables:
	372 binary variables
	96 nonlinear variables
	791 linear variables
1343 constraints; 13845 nonzeros
	99 nonlinear constraints
	1244 linear constraints
	97 equality constraints
	1190 inequality constraints
	56 range constraints
1 nonlinear objective; 899 nonzeros.

cbc 2.10.10:cbc 2.10.10: optimal solution; objective 2783808.156
2900 simplex iterations
2900 barrier iterations
20 branching nodes
 
------------ WARNINGS ------------
WARNING:  "Tolerance violations"
  Type                         MaxAbs [Name]   MaxRel [Name]
  objective(s)                 1E+05           4E-02         
Documentation: mp.ampl.com/modeling-tools.html#automatic-solution-check.

6. Display the solution

%%ampl_eval
# Display the values of the variables:
display Plant_Working, Crude_Supply, Distill_to_Reforming, Distill_to_Cracking, Distill_to_Lubricating,  Blending, OctaneNumberMin, VaporPressure_Max, Storage_Fraction, Demand, Loan_In, Loan_Out, CashFlow, Waste_Pollutant;

# Calculate and print the octane number for each product over all periods.
printf {p in PROD, t in 1..nPeriod}: "Octane number for products: %u %s %6.2f\n", t, p, sum{(i,p) in BLENDING} Blending[i,p,t] * Intermed_Octane[i] / sum{(ii,p) in BLENDING}(if Blending[ii,p,t] = 0 then 1 else Blending[ii,p,t]);

# Calculate and print the vapor pressure limits for "Jet Fuel" over all periods.
printf {p in PROD, t in 1..nPeriod: p='Jet Fuel'}: "Vapor pressure limits of product Jet Fuel: %u %s %6.2f\n", t, p, sum{(i,p) in BLENDING} Blending[i,p,t] * Intermed_VaporPressure[i] / (if sum{(ii,p) in BLENDING} Blending[ii,p,t] = 0 then 1 else sum{(ii,p) in BLENDING}Blending[ii,p,t]) ;

# Calculate and print the percentage ratio of "Premium Gasoline" to "Regular Gasoline" over all periods.
printf {t in 1..nPeriod}: "Premium / Regular percentage: %u %6.2f\n", t,
sum{(i,p) in BLENDING: p ='Premium Gasoline'} Blending[i,p,t] / (if sum{(ii,pp) in BLENDING: pp ='Regular Gasoline'}Blending[ii,pp,t] = 0 then 1 else sum{(ii,pp) in BLENDING: pp ='Regular Gasoline'}Blending[ii,pp,t]) ;

# Calculate and print the production ratio for "Fuel Oil" for each blending component over all periods.
printf {(i,p) in BLENDING, t in 1..nPeriod: p='Fuel Oil'}: "Production Fuel Oil ratio: %u   %s %6.2f\n", t, i,
    Blending[i,p,t] / (if Blending['Residuum',p,t] = 0 then 1 else Blending['Residuum',p,t]);
Plant_Working [*] :=
 1 1    2 1    4 1    5 1    6 1    7 1    8 1    9 1   10 1   11 1   12 1
;

# $2 = Distill_to_Reforming
:                                            Crude_Supply       $2       :=
D1                  Crude1             5     4341.18             .
D1                  Crude2             4    27235.2              .
D1                  Crude2             5    30000                .
D1                  Crude2             6    30000                .
D1                  Crude2             7    30000                .
D1                  Crude2             8    30000                .
D1                  Crude2             9    30000                .
D1                  Crude2             10   25667.5              .
D1                  Crude2             11   22825.2              .
D1                  Crude2             12   25149.7              .
D2                  Crude1             1    25116.9              .
D2                  Crude1             2    40000                .
D2                  Crude1             7        4.01943e-26      .
D2                  Crude1             8        6.04316e-12      .
D2                  Crude2             2     9176.88             .
R2                  'Heavy Naphta'     1           .          3289.03
R2                  'Heavy Naphta'     2           .          6584.9
R2                  'Heavy Naphta'     4           .          4360.94
R2                  'Heavy Naphta'     5           .          5536.63
R2                  'Heavy Naphta'     6           .          4019.59
R2                  'Heavy Naphta'     7           .          3760.46
R2                  'Heavy Naphta'     8           .          3894.68
R2                  'Heavy Naphta'     9           .          4147.09
R2                  'Heavy Naphta'     10          .          3685.4
R2                  'Heavy Naphta'     11          .          3214.41
R2                  'Heavy Naphta'     12          .          3492.84
;
# $1 = Distill_to_Cracking
# $2 = Distill_to_Lubricating
:                                              $1     $2   Blending     :=
CR2                 'Heavy Oil'        1     905.436   .       .
CR2                 'Heavy Oil'        2    1555.14    .       .
CR2                 'Heavy Oil'        7    1418       .       .
CR2                 'Heavy Oil'        8    1394.04    .       .
CR2                 'Heavy Oil'        9    1394.04    .       .
CR2                 'Heavy Oil'        11    395.345   .       .
CR2                 'Heavy Oil'        12    954.904   .       .
CR2                 'Light Oil'        1     835.253   .       .
CR2                 'Light Oil'        2    1601.69    .       .
CR2                 'Light Oil'        4     871.527   .       .
CR2                 'Light Oil'        5     997.913   .       .
CR2                 'Light Oil'        6    1014.29    .       .
CR2                 'Light Oil'        7     871.887   .       .
CR2                 'Light Oil'        8     235.099   .       .
CR2                 'Light Oil'        9     235.099   .       .
CR2                 'Light Oil'        10    674.298   .       .
CR2                 'Light Oil'        11    524.828   .       .
CR2                 'Light Oil'        12    308.239   .       .
'Cracked Oil'       'Fuel Oil'         1        .      .    971.979
'Cracked Oil'       'Fuel Oil'         2        .      .   1769.69
'Cracked Oil'       'Fuel Oil'         4        .      .    522.916
'Cracked Oil'       'Fuel Oil'         5        .      .    598.748
'Cracked Oil'       'Fuel Oil'         6        .      .    554.286
'Cracked Oil'       'Fuel Oil'         7        .      .    611.245
'Cracked Oil'       'Fuel Oil'         8        .      .    865.96
'Cracked Oil'       'Fuel Oil'         9        .      .    865.96
'Cracked Oil'       'Fuel Oil'         10       .      .    404.579
'Cracked Oil'       'Fuel Oil'         11       .      .    520.476
'Cracked Oil'       'Fuel Oil'         12       .      .    681.494
'Cracked Oil'       'Jet Fuel'         6        .      .     54.2857
'Cracked Oil'       'Jet Fuel'         7        .      .    649.249
'Cracked gasoline'  'Premium Gasoline' 1        .      .    591.834
'Cracked gasoline'  'Premium Gasoline' 4        .      .    296.319
'Cracked gasoline'  'Premium Gasoline' 7        .      .    778.563
'Cracked gasoline'  'Premium Gasoline' 8        .      .    553.907
'Cracked gasoline'  'Premium Gasoline' 10       .      .    229.261
'Cracked gasoline'  'Premium Gasoline' 12       .      .    429.469
'Cracked gasoline'  'Regular Gasoline' 2        .      .   1073.32
'Cracked gasoline'  'Regular Gasoline' 5        .      .    339.291
'Cracked gasoline'  'Regular Gasoline' 6        .      .    344.857
'Cracked gasoline'  'Regular Gasoline' 9        .      .    553.907
'Cracked gasoline'  'Regular Gasoline' 11       .      .    312.859
'Heavy Naphta'      'Premium Gasoline' 2        .      .    315.576
'Heavy Naphta'      'Premium Gasoline' 5        .      .    731.602
'Heavy Naphta'      'Regular Gasoline' 1        .      .   1483.19
'Heavy Naphta'      'Regular Gasoline' 2        .      .   2443.13
'Heavy Naphta'      'Regular Gasoline' 4        .      .    541.394
'Heavy Naphta'      'Regular Gasoline' 6        .      .   1380.41
'Heavy Naphta'      'Regular Gasoline' 7        .      .   1639.54
'Heavy Naphta'      'Regular Gasoline' 8        .      .   1505.32
'Heavy Naphta'      'Regular Gasoline' 9        .      .   1252.91
'Heavy Naphta'      'Regular Gasoline' 10       .      .    934.749
'Heavy Naphta'      'Regular Gasoline' 11       .      .    894.134
'Heavy Naphta'      'Regular Gasoline' 12       .      .   1034.1
'Heavy Oil'         'Fuel Oil'         1        .      .    728.984
'Heavy Oil'         'Fuel Oil'         2        .      .   1327.27
'Heavy Oil'         'Fuel Oil'         4        .      .    392.187
'Heavy Oil'         'Fuel Oil'         5        .      .    449.061
'Heavy Oil'         'Fuel Oil'         6        .      .    415.714
'Heavy Oil'         'Fuel Oil'         7        .      .    458.434
'Heavy Oil'         'Fuel Oil'         8        .      .    649.47
'Heavy Oil'         'Fuel Oil'         9        .      .    649.47
'Heavy Oil'         'Fuel Oil'         10       .      .    303.434
'Heavy Oil'         'Fuel Oil'         11       .      .    390.357
'Heavy Oil'         'Fuel Oil'         12       .      .    511.12
'Heavy Oil'         'Jet Fuel'         1        .      .   3137.79
'Heavy Oil'         'Jet Fuel'         2        .      .   6369.43
'Heavy Oil'         'Jet Fuel'         4        .      .   4782.5
'Heavy Oil'         'Jet Fuel'         5        .      .   6119.18
'Heavy Oil'         'Jet Fuel'         6        .      .   5284.29
'Heavy Oil'         'Jet Fuel'         7        .      .   3823.56
'Heavy Oil'         'Jet Fuel'         8        .      .   3656.49
'Heavy Oil'         'Jet Fuel'         9        .      .   3656.49
'Heavy Oil'         'Jet Fuel'         10       .      .   4573.39
'Heavy Oil'         'Jet Fuel'         11       .      .   3551.09
'Heavy Oil'         'Jet Fuel'         12       .      .   3312.41
'Light Naphta'      'Premium Gasoline' 2        .      .   6084.76
'Light Naphta'      'Premium Gasoline' 4        .      .   4085.28
'Light Naphta'      'Premium Gasoline' 5        .      .   4934.12
'Light Naphta'      'Premium Gasoline' 7        .      .   3582.13
'Light Naphta'      'Premium Gasoline' 8        .      .   3601.32
'Light Naphta'      'Premium Gasoline' 9        .      .   4435.2
'Light Naphta'      'Premium Gasoline' 10       .      .   3604.3
'Light Naphta'      'Premium Gasoline' 11       .      .   3326.4
'Light Naphta'      'Premium Gasoline' 12       .      .   3524.21
'Light Naphta'      'Regular Gasoline' 1        .      .   3014.03
'Light Naphta'      'Regular Gasoline' 6        .      .   4500
'Light Naphta'      'Regular Gasoline' 7        .      .    917.875
'Light Naphta'      'Regular Gasoline' 8        .      .    898.682
'Light Naphta'      'Regular Gasoline' 9        .      .     64.8
'Light Naphta'      'Regular Gasoline' 10       .      .    245.829
'Light Naphta'      'Regular Gasoline' 11       .      .     97.3843
'Light Naphta'      'Regular Gasoline' 12       .      .    248.238
'Light Oil'         'Fuel Oil'         1        .      .   2429.95
'Light Oil'         'Fuel Oil'         2        .      .   4424.23
'Light Oil'         'Fuel Oil'         4        .      .   1307.29
'Light Oil'         'Fuel Oil'         5        .      .   1496.87
'Light Oil'         'Fuel Oil'         6        .      .   1385.71
'Light Oil'         'Fuel Oil'         7        .      .   1528.11
'Light Oil'         'Fuel Oil'         8        .      .   2164.9
'Light Oil'         'Fuel Oil'         9        .      .   2164.9
'Light Oil'         'Fuel Oil'         10       .      .   1011.45
'Light Oil'         'Fuel Oil'         11       .      .   1301.19
'Light Oil'         'Fuel Oil'         12       .      .   1703.73
'Light Oil'         'Jet Fuel'         5        .      .    426.158
'Light Oil'         'Jet Fuel'         10       .      .    367.655
'Medium Naphta'     'Premium Gasoline' 1        .      .   2206.9
'Medium Naphta'     'Premium Gasoline' 4        .      .    645.91
'Medium Naphta'     'Premium Gasoline' 5        .      .     58.9735
'Medium Naphta'     'Premium Gasoline' 6        .      .   2683.66
'Medium Naphta'     'Regular Gasoline' 1        .      .   2565.31
'Medium Naphta'     'Regular Gasoline' 2        .      .   9802.45
'Medium Naphta'     'Regular Gasoline' 4        .      .   6162.89
'Medium Naphta'     'Regular Gasoline' 5        .      .   8309.26
'Medium Naphta'     'Regular Gasoline' 6        .      .   4816.34
'Medium Naphta'     'Regular Gasoline' 7        .      .   7500
'Medium Naphta'     'Regular Gasoline' 8        .      .   7500
'Medium Naphta'     'Regular Gasoline' 9        .      .   7500
'Medium Naphta'     'Regular Gasoline' 10       .      .   6416.87
'Medium Naphta'     'Regular Gasoline' 11       .      .   5706.31
'Medium Naphta'     'Regular Gasoline' 12       .      .   6287.42
'Reformed gasoline' 'Premium Gasoline' 1        .      .   1161.26
'Reformed gasoline' 'Premium Gasoline' 2        .      .   1519.66
'Reformed gasoline' 'Premium Gasoline' 4        .      .   1053.54
'Reformed gasoline' 'Premium Gasoline' 5        .      .   1815.26
'Reformed gasoline' 'Premium Gasoline' 6        .      .   1789.11
'Reformed gasoline' 'Premium Gasoline' 7        .      .    274.491
'Reformed gasoline' 'Premium Gasoline' 8        .      .    395.824
'Reformed gasoline' 'Premium Gasoline' 9        .      .    844.8
'Reformed gasoline' 'Premium Gasoline' 10       .      .    566.443
'Reformed gasoline' 'Premium Gasoline' 11       .      .    633.6
'Reformed gasoline' 'Premium Gasoline' 12       .      .    446.319
'Reformed gasoline' 'Regular Gasoline' 1        .      .    417.473
'Reformed gasoline' 'Regular Gasoline' 2        .      .   1641.09
'Reformed gasoline' 'Regular Gasoline' 4        .      .   1039.71
'Reformed gasoline' 'Regular Gasoline' 5        .      .    842.321
'Reformed gasoline' 'Regular Gasoline' 6        .      .    140.296
'Reformed gasoline' 'Regular Gasoline' 7        .      .   1530.53
'Reformed gasoline' 'Regular Gasoline' 8        .      .   1473.62
'Reformed gasoline' 'Regular Gasoline' 9        .      .   1145.8
'Reformed gasoline' 'Regular Gasoline' 10       .      .   1202.55
'Reformed gasoline' 'Regular Gasoline' 11       .      .    909.315
'Reformed gasoline' 'Regular Gasoline' 12       .      .   1230.25
Residuum            'Fuel Oil'         1        .      .    242.995
Residuum            'Fuel Oil'         2        .      .    442.423
Residuum            'Fuel Oil'         4        .      .    130.729
Residuum            'Fuel Oil'         5        .      .    149.687
Residuum            'Fuel Oil'         6        .      .    138.571
Residuum            'Fuel Oil'         7        .      .    152.811
Residuum            'Fuel Oil'         8        .      .    216.49
Residuum            'Fuel Oil'         9        .      .    216.49
Residuum            'Fuel Oil'         10       .      .    101.145
Residuum            'Fuel Oil'         11       .      .    130.119
Residuum            'Fuel Oil'         12       .      .    170.373
Residuum            'Jet Fuel'         1        .      .   3022.21
Residuum            'Jet Fuel'         2        .      .   5950.57
Residuum            'Jet Fuel'         4        .      .   3137.5
Residuum            'Jet Fuel'         5        .      .   4014.67
Residuum            'Jet Fuel'         6        .      .   3461.43
Residuum            'Jet Fuel'         7        .      .   3447.19
Residuum            'Jet Fuel'         8        .      .   3383.51
Residuum            'Jet Fuel'         9        .      .   3383.51
Residuum            'Jet Fuel'         10       .      .   2978.95
Residuum            'Jet Fuel'         11       .      .   2608.91
Residuum            'Jet Fuel'         12       .      .   2847.59
;

:                     OctaneNumberMin VaporPressure_Max Storage_Fraction    :=
'Fuel Oil'         1          .                .           568.165
'Fuel Oil'         2          .                .          4878.24
'Fuel Oil'         3          .                .          1304.34
'Fuel Oil'         4          .                .            60.0276
'Fuel Oil'         5          .                .             2.27374e-12
'Fuel Oil'         6          .                .           118.093
'Fuel Oil'         7          .                .           489.94
'Fuel Oil'         8          .                .          1714.39
'Fuel Oil'         9          .                .          2468.6
'Fuel Oil'         10         .                .          1073.29
'Fuel Oil'         11         .                .           100.265
'Fuel Oil'         12         .                .            -1.77351e-11
'Jet Fuel'         1          .                0             9.09495e-13
'Jet Fuel'         2          .                0          6098.4
'Jet Fuel'         3          .                0            -9.09495e-13
'Jet Fuel'         4          .                0            -5.45697e-12
'Jet Fuel'         5          .                0             1.27329e-11
'Jet Fuel'         6          .                0            -1.09139e-11
'Jet Fuel'         7          .                0            -1.00044e-11
'Jet Fuel'         8          .                0            -4.54747e-12
'Jet Fuel'         9          .                0            -9.09495e-13
'Jet Fuel'         10         .                0             1.00044e-11
'Jet Fuel'         11         .                0             9.09495e-12
'Jet Fuel'         12         .                0             1.00044e-11
'Premium Gasoline' 1      -3.09633             .             2.27374e-12
'Premium Gasoline' 2      -3.11884             .          3912.48
'Premium Gasoline' 3       0                   .             3.63798e-12
'Premium Gasoline' 4      -3.02616             .          2095.6
'Premium Gasoline' 5      -3.06752             .          5197.88
'Premium Gasoline' 6      -3.13694             .          3530.89
'Premium Gasoline' 7      -3.20608             .          1589.64
'Premium Gasoline' 8      -3.21007             .            -1.00044e-11
'Premium Gasoline' 9      -3.2059              .            -2.09184e-11
'Premium Gasoline' 10     -2.98411             .            -5.45697e-12
'Premium Gasoline' 11     -2.99578             .            -7.7307e-12
'Premium Gasoline' 12     -2.96132             .            -4.54747e-12
'Regular Gasoline' 1          .                .            -1.81899e-12
'Regular Gasoline' 2          .                .          7330.4
'Regular Gasoline' 3          .                .            -3.63798e-12
'Regular Gasoline' 4          .                .            -2.72848e-12
'Regular Gasoline' 5          .                .          1366.98
'Regular Gasoline' 6          .                .           682.848
'Regular Gasoline' 7          .                .            -1.81899e-11
'Regular Gasoline' 9          .                .             1.27329e-11
'Regular Gasoline' 10         .                .             2.18279e-11
'Regular Gasoline' 11         .                .             3.45608e-11
'Regular Gasoline' 12         .                .             1.81899e-11
;

Demand ['Fuel Oil',Station1,*,*]
:     1      2     3        4         5   6    :=
1       0   3800   0    0             0   0
2       0   3610   0    0             0   0
3       0   3610   0    0             0   0
4       0   3610   0    0             0   0
5       0   2755   0    0             0   0
6       0   2375   0   -3.57307e-27   0   0
7       0   2375   0    0             0   0
8       0   2660   0    0             0   0
9       0   3135   0    0             0   0
10      0   3230   0    0             0   0
11      0   3325   0    0             0   0
12   3168      0   0    0             0   0

 ['Jet Fuel',Station1,*,*]
:      1          2         3        4              5         6    :=
1     6160    0             0    0              0             0
2     6160    0             0    0              0             0
3     6160    0             0   -1.64424e-27    0             0
4     7920    0             0    0              0             0
5    10560   -5.77581e-27   0    0              0             0
6     8800    0             0    0              0             0
7     7920    0             0    0             -3.91569e-27   0
8     7040    0             0   -2.69196e-27    0             0
9     7040    0             0    0              0             0
10    7920    0             0    0              0             0
11    6160    0             0   -1.38802e-26    0             0
12    6160    0             0    2.44485e-27    0             0

 ['Lube Oil',Station1,*,*]
:    1   2   3   4   5   6    :=

 ['Premium Gasoline',Station1,*,*]
:     1     2   3   4   5        6          :=
1    3960   0   0   0   0    0
2    3960   0   0   0   0    0
3    3960   0   0   0   0    0
4    3960   0   0   0   0    0
5    4400   0   0   0   0    0
6    6160   0   0   0   0    0
7    6600   0   0   0   0    0
8    6160   0   0   0   0    0
9    5280   0   0   0   0    0
10   4400   0   0   0   0    0
11   3960   0   0   0   0    0
12   4400   0   0   0   0   -7.48227e-26

 ['Regular Gasoline',Station1,*,*]
:       1           2              3        4        5             6         :=
1     7480      0             0             0   0              0
2     7480      0             0             0   0              0
3     7480     -1.66463e-27   0             0   0              0
4     7744      0             0             0   3.5795e-27     0
5     8096      0             0             0   0              0
6    11880      0             0             0   0              0
7    12284.7    0             0             0   0              0
8    11377.6    0             0             0   0              0
9    10517.4    0             0             0   0              0
10    8800      0             0             0   5.71403e-27    0
11    7920      0             2.83097e-27   0   0              0
12    8800      0             0             0   0             -3.11251e-27
;

:     Loan_In Loan_Out    :=
I 2    194710        0
I 3         0   195765
;

CashFlow [*] :=
 1   232094      4    78728.1    7   615940     10   350867
 2 -2232090      5  -433610      8   386024     11   331837
 3  2481690      6   535397      9   197119     12   240251
;

Waste_Pollutant [*,*] (tr)
:  Hydrocarbon 'Sulfur dioxide'    :=
1     269077         385681
2     511614         734160
4     250804         371779
5     324924         480952
6     248116         361996
7     267436         392203
8     267234         392757
9     276422         408244
10    219856         322741
11    200985         295620
12    228633         337255
;

Octane number for products: 1 Fuel Oil   0.00
Octane number for products: 2 Fuel Oil   0.00
Octane number for products: 3 Fuel Oil   0.00
Octane number for products: 4 Fuel Oil   0.00
Octane number for products: 5 Fuel Oil   0.00
Octane number for products: 6 Fuel Oil   0.00
Octane number for products: 7 Fuel Oil   0.00
Octane number for products: 8 Fuel Oil   0.00
Octane number for products: 9 Fuel Oil   0.00
Octane number for products: 10 Fuel Oil   0.00
Octane number for products: 11 Fuel Oil   0.00
Octane number for products: 12 Fuel Oil   0.00
Octane number for products: 1 Regular Gasoline  83.99
Octane number for products: 2 Regular Gasoline  83.99
Octane number for products: 3 Regular Gasoline   0.00
Octane number for products: 4 Regular Gasoline  83.98
Octane number for products: 5 Regular Gasoline  83.98
Octane number for products: 6 Regular Gasoline  84.00
Octane number for products: 7 Regular Gasoline  83.99
Octane number for products: 8 Regular Gasoline  83.99
Octane number for products: 9 Regular Gasoline  84.00
Octane number for products: 10 Regular Gasoline  83.99
Octane number for products: 11 Regular Gasoline  84.00
Octane number for products: 12 Regular Gasoline  83.99
Octane number for products: 1 Lube Oil   0.00
Octane number for products: 2 Lube Oil   0.00
Octane number for products: 3 Lube Oil   0.00
Octane number for products: 4 Lube Oil   0.00
Octane number for products: 5 Lube Oil   0.00
Octane number for products: 6 Lube Oil   0.00
Octane number for products: 7 Lube Oil   0.00
Octane number for products: 8 Lube Oil   0.00
Octane number for products: 9 Lube Oil   0.00
Octane number for products: 10 Lube Oil   0.00
Octane number for products: 11 Lube Oil   0.00
Octane number for products: 12 Lube Oil   0.00
Octane number for products: 1 Premium Gasoline  93.95
Octane number for products: 2 Premium Gasoline  93.98
Octane number for products: 3 Premium Gasoline   0.00
Octane number for products: 4 Premium Gasoline  93.98
Octane number for products: 5 Premium Gasoline  93.99
Octane number for products: 6 Premium Gasoline  93.94
Octane number for products: 7 Premium Gasoline  93.96
Octane number for products: 8 Premium Gasoline  93.96
Octane number for products: 9 Premium Gasoline  93.95
Octane number for products: 10 Premium Gasoline  93.96
Octane number for products: 11 Premium Gasoline  93.93
Octane number for products: 12 Premium Gasoline  93.96
Octane number for products: 1 Jet Fuel   0.00
Octane number for products: 2 Jet Fuel   0.00
Octane number for products: 3 Jet Fuel   0.00
Octane number for products: 4 Jet Fuel   0.00
Octane number for products: 5 Jet Fuel   0.00
Octane number for products: 6 Jet Fuel   0.00
Octane number for products: 7 Jet Fuel   0.00
Octane number for products: 8 Jet Fuel   0.00
Octane number for products: 9 Jet Fuel   0.00
Octane number for products: 10 Jet Fuel   0.00
Octane number for products: 11 Jet Fuel   0.00
Octane number for products: 12 Jet Fuel   0.00
Vapor pressure limits of product Jet Fuel: 1 Jet Fuel   0.33
Vapor pressure limits of product Jet Fuel: 2 Jet Fuel   0.33
Vapor pressure limits of product Jet Fuel: 3 Jet Fuel   0.00
Vapor pressure limits of product Jet Fuel: 4 Jet Fuel   0.38
Vapor pressure limits of product Jet Fuel: 5 Jet Fuel   0.41
Vapor pressure limits of product Jet Fuel: 6 Jet Fuel   0.39
Vapor pressure limits of product Jet Fuel: 7 Jet Fuel   0.43
Vapor pressure limits of product Jet Fuel: 8 Jet Fuel   0.34
Vapor pressure limits of product Jet Fuel: 9 Jet Fuel   0.34
Vapor pressure limits of product Jet Fuel: 10 Jet Fuel   0.41
Vapor pressure limits of product Jet Fuel: 11 Jet Fuel   0.37
Vapor pressure limits of product Jet Fuel: 12 Jet Fuel   0.35
Premium / Regular percentage: 1   0.53
Premium / Regular percentage: 2   0.53
Premium / Regular percentage: 3   0.00
Premium / Regular percentage: 4   0.79
Premium / Regular percentage: 5   0.79
Premium / Regular percentage: 6   0.40
Premium / Regular percentage: 7   0.40
Premium / Regular percentage: 8   0.40
Premium / Regular percentage: 9   0.50
Premium / Regular percentage: 10   0.50
Premium / Regular percentage: 11   0.50
Premium / Regular percentage: 12   0.50
Production Fuel Oil ratio: 1   Cracked Oil   4.00
Production Fuel Oil ratio: 2   Cracked Oil   4.00
Production Fuel Oil ratio: 3   Cracked Oil   0.00
Production Fuel Oil ratio: 4   Cracked Oil   4.00
Production Fuel Oil ratio: 5   Cracked Oil   4.00
Production Fuel Oil ratio: 6   Cracked Oil   4.00
Production Fuel Oil ratio: 7   Cracked Oil   4.00
Production Fuel Oil ratio: 8   Cracked Oil   4.00
Production Fuel Oil ratio: 9   Cracked Oil   4.00
Production Fuel Oil ratio: 10   Cracked Oil   4.00
Production Fuel Oil ratio: 11   Cracked Oil   4.00
Production Fuel Oil ratio: 12   Cracked Oil   4.00
Production Fuel Oil ratio: 1   Heavy Oil   3.00
Production Fuel Oil ratio: 2   Heavy Oil   3.00
Production Fuel Oil ratio: 3   Heavy Oil   0.00
Production Fuel Oil ratio: 4   Heavy Oil   3.00
Production Fuel Oil ratio: 5   Heavy Oil   3.00
Production Fuel Oil ratio: 6   Heavy Oil   3.00
Production Fuel Oil ratio: 7   Heavy Oil   3.00
Production Fuel Oil ratio: 8   Heavy Oil   3.00
Production Fuel Oil ratio: 9   Heavy Oil   3.00
Production Fuel Oil ratio: 10   Heavy Oil   3.00
Production Fuel Oil ratio: 11   Heavy Oil   3.00
Production Fuel Oil ratio: 12   Heavy Oil   3.00
Production Fuel Oil ratio: 1   Light Oil  10.00
Production Fuel Oil ratio: 2   Light Oil  10.00
Production Fuel Oil ratio: 3   Light Oil   0.00
Production Fuel Oil ratio: 4   Light Oil  10.00
Production Fuel Oil ratio: 5   Light Oil  10.00
Production Fuel Oil ratio: 6   Light Oil  10.00
Production Fuel Oil ratio: 7   Light Oil  10.00
Production Fuel Oil ratio: 8   Light Oil  10.00
Production Fuel Oil ratio: 9   Light Oil  10.00
Production Fuel Oil ratio: 10   Light Oil  10.00
Production Fuel Oil ratio: 11   Light Oil  10.00
Production Fuel Oil ratio: 12   Light Oil  10.00
Production Fuel Oil ratio: 1   Residuum   1.00
Production Fuel Oil ratio: 2   Residuum   1.00
Production Fuel Oil ratio: 3   Residuum   0.00
Production Fuel Oil ratio: 4   Residuum   1.00
Production Fuel Oil ratio: 5   Residuum   1.00
Production Fuel Oil ratio: 6   Residuum   1.00
Production Fuel Oil ratio: 7   Residuum   1.00
Production Fuel Oil ratio: 8   Residuum   1.00
Production Fuel Oil ratio: 9   Residuum   1.00
Production Fuel Oil ratio: 10   Residuum   1.00
Production Fuel Oil ratio: 11   Residuum   1.00
Production Fuel Oil ratio: 12   Residuum   1.00

7. Retrieve solution and export it to a *.xlsx file

Write the data to an *.xlsx file.

! pip install openpyxl
amplvar = dict()
# Create an ExcelWriter object for saving DataFrames to an Excel file at the specified path
with pd.ExcelWriter('oil_refyning.xlsx') as writer:
    # Generate a list of all variable names from the AMPL model
    list_of_ampl_variables = [item[0] for item in ampl.get_variables()]
    # Iterate over each variable name in the list
    for key_ampl in list_of_ampl_variables:
        # Skip certain variables that are not to be processed (these variables won't be included in the output)
        if key_ampl not in ['X', 'Cracking_X', 'Distill_X', 'Lubricating_X', 'Reform_X']:
            # Convert the AMPL variable data to a pandas DataFrame
            df = ampl.var[key_ampl].to_pandas()
            # Filter the DataFrame to include only rows where the variable's value is greater than a small threshold (10^-5)
            filtered_df = df[df[f"{key_ampl}.val"] > 10e-5]
            # Save the filtered DataFrame to the corresponding sheet in the Excel file
            if not filtered_df.empty:  # Ensure that only non-empty DataFrames are saved
                filtered_df.to_excel(writer, sheet_name=key_ampl, index=True)
                # Save the filtered DataFrame to a dictionary
                #amplvar[key_ampl] = filtered_df

8. Retrieve solution and export it to a Google Sheets document

Write the data to an Google Sheets document.

! pip install google-auth google-auth-oauthlib gspread
import gspread # https://docs.gspread.org/en/v6.0.0/user-guide.html
from google.oauth2.service_account import Credentials
scopes = ['https://www.googleapis.com/auth/spreadsheets']
credentials = Credentials.from_service_account_file('credentials.json', scopes=scopes)

client = gspread.authorize(credentials)     # Use gspread to open the Google Sheet

spreadsheet = client.open_by_key("15R3QuyUFN_oVztI_P4lM9C5mjsYC7ZKUO-BogCdt81s")        # Open the Google Sheets document by name

list_of_ampl_variables = [item[0] for item in ampl.get_variables()]                     # Retrieve a list of AMPL variables from the AMPL environment
variables_to_exclude = ['X', 'Cracking_X', 'Distill_X', 'Lubricating_X', 'Reform_X']    # List of variables to exclude from the export
filtered_ampl_variables = [var for var in list_of_ampl_variables if var not in variables_to_exclude]

for key_ampl in filtered_ampl_variables:                            # Iterate through each AMPL variable
    ampl_dict = ampl.getVariable(key_ampl).getValues().toDict()     # Retrieve AMPL variable values and convert them to a dictionary

    filtered_dict = {       # Filter the dictionary to exclude certain variables and include only values greater than 0.0001
    key: value
    for key, value in ampl_dict.items()
    if value > 0.0001}

    #if filtered_dict:       # Only process further if there's data to be exported
    if key_ampl not in [sheet.title for sheet in spreadsheet.worksheets()]: # Check if the current variable's name is not already a sheet in the spreadsheet
        new_sheet_name = f"{key_ampl}"                                      # Create a new sheet with a unique name based on the variable
        spreadsheet.add_worksheet(title=new_sheet_name, rows=100, cols=20)  # Add a new worksheet to the spreadsheet with a default size of 100 rows and 20 columns

    sheet = spreadsheet.worksheet(key_ampl)                 # Get the worksheet object corresponding to the current variable

    sheet.clear()                                           # Clear the existing data in the sheet

    first_key = next(iter(filtered_dict.keys()), None)      # Get the first key to determine the data structure

    if isinstance(first_key, tuple):                        # Check if the first key is a tuple, indicating multi-dimensional data
        number_of_columns = len(first_key)
        indices = [f'index{i}' for i in range(number_of_columns)]

        data = []                                           # Prepare data for DataFrame
        for key, value in filtered_dict.items():
            data.append([str(k) for k in key] + [value])    # Convert each part of the key tuple to a string and append the value

        columns = indices + [key_ampl+".val"]               # Define column names including value column

    else:
        # Handle one-dimensional data
        indices = ['']                                      # Single index column
        data = [[str(key), value] for key, value in filtered_dict.items()]    # Convert key to string and pair it with the value
        columns = indices + [key_ampl+".val"]               # Define column names including value column

    df = pd.DataFrame(data, columns=columns)                # Create a DataFrame from the prepared data

    data_list = [df.columns.tolist()] + df.values.tolist()  # Convert DataFrame to list of lists

    sheet.update(data_list,'A1')                           # Update the Google Sheet with the prepared data starting from cell A1

Load Data into the Power BI Report from Google Sheets

To ensure we have the most up-to-date information, we’ll refresh the data in the Oil Refinery report. Once the data is updated, the latest model solution will be visualized on the “Result” page, providing a clear and accurate reflection of the current outcomes.

alt text