|
 Dates dont appear at my financial chart |
| Posted by Thiago Almeida Franco on Aug-27-2013 20:58 |
|
Hi, i am using vba in excel and the dates don't appear in my financial chart, the Xaxis is
blank. i am retrieving the data from a sheet and the format used is "dd/mm/yyyy". When i
debug the code i see that dates come between brackets, like "#21/01/2011#.
Why does this happen?
Thank you in advance.
My code is this:
Private Sub CommandButton1_Click()
Dim CodA?ao As String, i As Integer
CodA?ao = UserForm2.TextBox1.Text
i = 1
While Sheets("A??es").Cells(i, 1) <> CodA?ao And i < 500
i = i + 1
Wend
If i > 500 Then
Exit Sub
End If
Set x = UserForm2.Image1.Object
x.Picture = simplebar(248, 497, i)
End Sub
Function simplebar(LinhaInicio As Long, LinhaFim As Long, NumColuna As Integer)
Dim CodA?ao As String, DataInicio As String, DataFim As String
Const TamVetor = 250
Set cd = CreateObject("ChartDirector.API")
CodA?ao = Sheets("A??es").Cells(NumColuna, 1)
DataInicio = Workbooks("Cota??es").Sheets("Fechamento").Cells(LinhaInicio, 1)
DataFim = Workbooks("Cota??es").Sheets("Fechamento").Cells(LinhaFim, 1)
Dim noOfDays As Long
noOfDays = 50
Dim extraDays As Long
extraDays = 50
Dim timeStamps(TamVetor) As Date
Dim highData(TamVetor)
Dim lowData(TamVetor)
Dim openData(TamVetor)
Dim closeData(TamVetor)
Dim volData(TamVetor)
Dim adxData(TamVetor)
Dim dmiposData(TamVetor)
Dim dminegData(TamVetor)
'Retrieving data from the sheets
For i = extraDays To TamVetor + extraDays
timeStamps(i - extraDays) =
Workbooks("Cota??es.xlsm").Sheets("Abertura").Cells(i, 1)
highData(i - extraDays) = Workbooks("Cota??es.xlsm").Sheets("M?ximo").Cells(i,
NumColuna)
lowData(i - extraDays) = Workbooks("Cota??es.xlsm").Sheets("M?nimo").Cells(i,
NumColuna)
openData(i - extraDays) = Workbooks("Cota??es.xlsm").Sheets("Abertura").Cells(i,
NumColuna)
closeData(i - extraDays) =
Workbooks("Cota??es.xlsm").Sheets("Fechamento").Cells(i, NumColuna)
volData(i - extraDays) = Workbooks("Cota??es.xlsm").Sheets("Volume").Cells(i,
NumColuna)
adxData(i - extraDays) = Workbooks("ADX.xlsm").Sheets("ADX").Cells(i, NumColuna)
dmiposData(i - extraDays) = Workbooks("ADX.xlsm").Sheets("DMI+").Cells(i,
NumColuna)
dminegData(i - extraDays) = Workbooks("ADX.xlsm").Sheets("DMI-").Cells(i,
NumColuna)
Next i
'Dim c As FinanceChart
Set c = cd.FinanceChart(1.3 * TamX)
'set XYChart c = f.addIndicator(200)
Set f = c.addIndicator(TamX / 6)
'Set f = c.addIndicator(200)
' Add a title to the chart
Call c.addTitle2(8, CodA?ao)
Call c.addTitle2(9, DataInicio & " at? " & DataFim)
' Set the data into the finance chart object
Call c.setData(timeStamps, highData, lowData, openData, closeData, volData, 0)
'Call c.xAxis().setLabels(labels)
' Add the main chart with 240 pixels in height
Call c.addMainChart(TamX / 3)
Call c.addCandleStick(&H8000, &HCC0000)
Call c.addVolBars(150, &H99FF99, &HFF9999, &H808080)
Call c.addDonchianChannel(20, &H9999FF, &HC06666FF)
'Call c.addADX(120, 14, &H8000, &H800000, &H80)
'ADX
Call c.addLineIndicator2(f, adxData(), &H80, "ADX").setLineWidth(3)
Call c.addLineIndicator2(f, dmiposData(), &H800, "DMI+")
Call c.addLineIndicator2(f, dminegData(), &H800000, "DMI-")
'ADX******************
' Add a vertical brown (0x995500) mark line at x = 18
Dim xMark1
Set xMark1 = c.xAxis().addMark(20, &H995500, "Backup Start")
Erase timeStamps
Erase highData
Erase lowData
Erase openData
Erase closeData
Erase volData
Erase adxData
Erase dmiposData
Erase dminegData
Set simplebar = c.makePicture()
End Function |
| Re: Dates dont appear at my financial chart |
| Posted by Peter Kwan on Aug-28-2013 00:57 |
|
Hi Thiago,
In VBA syntax, date literals should be enclosed with the hash character #, just like a string literal should be enclosed with the double quote character ". This character is not related to ChartDirector but is the representation used by your VBA system. You may refer to Microsoft documentation for details:
http://msdn.microsoft.com/en-us/library/aa227559(v=vs.60).aspx
If the x-axis is empty, would you mind to double check if your timeStamps array contains valid date/time in all elements?
In your case, you should have 251 date/time. (Note that the code "Dim highData(TamVetor)" will declare an array with (TamVetor + 1) elements.) Would you mind to verify all 251 elements contain valid date/time? The date/time should be sorted in chronological order, with the most recent date being the last element. If the dates are not sorted, or the last element is not valid (because your data only have 250 dates), it could cause the x-axis to become empty.
Regards
Peter Kwan |
| Re: Dates dont appear at my financial chart |
| Posted by Thiago Almeida Franco on Aug-28-2013 02:59 |
|
Hi Peter , thanks for the answer.
I found out what was the problem in my sheets.
Here is the problem:
'ADX
'Error is here
'Set f = c.addIndicator(200)
'Call c.addlineindicator2(f, adxData, &H80, "ADX").setLineWidth(3)
'Call c.addlineindicator2(f, dmiposData, &H800, "DMI+")
'Call c.addlineindicator2(f, dminegData, &H800000, "DMI-")
When i comment this part of the code, the chart is correct(with all the dates). This part is
the ADX data from my sheets ( i am using this because the values i calculate differ from the
values addadx returns). For some reason, these vectors aren't linking with open, close,
etc... I think it?s because i didn't put dates on it. Any idea how i can solve this?
I send my example attached.
Thank you.
|
| Re: Dates dont appear at my financial chart |
| Posted by Thiago Almeida Franco on Aug-28-2013 03:08 |
|
I solved the problem, it happened because of setlinewidth, i changed my code for:
Set f = c.addIndicator(200)
Set layer1 = c.addlineindicator2(f, adxData, &H80, "ADX")
Call layer1.setLineWidth(3)
Call c.addlineindicator2(f, dmiposData, &H800, "DMI+")
Call c.addlineindicator2(f, dminegData, &H800000, "DMI-")
and it worked fine!
Thank you very much! |
|
Forum Home
Search
Message List
Post Message
(Thiago Almeida Franco)