Is My Economic Model too Difficult for Excel to Handle?

jerH

Board Regular
Joined
Dec 3, 2008
Messages
168
Sorry I've been unable to spend much time looking at the spreadsheet you sent me. I'd echo the above comments about VBA's capabilities. I encounter a lot of people who underestimate the scope of problems that recent versions of Excel can handle.

In some of the MatLab work I've done for my dissertation, I use the fprintf command to send my output to a text file which I then import to Excel for easy manipulation. I assume Gnu Octave would have a similar if not identical command.
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Ed Song

Board Regular
Joined
Sep 1, 2014
Messages
90
Thanks mrmickle1. I've finished the first time period of 40 (I might reduce it to 30), and the Gnu Octave program functioned well. However, it took me more than an hour to transcribe the data into my excel file, and as the data list gets longer, its only going to get harder to transcribe the data. One big advantage Gnu Octave has over Excel is that its easier to spot the bugs and mistakes. Its hard looking into an excel cell and figure out what is what. My final program in excel was just a mess. I discovered many mistakes by writing the program on gnu octave. Programming for the second period will be somewhat more difficult than the first period, because now I'll have to account for depreciation and build up of capital and human capital stock. However, once the second period is finished, the second period program can serve as a template for the remaining periods.

In my test run for just the first period, I instructed the program to do up to 100 iterations until one of the aggregate variables changes by less than .1% between successive approximations. In the test run, it took three iterations for convergence to occur. However, I'm worried that convergence will be a lot tougher to get once stock quantities begin to accumulate. During the iteration, my first guess at the value of the variable is done by ignoring certain effects that make the calculation complicated. Because in the early periods, there is little accumulation of stock variables, the initial guess is quite good. Once we get in the later period, a guess too far from the actual value may not result in convergence. I suppose, I could learn how to do more complicated iteration processes.

Anyway, what downloads do I need to use VBA on Windows 8 and Office 2010? Is there a user manual? I've just spent two weeks learning Gnu Octave, do you think its worth it to change programs again? How easy is it to learn VBA? I can see myself doing 40+ hours of work just to transcribe the gnu octave data output into excel for a given test run. Thus, just conducting three or four runs with different parameters could take weeks. In a similar but simpler program in excel, I was able to do 20+ runs per day.
 
Last edited:

JackBean

Active Member
Joined
Nov 1, 2007
Messages
403
Your project and last post raise several issues.
I am not familiar with Gnu Octave. And why so long to get the output transferred to Excel. Are you using clipboard to transfer? Once in Excel VBA could again be helpful in reformatting the data.

There is a recent post of a list of Excel resources by hiker95. Hang around this forum and you will soon find helpful info like this.
http://www.mrexcel.com/forum/excel-...anging-columns-rows-advanced.html#post3925232

The list is long. If you prefer book over video, one book like Walkenbach's power programming with Excel book may be enough.
Excel comes with two help systems, one on the sheet, and a different one from the code window.

Convergence may not always happen, or could different starting numbers result in converging to different result? You may have to test to find out.

What exactly are stock quantities?

How easy is it to learn VBA depends. VBA is similar to some other programming. It has standard variable types, many functions corresponding to the worksheet functions, simple and array variables, loops. If you are already familiar with these concepts it would go much faster learning to transform cell type formulas into code. Some people revel in getting Cell Formulas to work. But this approach is not always practical.

There are various debugging techniques for VBA code explained in the references. Some may use one simple technique, while others may use a variety of techniques. Debugging Cell Formulas or VBA code involves first spotting a problem, then figuring out what is wrong, and how to fix it. It should get easier and faster with practice.

With such a project with no answer key how do you test it? Organization factors such as compartmentalization into modular parts factor into design. Start simple and add functionality. Could a cell formula and code version be built and compared, or an Excel and Gnu Octave version? With complex climate modeling, different models can be compared. What value is the result unless there is confidence it works as planned and how is that demonstrated? Similarly a math problem can often be solved by two different methods to show the same answer. So if you ask if it is worth it to learn another method these issues come to mind. Without some kind of testing or verification or validation you may just be building a house of cards.

Other books, not specifically on Excel, may also give you ideas on approaching complex projects.
The pragmatic programmer by Andrew Hunt published 1999.
This book discusses issues such as building a prototype, and adding complexity.
 

JackBean

Active Member
Joined
Nov 1, 2007
Messages
403
I meant to address this in your other post #9 also.

"But I've heard that there are limitations on the amount of code <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); font-variant: normal; cursor: help; color: rgb(51, 51, 51); font-family: Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif; font-size: 12.7272720336914px; font-style: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: left; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(250, 250, 250);">VBA</acronym><acronym> </acronym>can handle."

Macros do have a size limitation. Each module can have over 1000 lines of code. If the limit is exceeded the subs can be put in different modules.

Here is a link:
Maximum Length Limit for a Macro (Microsoft Excel)
 

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,453
Edsong,

You have been referring to vectors and scalars. I am not too familiar with these, but here is a website that may prove of help with scalars:

VBA Internals: Getting Pointers | Byte Comb


This website discusses e :

MS Excel: EXP Function (WS, VBA)

As I mentioned before I will be glad to assist you in your project. I thought this may give you a little bit of VBA background on some of the subject matter your project encompasses.

Jackbean,

You bring up some good points. Early as I was learning VBA I recorded a few macros that exceeded the maximum limit. The funny thing is that I never knew what it was.... I just knew I had surpassed it. Thanks for the information!
 

Watch MrExcel Video

Forum statistics

Threads
1,095,814
Messages
5,446,654
Members
405,413
Latest member
AlainCar

This Week's Hot Topics

Top