VBA question

Quote from chs245:

Hi Vald

I had the same problem a while ago. I found the solution in the form of a VBA macro that does a fuzzy match between 2 strings. The code is listed on the mrexcel.com site, as part of the monthly challenge. Have a look at http://www.mrexcel.com/pc07.shtml and ammend the code to what you need.

Greetings,

OLiver
More...

OLiver,

I checked out that link, very interesting - where were you yesterday! Kidding :-) It turns out the macro I have now outputs identical results - it does exactly what that challenge was asking for. Whether it's as efficient as the one they have - I don't know yet, I'll check. Mine seems a bit longer code wise, I think...
Thank a lot though!
Vladimir.
 
A symbol lookup database isn't going to grow significantly, so Access or MySQL is probably fine. Personally, I'd use SQL Server or MSDE, but I already know those tools. It's up to you if you want to learn but Access/MySQL is easier and cheaper and you can always upgrade later.

Rather than program a bunch of complex searching methods based on messy data, just clean up the data. Dell vs. D E L L?? Fix the data as it's imported. Makes searching much easier and faster and simplifies your code.
 
Quote from FireWalker:


Rather than program a bunch of complex searching methods based on messy data, just clean up the data. Dell vs. D E L L?? Fix the data as it's imported. Makes searching much easier and faster and simplifies your code.
More...

Not sure about that. If this was a one time deal, yes, you are undeniably right. But this is something that will be done over and over. The way I have it now is rather flexible, it will find the best three matches and if what you were looking for was there, odds are very high it'll be one of them. the number of various way you would have to "clean" the data is uncountable and doing it every time would be time consuming. Also, having this program means you can be less precise entering the name - a couple of typos won't matter usually.
Cheers,
V.
 
Well, I was thinking a program would be performing the import and clean it every time data was updated or added. The program would be updated with specific rules as time went on. Then add soundex for fuzzier searches. Clean data is critical as you grow into unanticipated needs.

That's a programmer's perspective though. Your approach will work, but I wouldn't hire you as my DBA. :D
 
nononsense, you couldn't be more mistaken about Excel and VBA if you tried. Go to Borders and read a few of the books on it.
 
Actually he's not mistaken (and no I don't need to go to Borders to get a book - I've been working with all the aforementioned software for a VERY long time).

I believe his point was that while you can make Excel do a lot of things, it's fundamentally a spreadsheet - not a database, not a programming environment, etc.

Can you drive nails with a pair of pliers? Sure. You could put in screws with a hammer too. It's not nearly as efficient or easy, and the results wouldn't be as good as using a more appropriate tool, but you COULD do it.

If you were so inclined, you could VBA program Word to do this company name lookup exercise too. You could even Javascript or VBscript it for local execution in a browser.

The overall point wasn't whether you CAN make Excel do a whole spectrum of non-spreadsheety things - it was whether it made sense to always try to force every problem into an Excel-based solution.

For one off hacks - who really cares. But for something that'd be used repeatedly over a long period - at least a little extra consideration is due.

Many things that I've seen "Excel gurus" hack around and kludge Excel into doing (for a recurring application) are in fact quite trivial to have done in Access or straight VB. I've certainly used Excel to do a bunch of non-spreadsheet things for quicky one-time needs, but it's not the first or only tool that comes to mind to solve every problem - although admittedly I have more than one tool in my toolbox which isn't true of everyone.

And if a person's not (or is only marginally) familiar with an alternate tool (e.g., Access), they really don't have a clue what can actually be done quite easily with it.

To the guy who only knows how to use a hammer, every problem looks like a nail.
 
Well, if you consider using IB/Excel for arb monitoring, ES fair value calculation, audio - visual pace feedback, and assorted other goodies a 'one off hack' then I fully agree.

Who am I to argue with experience. My 25 years programming is nothing, so I wouldn't pretend to butt up against my betters :p
 
I agree with everyone on this debate.

Excel is the most over used application in the financial world. It takes a few turns for people with an IQ over a hundred to get the hang and start running with it. Visual Basic for Applications requires a little bit more patience and thus weeds out a fair share of those who tinker with Excel. A precious few people have actually delved into the world of VBA/Excel as far as I have, so I feel the right, and the need to step up and make a few points here.

First of all, Excel/VBA and Access make an excellent combination toolset for the serious VBA coder. VBA is not an object oriented program that you would use for enterprise systems, but it sure as hell can get you very far and is quite power and succinct. The multitude of object libraries which Microsoft allows VBA to run really bring Excel into the Big league through its back end. I have created Applications which have seamlessly interfaced with Access, Oracle, the internet, other Office products, XML, etc.

What I have seen is a lot of prejudice from the OOP world against Excel, usually because of the feeling that it threatens there livelihood more so than anything else. Typically ignorance runs wild in these debates - the pros feel they know it all but they really never have looked at Excel for what it really can do, and that is give the user an environment where he/she feels they have control and familiarity but in the right hands can also really rock in the background.

My $.02 - and that folks, is a bargain compared to what companies pay to have Excel on the desktops of the analysts who work for them.

PEACE

:cool:
 
Quote from dbTrader:

I agree with everyone on this debate.

Excel is the most over used application in the financial world. It takes a few turns for people with an IQ over a hundred to get the hang and start running with it. Visual Basic for Applications requires a little bit more patience and thus weeds out a fair share of those who tinker with Excel. A precious few people have actually delved into the world of VBA/Excel as far as I have, so I feel the right, and the need to step up and make a few points here.

First of all, Excel/VBA and Access make an excellent combination toolset for the serious VBA coder. VBA is not an object oriented program that you would use for enterprise systems, but it sure as hell can get you very far and is quite power and succinct. The multitude of object libraries which Microsoft allows VBA to run really bring Excel into the Big league through its back end. I have created Applications which have seamlessly interfaced with Access, Oracle, the internet, other Office products, XML, etc.

What I have seen is a lot of prejudice from the OOP world against Excel, usually because of the feeling that it threatens there livelihood more so than anything else. Typically ignorance runs wild in these debates - the pros feel they know it all but they really never have looked at Excel for what it really can do, and that is give the user an environment where he/she feels they have control and familiarity but in the right hands can also really rock in the background.

My $.02 - and that folks, is a bargain compared to what companies pay to have Excel on the desktops of the analysts who work for them.

PEACE

:cool:
More...

Hi dbTrader,

I'm all with you. If I may say, I don't think Excel / VBA needed any defense, it hadn't been attacked. I have used VB a lot and still do (as I didn't liberate myself 100% from M$'s stranglehold :D ).

I think the point that was not well understood by all is that if you become serious about "backtesting" and so, out of necessity you will be dealing with a lot of data. This is where "torturing yourself" has been correctly used by some, describing the fate of the ignorant-about-db guys.

You dbTrader don't belong in this category as you state: "Excel/VBA and Access make an excellent combination toolset for the serious VBA coder". A couple of people didn't think a db package like Access to be of great use.

Of course you could become a lot more ambitious, but this was not the point in this thread.

Be good,

nononsense
 
You must log in or register to reply here.
Back
Top