excel-stock-quotes does not work for some stocks -- ALGN and TFM

joseph168 · Aug 7, 2012

Dear Support team,

Thanks very much for the website http://chandoo.org/wp/2010/06/02/excel-stock-quotes/

It works great for most of the stocks, but I have found a problem with these two stocks, ALGN and TFM. The last Trade Price was blank.

I will be very appreciate for your suggestion!

Regards,

Joseph

Luke M · Aug 7, 2012

My guess is that it's part of the "comma in a name" bug that Daniel mentions here:

http://chandoo.org/wp/2010/06/02/excel-stock-quotes/#comment-109733

The updated file catches most of the problems, but it appears that there are others. Looking in the code, I'd recomend changing the PostProcessActiveRange macro to this

[pre]

Code:

Private Sub PostProcessActiveRange()

Dim vArr As Variant
Dim i As Long

If rnAR_Dest.Columns.Count &#62; 2 Then
Application.DisplayAlerts = False

&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;
&#39; This code deals with the comma bug in the Name of a
&#39; company, if it is returned before a numeric value.
&#39; In this case, YF does not enclose the name of
&#39; a company in quotation marks. As a result a comma
&#39; in the name of a corporation (, Inc.) will be treated
&#39; as a field separator by the TextToColumns procedure.
&#39; This code removes such commas. It is unknown what
&#39; impact this may cause on other attributes, but most
&#39; attributes are numeric and so unaffected.
vArr = rnAR_Table.Resize(rnAR_Dest.Rows.Count - 1, 1)
For i = LBound(vArr) To UBound(vArr)
vArr(i, 1) = Replace(vArr(i, 1), &#34;, &#34;, &#34; &#34;)

&#39;The following line was added by Luke M to handle blank columns
vArr(i, 1) = Replace(vArr(i, 1), &#34;,,&#34;, &#34;,&#34;)
Next i
Application.EnableEvents = False
rnAR_Table.Resize(rnAR_Dest.Rows.Count - 1) = vArr
Application.EnableEvents = True
&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;

rnAR_Table.Resize(rnAR_Dest.Rows.Count).TextToColumns Destination:=rnAR_Table, DataType:=xlDelimited, Comma:=True
Application.DisplayAlerts = True
Worksheets(stAR_ConfigSheetName).[ar_LocalTimeLastUpdate] = Now
End If

End Sub

[/pre]

joseph168 · Aug 8, 2012

Hi Luke,

It works!! Thank you very much!

Regards,

Joseph

excel-stock-quotes does not work for some stocks -- ALGN and TFM

joseph168

New Member

Luke M

Excel Ninja

joseph168

New Member