Can anyone transform Excel spreadsheet to FORTRAN code?

[Daniel_T_]

I am in the crunch-mode of my thesis, and simulations in a program (that reads from an Excel file) are taking too long. A solution to this is to re-create the Excel code in FORTRAN code.

It is very basic, has 5 inputs and 4 outputs. If anyone is able to do this I will post the spreadsheet in here and I would even be willing to part with a small fee if anyone could. 

I am desperate. 

Thankyou,

Daniel. 

[IanH]

I am not aware of any automated tool, and I would not expect one to exist.

Many people can obviously do the transformation manually, but the degree of difficulty of that depends far more on the body of the spreadsheet code rather than its interface.

[Daniel_T_]

409056

Thanks Ian, I posted here because I was struggling to find anywhere on the internet to find such a person. I have attached the spreadsheet here. It basically takes information from 5 inputs, and calculates respective output based on conditional statements. For reference it is basically a controller mechanism. Again, any help would be appreciated finding someone who would be capable.  

409056


 

[Daniel_T_]

409056

Thanks Ian, I posted here because I was struggling to find anywhere on the internet to find such a person. I have attached the spreadsheet here. It basically takes information from 5 inputs, and calculates respective output based on conditional statements. For reference it is basically a controller mechanism. Again, any help would be appreciated finding someone who would be capable.  

409056


 

[Anthony_Richards]

There is no executable VB code in your module.

[Daniel_T_]

Hi Anthony, yes as I stated, it is just some simple coniditional statements in the output cells. Was not complex enough to usa VBA code, until now I did not realise that fortran code would be the quickest way. Still getting my head around fortran coding language and how to use it, hence any help would be appreciated.

Thanks again

Daniel

[IanH]

I looked at the spreadsheet and was not able to understand how it is "run".  Are you manually changing input cells and copy-pasting results to an output sheet?  Is it this, or some similar manual process that is taking time? 

Is there a separate program or thing, not stored in the spreadsheet, that is reading from the Excel sheet?

(These are not questions about the calculation that you are doing - they are questions about "how".)

[Anthony_Richards]

Your comment on the Table sheet says Cell E33 (labelled _Out2) is"Its content is set to VLOOKUP(Inp1,c13:d22,2)".
There appear to be no Function calls in cells in either Table  or Sheet1 worksheets , only straightforward arithmetic (numeric or logical) involving contents of cells which have numbers in them.

Perhaps, if the function VLOOKUP is a major component of your simulation, then if you supply (some of) the VB code, or describe what it is intended to do with its 3 arguments, posters here might be able to offer suggestions as to how you might be able to transform the code into a Fortran function in a Fortran DLL that could then be called by a wrapper function from Excel. Details on how to do this are easily available. If you know which part of your code consumes most computational time, then that would be the best place to start considering putting in the effort to develop and substitute faster code.

[JVanB]

VLOOKUP is intrinsic to Excel.

[dboggs]

I'm a bit surprised that some of the users of this forum appear to be unfamiliar with Excel. It is obvious to me that there is no VBA in your "program", and no explanation for VLOOKUP. Your file is "live" just as all elementary Excel files--type in a new value somewhere, and everything else adjusts. No explicit code needed, in fact that's the reason "spreadsheet" programs (VisiCalc followed by Lotus 123 and Quattro) were developed in the first place.

I would love to convert your Excel file to Fortran. But the functions entered in some of the cells are very long and unwieldy, so I fear that a bug-free conversion would take more time than I can spend (if it were my JOB or a funded project I would eagerly do it). But you should know that if it had been Fortran from the start--or any one of several other languages--it would probably have been quite simple.

Excel is a wonderful tool for certain applications but you are bumping up against its limitations in others--both in speed, and clarity of code. 

[mecej4]

Here is one big difference between the Fortran (and Java, C, Basic, etc.) way of doing things and the Excel way of doing things.

In Excel, you always see the contents of memory arranged into cells that are addressed by latitude and longitude on a flat planet. When you click on one of those cells, you can see the source code that may have produced the memory contents. The syntax used to address cells may appear natural to accountants, and bizarre to scientists. The sequence of operations that produced the final values in the cells is mysterious. You specify the problem but not the algorithm, and you have little or no control over the procedure.

In Fortran, you see as much of the source code as you wish to see, or may be content with knowing just name of the file. The algorithm for solving the problem has to be provided, either in source form or in a pre-compiled library such as MKL or IMSL. The program has a logical structure and, when compiled and run, will print out just those results that are asked for. 

I think of Excel as a write-only programming language. I need it as much as I need ebola.

[jimdempseyatthecove]

Someone, a long, long, time ago, may have written a VisiCalc implementation in Fortran. If you find that with a web search, you might be able to re-work that for some limited form of Excel simulation. (Someone may also have an Excel emulator written in Fortran).

In your specific case, that is the wrong way to get what you want. The best way, IMHO, would be to create a list of Fortran variable names and/or function names, that includes the RowColumn numbers of the original spread sheet, or in the case of named cells, the cell name.

real :: rA23 ! was real variable at A23

integer :: iQ45 ! was integer variable at Q45

real function fJ11() ! was Excel expression at J11 returning rea

! insert Excel expression here

The body of the function would then use variables expressed above. You only have 10 very small functions to write.

real function rf_StoreOut(()
  implicit none
  ! =IF(_Inp1>0,_Inp4+_Inp1,_Inp4+(_Out1/$O$7))
  IF(_Inp1 > 0) THEN
    rf_StoreOut = _Inp4+_Inp1
  ELSE
    rf_StoreOut = _Inp4 + rf_Out1() / rO7
  ENDIF
END function rf_StoreOut(()

Jim Dempsey

[dboggs]

I like the description "write-only." It's generally pretty easy to write, but give it another person to analyze the underlying logic and he may go crazy--or more likely just give up. It's very unreadable. But regarding the "need" for Excel, I disagree; it's more useful than Ebola. I describe it as a class that is "a very convenient and powerful tool for the rapid development of disposable software." Matlab (the biggest competitor to Fortran at least in my world) is also a member of that class but just a couple of steps above Excel. btw Many Excel users are ignorant of the built-in tools that show cell dependencies in a nice graphical manner: it will draw arrows from a given cell to all cells that depend on it; or draw arrows to it from every cell that it depends on. The geographical pattern of these arrows alone may be the best debugging tool in Excel. It's the first tool I reach for when trying to figure out someone else's spreadsheet--or my own spreadsheet from more than a year or so in the past.

[mecej4]

Daniel T: (speaking for just myself) if you can give a mathematical description of what your spreadsheet is supposed to do, I can probably point you towards a solution in Fortran (possibly using MKL and IMSL libraries). This approach may be better than trying to extract things from Excel that are not meant to be extracted.

[Anthony_Richards]

You say " simulations in a program (that reads from an Excel file) are taking too long". But you only supply an Excel worksheet that updates automatically when you change an input value in one of the cells and no other 'program'. As far as can be seen, there is no 'program that reads from an Excel file', other than Excel itself, which processes the contents of cells and the simple formulae that are dependant on the contenmts of other cells. When you say it 'takes too long' you appear to be saying that when you change one of the input cell values, the output cells take how long to update? Since it appears you only get a different output after you change oneof the input cell values, the time-limiting factor would appear to be governed by a) what you do with the contents of the worksheet after it has been updated, and b) how long it takes you to type in a new set of input values, both of which steps would appear to be limited by human factors rather than computational ones. Changing to Fortran can hardly affect those.

[FortranFan]

mecej4 wrote:

...

I think of Excel as a write-only programming language. I need it as much as I need ebola.

Not a fair characterization by any means - Excel is a spreadsheet package and as such, it is a productivity tool and it is a pretty good one at that.

[John_Campbell]

I agree with FortranFan, as I think many are misrepresenting what Excel and Fortran can do.

The spreadsheet Controllerv5.xls, as provided (or that I have downloaded) poorly explains what is being reported. It looks to have been hastily assembled.

Sheet "Table" provides some indication of input data, although much appears to be missing.

"Sheet1" appears to list some results obtained from another source, together with some validation calculations.

What is good about Excel is demonstrated in the chart in Sheet1, which can be used to show the results in a flexible way. Try doing that in Fortran.

Without a clearer indication of the input data and the output goals, we are left to discuss the merits of Excel vs Fortran.

Most of the simulation modelling I perform uses both, where Fortran does the long and complex calculations, then Excel takes the structured results and presents charts and tables to highlight the key outcomes. While I would consider myself an "advanced" Excel user, there are a huge number of capabilities that I have never ever used. I stick to structured tables, enhanced by vlookup then am amazed what I can report with a suitable pivot table. This provides the ability to interactively identify relationships in results that I did not previously understand, greatly enhancing the results that a Fortran simulation can provide.

John

[Daniel_T_]

jimdempseyatthecove wrote:

Someone, a long, long, time ago, may have written a VisiCalc implementation in Fortran. If you find that with a web search, you might be able to re-work that for some limited form of Excel simulation. (Someone may also have an Excel emulator written in Fortran).

In your specific case, that is the wrong way to get what you want. The best way, IMHO, would be to create a list of Fortran variable names and/or function names, that includes the RowColumn numbers of the original spread sheet, or in the case of named cells, the cell name.

real :: rA23 ! was real variable at A23

integer :: iQ45 ! was integer variable at Q45

real function fJ11() ! was Excel expression at J11 returning rea

! insert Excel expression here

The body of the function would then use variables expressed above. You only have 10 very small functions to write.

real function rf_StoreOut(()
  implicit none
  ! =IF(_Inp1>0,_Inp4+_Inp1,_Inp4+(_Out1/$O$7))
  IF(_Inp1 > 0) THEN
    rf_StoreOut = _Inp4+_Inp1
  ELSE
    rf_StoreOut = _Inp4 + rf_Out1() / rO7
  ENDIF
END function rf_StoreOut(()

Jim Dempsey

Thanks Jim! I managed to implement what I required using this exact form of code. You are a life saver!!

Thanks to everyone else for the comments as well. Will recommend this forum to anyone in the future!

Cheers :)