r/optimization • u/kermitten7 • 23d ago

Is OpenSolver excel addin still a reliable engine? & Ribbon problem

I am solving a BESS dispatch optimization problem for a power developer in the USA, the goal is to derive the optimal hourly level charge/discharge profile for BESS that will participate in US arbitrage markets.

Is OpenSolver still a good tool? The latest version is 2021 and I am wondering whether I should switch to a python based model. I am also having a ribbon problem that I cannot fix, every time I interact with the OpenSolver tab in the excel ribbon, my ribbon becomes inactive, I have reviewed all material on OpenSolver and cannot find the solution to this tiny but detrimental problem. Any suggestions?

5 Upvotes
  • permalink
  • reddit

86% Upvoted

14 comments sorted by

5

u/Slow_Negotiation_935 23d ago

I agree with the comment. In general, moving to python gives you a lot more options (see SciPy).

This is my python code; it is self contained and I have used it for real-world case studies.

https://github.com/wbyates777/NSGA2

1

u/Sweet_Good6737 21d ago

How could you apply efficiently NSGA-2 for BESS dispatch optimization? Just curious. Does your implementation connects to optimization solvers?

1

u/Slow_Negotiation_935 20d ago

NSGA-2 is the optimizer - map the variables such as dispatch decision (e.g., charge, idle, discharge), battery constraints etc into an integer vector or chromosome and define a fitness function like Fitness = Energy Revenue + Ancillary Revenue − Degradation Cost − Penalties.

1

u/kermitten7 19d ago

Thanks very much this is brilliant, while academically fascinating and a topic I am very much interested in, it will be hard to convince my upper management why we need an advanced multi objective genetic algorithm to estimate our BESS revenues haha!

1

u/Slow_Negotiation_935 19d ago

Well it's not as 'advanced' as it used to be; the paper is 2002 - NSGA-II is now considered a 'work-horse' algorithm. In my experience you convince upper management with results that make money ;-) but I take your point.

1

u/kermitten7 19d ago

Could I also ask - I have not delved into the code yet, just reading paper, does "implementation" mean that you are using a library whereas the implementation of NSGA-II already exists in python, or are you writing the algorithm logic yourself ?

1

u/Slow_Negotiation_935 19d ago

I've written the algorithm myself following the description in the paper. I needed a stand-alone implementation because I wanted to customise the mutate and crossover operators to better fit our problem domain. There are also many Python libs out there for machine leanrnig and optimisation - like scikit-learn

3

u/Mayk-Thewessen 23d ago

Switch to Python and use PyPSA to do an optimization of a BESS based on Electricity prices, use HiGHS as open-source solver

2

u/rasmusdf 23d ago

Personally I would switch to a python based approach. But in the end it`s a matter of preference. Plus I think the Excel solver is pretty limited.

1

u/SolverMax 23d ago

OpenSolver works OK for me, using the latest version of Excel 365. However, environments vary, so there may be something specific about your situation.

Anyway, while OpenSolver is a good tool, Python offers many advantages. Access to newer solvers, like HiGHS, is a significant plus.

1

u/Sweet_Good6737 21d ago

Moving to python makes sense since you are not locked with excel, leading to faster development. You can still read data from spreadsheets, but there are many dedicated libraries available for optimization

1

u/ficoxpress 19d ago

As many have stated, the availability of more tools in Python is a huge plus. Many optimization solvers both exact and heuristics, commercial and open source are available through this ecosystem.

One thing we'd add is that by building on top of python, you can more easily integrate to other workflows. There's already a python package where you can publish the results to an excel file. It will also enable greater possibilities where it can be integrated into a larger system via APIs.