Importing data into AIMMS from Exel

[Yiseyon Hosu]

Please can someone guide me into how i can import dataalready in Excel to AIMMS to run optimisation  model. the data are many and cannot be imputed manually

[Guido Diepen]

Hi,

please take a look at the Excel example that comes with the AIMMS installation. You can find this by starting AIMMS and click on Index of all examples on the startpage. In the index of all examples, you can go to the second tab page (Functional Examples) and select the Excel example there.

Additionally, you can take a look at the blog post  http://blog.aimms.com/2011/11/reading-multi-dimensional-excel-data-with-excelretrievetable/  to have a more detailed explanation about reading multi dimensional data from Excel into AIMMS.

Guido Diepen

AIMMS Specialist

[Yiseyon Hosu]

Thanks so much. I will get back to you

On Friday, April 20, 2012 1:35:44 PM UTC+2, Yiseyon Hosu wrote:

[Guido Diepen]

Hi Carly,

there are two ways that you can combine AIMMS with Excel:

• Use AIMMS as the frontend and just get the data for your model from an Excel workbook. This can be done via the Excel (or spreadsheet in newer versions of AIMMS) functions in AIMMS.
• Use Excel as the frontend and have Excel start an AIMMS project, fill it with data, execute some procedures, and retrieve the data back to Excel again. This is done via the AIMMS Excel Addin that you have to add to your Excel installation.

Both of these approaches are demonstrated in the AIMMS Excel Link example, which you can find by starting AIMMS, clicking on "Index of all examples", clicking on the second tab page "Functional examples", and double-clicking on the line that states Excel-Link. 

Which of the two approaches do you want to use?

The error you provide suggests you are trying the second approach. Can you verify if the Excel Addin example works without any errors? If that works, it means there is just something going wrong with your own project and not with the link itself. Do you happen to have the Carlywork.prj file still open as an AIMMS developer?

Guido Diepen

AIMMS Specialist

On Tuesday, June 12, 2012 3:39:30 PM UTC+2, Carly wrote:

I had a similar problem to Yiseyon, 

I am working on a problem and when defining some of my sets, I would like to reference or call on data from certain Excel files that have to do with the problem. I do not want to transfer the entire Excel file's contents to my set, but only a specific column without copy and pasting.  I was wondering if there is a specific line of code to use in the definition section of the attributes of that set that will call upon a specific Excel file. For example call a file named work1.xlxs and want to call specifically the data in column B.

I have installed and tried the AIMMS add-in function in Excel. However, when I enter the AIMMS Interface Setup, Execution Sequences, I try to select an AIMMS Identifier (by clicking on the '...' button) but I receive this message: "Could not startup AIMMS" and then "AIMMS cannot open project 'Carlywork.prj': ProjectOpen failed. Starting up Aimms failed. Program initialization error."

I am unsure why this is happening and what I should do.

Any help would be greatly appreciated.

[Carly]

Hi Guido, 

I am interested in your second method to combine AIMMS and Excel. I have uninstalled and then re-installed the AIMMS Add-In feature to Excel and it seems to be working now. 

I am now interested in some of the code used in AIMMS (for example in the definition section of a set or parameter or constraint) to call the data from Excel. I noticed the Spreadsheet::RetrieveSet notation, but was wondering if you could provide me to a few examples of how to call a specific Excel file so that data can be used from different worksheets and columns on the same Excel file.

Thanks,

Carly 

On Friday, April 20, 2012 7:35:44 AM UTC-4, Yiseyon Hosu wrote:

[Guido Diepen]

Hi Carly,

when you use the second approach (i.e. Excel as frontend and AIMMS as optimization engine), you typically don't need to use the Spreadsheet::RetrieveSet functions. Instead, you will have to define the actions in the sequences.

If you want to use AIMMS as the frontend, you will have to make use of the Spreadsheet::RetrieveSet (or ExcelRetrieveSet for older versions of AIMMS) functions. If you want to have examples on how to use these functions, I would like to point you again to the example I mentioned in my previous answer: this demonstrates how to use the Excel/Spreadsheet functions in AIMMS to communicate data to/from spreadsheets.

Guido Diepen

AIMMS Specialist

[Yiseyon Hosu]

Please, can someone guide me on how to declare constraint on AIMMS. the error given is ' The constraint "availresources" without a suffix cannot be used inside the definition of a constraint.

what is the meaning suffix in declaring a constant. Please, I need a help urgently.

thanks

--
You received this message because you are subscribed to the Google Groups "AIMMS - The Modeling System" group.

To view this discussion on the web visit https://groups.google.com/d/msg/aimms/-/O4-0TTwVF00J.

To post to this group, send email to ai...@googlegroups.com.
To unsubscribe from this group, send email to aimms+un...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/aimms?hl=en.

--
Best regards

Sunday Y.A.F Hosu

Doctoral candidate,

Department of Agricultural Economics, Extension & Rural Development,

University of Fort Hare,

Alice campus

[Guido Diepen]

Hi,

based on just the error I don't know what exactly is the problem. My guess is that you created a new constraint in your model and tried to put something in the definition of this constraint which is not allowed by AIMMS. Are you using another constraint in the definition of your current constraint?

Can you please provide what exactly you put in the definition of the constraint?

Also, please create a new post/question on the group for each new question you have. This will help others to easily find topics related to their own problems.

Guido Diepen

AIMMS Specialist

On Saturday, June 16, 2012 2:34:20 PM UTC+2, Yiseyon Hosu wrote:

Please, can someone guide me on how to declare constraint on AIMMS. the error given is ' The constraint "availresources" without a suffix cannot be used inside the definition of a constraint.

what is the meaning suffix in declaring a constant. Please, I need a help urgently.

thanks

On Tue, Jun 12, 2012 at 9:37 PM, Guido Diepen <Guido....@aimms.com> wrote:

Hi Carly,

when you use the second approach (i.e. Excel as frontend and AIMMS as optimization engine), you typically don't need to use the Spreadsheet::RetrieveSet functions. Instead, you will have to define the actions in the sequences.

If you want to use AIMMS as the frontend, you will have to make use of the Spreadsheet::RetrieveSet (or ExcelRetrieveSet for older versions of AIMMS) functions. If you want to have examples on how to use these functions, I would like to point you again to the example I mentioned in my previous answer: this demonstrates how to use the Excel/Spreadsheet functions in AIMMS to communicate data to/from spreadsheets.

Guido Diepen

AIMMS Specialist

On Tuesday, June 12, 2012 9:08:03 PM UTC+2, Carly wrote:

Hi Guido, 

I am interested in your second method to combine AIMMS and Excel. I have uninstalled and then re-installed the AIMMS Add-In feature to Excel and it seems to be working now. 

I am now interested in some of the code used in AIMMS (for example in the definition section of a set or parameter or constraint) to call the data from Excel. I noticed the Spreadsheet::RetrieveSet notation, but was wondering if you could provide me to a few examples of how to call a specific Excel file so that data can be used from different worksheets and columns on the same Excel file.

Thanks,

Carly 

On Friday, April 20, 2012 7:35:44 AM UTC-4, Yiseyon Hosu wrote:

Please can someone guide me into how i can import dataalready in Excel to AIMMS to run optimisation  model. the data are many and cannot be imputed manually

--
You received this message because you are subscribed to the Google Groups "AIMMS - The Modeling System" group.

To view this discussion on the web visit https://groups.google.com/d/msg/aimms/-/O4-0TTwVF00J.

To post to this group, send email to ai...@googlegroups.com.

To unsubscribe from this group, send email to aimms+unsubscribe@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/aimms?hl=en.

[Yiseyon Hosu]

Thanks,

I an still try to build the model for crop-livestock integration.

I declared my indices as i=for farm gross margin, and j for the constraints. In the parameter section, I put enterprise gross margin with idex of i, then the interaction of enterprise with the resource (which is part of the constraints ) as i,j. Now in the constraint section i want to put the constraint so that I can enter the data. that was when the error came up. 

My question is can i declare constraint without putting the parameters or can i just take a stament of constraints in the parameter for constraints. 

Please, guide

To view this discussion on the web visit https://groups.google.com/d/msg/aimms/-/DsKXp144z60J.

To post to this group, send email to ai...@googlegroups.com.

To unsubscribe from this group, send email to aimms+un...@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/aimms?hl=en.

[Guido Diepen]

Hi,

unfortunately, your explanation is not clear to me. 

However, I just tried one thing to see if I could reproduce the error you were seeing, and I was able to by using a constraint in the definition of a constraint, like shown below:

Variable: myVariable

Constraint: MyConstraint

Definition: myVariable >= 5

Constraint myConstraint2

Definition: myConstraint >= 3

If you use the above in an AIMMS model, you will get the following error:

The constraint "myConstraint" without a suffix cannot be used inside the definition of a constraint.

In the definition of a constraint, you just cannot use any other constraint. The reason is that it is not clear what it means (i.e. what would be the meaning of the expression   myConstraint >= 3 ?).

Guido Diepen

AIMMS Specialist

[Yiseyon Hosu]

I think I know what is the problem. I am declaring the constraints twice.I have declared it in parameter section as was guided in the manual pg 11-36. I just removed it now and added linear programming and press f5 and it told me no error

thanks

To view this discussion on the web visit https://groups.google.com/d/msg/aimms/-/h5VYV8xcYuoJ.

To post to this group, send email to ai...@googlegroups.com.

To unsubscribe from this group, send email to aimms+un...@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/aimms?hl=en.

[Yiseyon Hosu]

Helo, i am back.I so sorry to discover my problem is far from been over

is asnybody there to help. please

[Yiseyon Hosu]

pls, can anyone guide me on how to run optimisation/maximisation on two enterprises. For example crop mixture and livestock mixtures. there is not example of maximising model in the AIMMS manual that i have. can any one help me with this. my project is stucked on the linear programming.

[Yiseyon Hosu]

This was the error given. can anyone help

Warning: The columns "UnitEntMixGM(mabncabcarpotspi,Oct_Aprillabor)" and "UnitEntMixGM(mabncabcarpotspi,Jan_Declabor)" are equal in the generated mathematical program "MaxFarmGMPlan". This may lead to non-unique solutions.  For more information regarding potential causes and consequences, see the help associated with the option "Warning_duplicate_column".

On Friday, April 20, 2012 1:35:44 PM UTC+2, Yiseyon Hosu wrote:

[Guido Diepen]

Hi,

please note that this actually is not an error, but only a warning. However,  having multiple columns with the exact same coefficients in all the constraints + objective often is caused by some error in your constraints and/or your data.

If you have the following math program:

max x1 + x2

s.t.

x1 + x2 <= 10

x1, x2 >= 0

the columns x1 and x2 both have the exact same coefficients. As stated in the warning, this may lead to non-unique solutions. Please see the instruction in our knowledge base article (http://www.aimms.com/aimms/knowledgebase/kb000017.html) for printing out the constraint listing with AIMMS. After you run the procedure to solve your math program, you can then view the constraint listing via File > Open > Listing File. Please check this generated version of your mathematical program and check which parts are not as expected. When you know that, you can modify the definitions of your constraints accordingly.

Guido Diepen

AIMMS Specialist

[Yiseyon Hosu]

Hello,

can any help with how to run maximisation model on AIMMS? I have been having problem on how to write the attribite of the variables & constraints in the model. the worked example given was on cost minimisation. pls HELP

--
You received this message because you are subscribed to the Google Groups "AIMMS - The Modeling System" group.

To view this discussion on the web visit https://groups.google.com/d/msg/aimms/-/ub5g_ka1WwoJ.

To post to this group, send email to ai...@googlegroups.com.
To unsubscribe from this group, send email to aimms+un...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/aimms?hl=en.

[Yiseyon Hosu]

Also, i confused wither to use * sign joining both enterprise attributes and the constraints for maximisation too. I just an example of maximisation to follow as minimisation transport model. PLEASE

[Guido Diepen]

Hi,

to change your model into a maximization problem instead of a minimization problem, the only thing you have to do is open the Math Program identifier and change the direction from minimize into maximize.

Your question about using the * sign is not clear to me, what do you mean with this?

Guido Diepen

AIMMS Specialist

Hello,

To unsubscribe from this group, send email to aimms+unsubscribe@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/aimms?hl=en.

--
Best regards

Sunday Y.A.F Hosu

Doctoral candidate,

Department of Agricultural Economics, Extension & Rural Development,

University of Fort Hare,

Alice campus

[Yiseyon Hosu]

thanks so much.

in the transport model, we have both supply & demand. the variable attributes was written: sum ((p,c), Unittransportcost(p,c)*Transport(p,c). 

My model is on combination of crops & livestocks together. which is not supply and demand.I used the gross margin, the production processess and the contraints.

in running the maximization model, will the variable still be let say, unitenterpriseGM (c,l)*Grossmargin(c,l)? as it was stated in minimizing model?

To view this discussion on the web visit https://groups.google.com/d/msg/aimms/-/-sv4BVeGmH0J.

To post to this group, send email to ai...@googlegroups.com.

To unsubscribe from this group, send email to aimms+un...@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/aimms?hl=en.

[Guido Diepen]

Hi,

it could be that this is correct, but I can't say that. This all depends on the model you are building. Putting the sum( (c,l) , unitenterpriseGM(c,l)*GrossMargin(c,l) ) in the definition attribute of a variable TotalProfit (for example) will make AIMMS do the following two things:

* Generate a variable TotalProfit

* Generate an equality constraint TotalProfit_definition  with the definition

        TotalProfit =   sum ( (c,l) ,  unitenterpriseGM(c,l)*GrossMargin(c,l)  )

Guido Diepen

AIMMS Specialist