1

I am currently working on an Excel spreadsheet capable of exporting data from the Yahoo Finance API for dynamic stock quote analysis. I am having problems properly parsing the values into my excel spreadsheet. The issue is that the last column of numeric values have a trailing space character, which prevents Excel from recognizing it as a number and formatting it in comma style.

Here is the function I use currently:

Function UpdateStockData(rawWebpageData As String)
    Dim stockQuotes As Variant
    Dim stockQuoteValues As Variant

    Dim i As Integer
    Dim j As Integer

    stockQuotes = Split(rawWebpageData, vbLf)

    For i = 0 To UBound(stockQuotes)
        If InStr(stockQuotes(i), ",") > 0 Then
            stockQuoteValues = Split(stockQuotes(i), ",")
            For j = 0 To UBound(stockQuoteValues)
                sheet.Cells(5 + i, 4 + j).Value = stockQuoteValues(j)
                sheet.Cells(5 + i, 4 + j).Value = Trim(sheet.Cells(5 + i, 4 + j).Value)
            Next j
        End If
    Next i
End Function

Here is some sample data:

43.99,44.375,41.97,42.62,30098498
573.37,577.11,568.01,573.64,1871694
16.03,16.14,15.93,16.17,25659400
128.54,129.56,128.32,129.36,31666340
126.32,126.68,125.68,126.27,1629499
105.57,106.00,104.78,106.35,4972937
82.58,83.21,82.20,83.37,6214421
27.89,27.9173,27.62,27.83,1003967
49.07,49.56,48.92,49.55,13870589
43.055,43.21,42.88,43.28,25748692
34.12,34.41,33.72,34.095,23005798
159.42,160.56,158.72,161.03,3633635
43.01,43.90,41.00,40.30,10075067
100.25,100.48,99.18,99.74,9179359
139.54,140.49,138.75,140.69,1311226
119.86,120.05,118.7828,120.20,2931459
42.50,42.98,42.47,42.95,16262994
78.02,78.99,77.66,78.99,1826464
89.87,91.35,89.86,91.02,1773576
15.84,15.98,15.76,15.99,78441600
69.50,70.2302,69.49,70.49,2343967
80.895,81.15,78.85,79.60,28126686
33.08,33.20,32.955,33.25,739726
83.08,83.80,82.34,83.16,4475302
64.72,64.90,64.27,64.27,5147320
35.64,41.85,35.40,40.78,15871339
83.08,83.80,82.34,83.16,4475302
22.93,23.099,22.71,23.10,5290225
18.47,19.00,18.30,18.98,71891
69.65,69.684,69.08,69.98,5992137
154.35,155.22,154.00,155.57,4476188
80.08,81.16,79.77,81.51,7731275
47.79,48.87,47.31,48.58,2219634
23.04,23.21,22.97,23.23,891504
114.76,115.47,114.25,116.07,3799034
80.63,81.56,80.56,81.91,6140957
25.66,25.77,25.47,25.86,31543764
87.18,87.96,86.93,87.62,13467554
58.31,58.795,57.61,58.255,5791024
174.62,175.78,174.41,176.15,1035588
84.35,85.24,84.21,85.16,7369986
42.03,42.25,41.69,41.98,3192667
34.19,34.49,34.01,34.57,15652895
101.65,102.12,101.17,102.34,8665474
7.88,8.01,7.84,7.88,10425638
62.13,62.17,61.3525,61.97,16626413
23.10,23.215,22.85,23.18,651929

The last value of each row of data above is where the problem occurs.

Community
  • 1
  • 1
CaptainObvious
  • 149
  • 1
  • 12
  • See http://stackoverflow.com/questions/9578397/how-to-remove-leading-or-trailing-spaces-in-an-entire-column-of-excel-worksheet/9582919#9582919 The issue with web data is often the `CHAR(160)` problem – brettdj Mar 05 '15 at 05:22
  • @brettdj, I tried it and it half worked for me. It now variably formats it correctly (there's no pattern to it, it can work 2 times or not work 3 times) – CaptainObvious Mar 05 '15 at 05:49
  • would be good to see your data. – brettdj Mar 05 '15 at 08:04

1 Answers1

1

Check the value of the last char on the last iteration it might be a return char. You can use the left function to take what you want or replace.

It would be easier to answer if we I can see the value of rawWebpageData variable.

Check the cell format, you can try to set it to numeric if it is text. If I was doing it I would debug the data and step through it to look for characters that i'm not checking.

Andrew
  • 11
  • 2