MrExcel Publishing
Your One Stop for Excel Tips & Solutions

C'mon you Excel gurus, help me please!


Posted by Frank Krumm on November 15, 2001 8:57 AM

My previous message goes unanswered. I know there is a genius out there who has the answer.


Posted by Bob Umlas on November 15, 2001 9:07 AM

So... what's the question?
Making us search the Message Board for your name is not really nice! Besides, I couldn't find it!

Posted by lenze on November 15, 2001 9:12 AM

Have you tried using MS Query. You can create a query and set the link of the parameter to the cell where you enter the stock symbol. You may have to install the MS Query add-in and create the data source and locate the correct driver, but it should certainly be possible. By the way, if you aren't locked to that particular source, some of the ready to use Web queries available from MS give excellent stock information. Email me if you need some help getting started.

Posted by Rick E on November 15, 2001 10:56 AM

I'll write that macro for you, I need the whole formula first.

Answer this with the formula and I will put the symbol into it based on the "high lighted" (active) cell.

Posted by Frank Krumm on November 15, 2001 7:59 PM

Re: I'll write that macro for you, I need the whole formula first.

:Thanks and so sorry for not getting back much much sooner...

=Qlink|Bars!'SYMBOL,PERIOD,#BARS,DOHLC'

Using an external data source anything inside the single quote goes to the source which tries to read it as a stock symbol, etc. So if I put in A1 for example as a means to reference that cell's text string, I get #NAME error because the external source is trying to read A1 as the symbol. Thanks so much for your thoughts.

Posted by Frank Krumm on November 15, 2001 8:11 PM

:Thanks for the reply and sorry for the trouble. The formula is =Qlinks|Bars!'SYMBOL,PERIOD,#BARS,DOHLC'

I want to be able to enter a stock symbol into a designated cell and run the external reference formula. However, placing A1 for example in the place of SYMBOL returns a #NAME error because the external source is reading the A1 as a stock symbol.

At this point I can only change stock symbols by manually editing the formula.

Posted by Rick E on November 16, 2001 6:10 AM

Try This

Here is the marco to do what you want, it will "write" the formula somewhere ? with the stock symbol in it based of that symbol being the active cell.

Sub changeIt()
str1 = ActiveCell.Value
newFor = "=Qlink|Bars!'" & str1 & ",PERIOD,#BARS,DOHLC'"
' change A1 below to any cell you want to put the formula
Range("A1").Value = newFor ' <- change this line
End Sub

So put this in your workbook module, to get there hit 'Alt + F11' on your keyboard, right click on ThisWorkbook (on the upper, left side on the window) of the Project and select Insert, then select Module. Copy the code above to this module. Next go back to your sheet and select the symbol (click on that cell) you want put in the formula and then execute the macro, (Tools, macro, marcos..., select changeIt and then hit the run button.) It will put the formula in cell A1 unless you have changed that in the macro.

This should do it, good luck. Rick E

Posted by Frank Krumm on November 16, 2001 9:43 PM

Thank you very much Rick