VBA question

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
 
Hi All,

Archangel and CalTrader did a great job in their posts. As the thread keeps going, I thought to add or confirm something that he already said.

I have seen so many cases over the last 10 years of overuse of Excel. Excel of course is a terrific tool. People get started with it and pick up skills. If you get more ambitious, many people can't get rid of it anymore. Like Archangel said, they keep torturing themselves to get things done that would be child's play in other environments.

As mentioned, Access is a great tool. You have to learn a bit to get started, like you had to with Excel, but a whole new world will open for you. If you really needed Access, you will very quickly leave Excel for what it's good at.

One more thing. I myself thought VBA only really interesting for Word. It enables you to do many neat things in text processing. For Excel, I always had my doubts. If you need VBA there, it's time to look further.

Be good,

nononsense
 
Quote from nononsense:

I myself thought VBA only really interesting for Word.
More...

Well, I beg to differ. I have found VBA and Excel the single most important drivers in my trading profitability. They are excellent tools to develop strategies and evaluate data. Access is not really suited to develop customized trading strategies (incl order management) and Tradestation, Wealthlab simply are a joke to what you can achieve writing your own code (using VBA, VB or whatever else you chose).

OLiver
 
Quote from ArchAngel:

A match metric as you describe is OK, although probably not even as good a filter as a Soundex.

Example: While you'd match DELL to "D E L L Inc.", you'd also match it to suff like:

Hugo DarnELL, Inc.
stuDio EL Loco
aDriana Enterprises LLc
etc.

Good luck.
More...


You are quite right. But relative to the length of the string, the match would be closer for D E L L inc. Of course, there might be some other name that matches better, but D E L L Inc would likely be one of the top three hits. ... I hope :-)
Thanks a lot for your insights!
V.
 
Quote from nononsense:

Hi All,

Archangel and CalTrader did a great job in their posts. As the thread keeps going, I thought to add or confirm something that he already said.

I have seen so many cases over the last 10 years of overuse of Excel. Excel of course is a terrific tool. People get started with it and pick up skills. If you get more ambitious, many people can't get rid of it anymore. Like Archangel said, they keep torturing themselves to get things done that would be child's play in other environments.

As mentioned, Access is a great tool. You have to learn a bit to get started, like you had to with Excel, but a whole new world will open for you. If you really needed Access, you will very quickly leave Excel for what it's good at.

One more thing. I myself thought VBA only really interesting for Word. It enables you to do many neat things in text processing. For Excel, I always had my doubts. If you need VBA there, it's time to look further.

Be good,

nononsense
More...


I partially agree with you that it's not the best tool for many tasks. I also agree with chs245 that you are not giving it enough credit. Under the present circumstances, I'm writing a macro which would be simple enough for junior staff to use - hence, simper platform is better :D
Cheers,
V.
 
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...

Thanks OLiver! Will check it out in a sec. Sounds promising - I may end up using a combination of the techniques!
Vladimir.
 
Quote from chs245:

Well, I beg to differ. I have found VBA and Excel the single most important drivers in my trading profitability. They are excellent tools to develop strategies and evaluate data. Access is not really suited to develop customized trading strategies (incl order management) and Tradestation, Wealthlab simply are a joke to what you can achieve writing your own code (using VBA, VB or whatever else you chose).

OLiver
More...

I don't really want to argue with you about this. You are certainly right to love the tool that helped you grow rich. However, name me one thing that your VBA/Excel combination can do better than the Acces programming environment (Is also VBA in fact).

You certainly cannot have been very seriously engaged in "developing strategies" based on "evaluating data". The torturing that Excel virtuosi impose on themselves by ignoring and refusing to learn about elementary database support is deplorable.

I will not convince you with this, however I felt obliged to correct your view which goes against reason. I simply tried my best to help.

May you continue on your beloved path of masochism. :D
 
Quote from nononsense:


May you continue on your beloved path of machosism. :D
More...

...with pleasure :p

I do not know Access well enough, but since it supports VBA, it must be able to do similar things as Excel.
For what I'm doing, Excel is the best solution in terms of development time versus trading reward. In the end, I'm a trader not a hard core programmer, although I do enjoy these challenges such as the one described by vlad.

OLiver
 
Quote from chs245:

Well, I beg to differ. I have found VBA and Excel the single most important drivers in my trading profitability. They are excellent tools to develop strategies and evaluate data. Access is not really suited to develop customized trading strategies (incl order management) and Tradestation, Wealthlab simply are a joke to what you can achieve writing your own code (using VBA, VB or whatever else you chose).

OLiver
More...

Both sides in the debate are correct: A lot can be done - for not a lot of upfront money - using only word, excel and access.

However things like SQL server or oracle are more specialized and tailored for manipulating data and multidimensional data products like the MS OLAP services offer even more specialized services for handling data.

When to switch ? When you can calculate - with reasonable certainty - that you will incur a cost savings by doing so. If you cant calculate a cost savings in advance and your business is not paralized or put in jepardy of failure by the lack of an immediate solution then just stick with the solution that works or can be made to work well enough....
 
Quote from CalTrader:

Both sides in the debate are correct: A lot can be done - for not a lot of upfront money - using only word, excel and access.

However things like SQL server or oracle are more specialized and tailored for manipulating data and multidimensional data products like the MS OLAP services offer even more specialized services for handling data.

When to switch ? When you can calculate - with reasonable certainty - that you will incur a cost savings by doing so. If you cant calculate a cost savings in advance and your business is not paralized or put in jepardy of failure by the lack of an immediate solution then just stick with the solution that works or can be made to work well enough....
More...

Hi CalTrader,

I believe you raised the ante a bit, but that makes it more interesting. I thought that up till now we were talking about Excel/VBA versus a simple db like Access.

However, you are right that when you grow, you are likely to outgrow Access sometime. I run SQLserver for years now. I'm still using Access often because it meshes very nicely with SQLserver but it's amazing how easily I keep bumping in the 2Gb db size limit with Access.

FWIW, in my struggle for "freedom" you may have read about, I first set up a migration plan from SQLserver to PostgreSQL. As of late l am on MySQL which runs on both M$ and Linux. With the latest development announcments for MySQL and given its popularity this looks like the best for me. My "bread and butter" stuff still goes on SQLserver though. I'd love to hear about any comments.

Right now for a starter, skipping Access and moving straight to MySQL becomes more attractive as they seem to have much better support/management tools.

Be good,

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