- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Link Copied
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
There is no executable VB code in your module.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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".)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
VLOOKUP is intrinsic to Excel.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 :)
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page