I have an Excel file with macros that opens a CSV file in a specified directory (the name of each csv file corresponds to stock symbol name), downloads historical stock price data into the CSV file, performs calculations of several indexes, and then creates a chart based on the calculation data. The horizontal axis of the chart represents dates, and the vertical axis corresponds to data ranging from 0 to 100.
The macros function correctly when the price data intervals are set to 1 day. However, if I change the price data interval to 15 minutes, the charts are generated inappropriately. This issue may arise because the price data is only available during trading hours, in my region from 14:30 to 21:00. Consequently, when the chart is built for two days with 15-minute intervals of price data, the chart line corresponding to the time period starting from 21:00 of the previous day until 14:30 of the next day appears as a straight line.
How can I modify the code so that when using 15-minute intervals for price data, the chart excludes data corresponding to the time period from 21:00 to 14:30?
I would greatly appreciate any assistance in modifying the code. Thank you in advance.
The macros function correctly when the price data intervals are set to 1 day. However, if I change the price data interval to 15 minutes, the charts are generated inappropriately. This issue may arise because the price data is only available during trading hours, in my region from 14:30 to 21:00. Consequently, when the chart is built for two days with 15-minute intervals of price data, the chart line corresponding to the time period starting from 21:00 of the previous day until 14:30 of the next day appears as a straight line.
How can I modify the code so that when using 15-minute intervals for price data, the chart excludes data corresponding to the time period from 21:00 to 14:30?
I would greatly appreciate any assistance in modifying the code. Thank you in advance.
VBA Code:
Dim StartTime As Double
Dim SecondsElapsed As Double
StartTime = Timer
Dim wb As Workbook, ws As Worksheet
Dim FSO As Object, fldr As Object, wbFile As Object
Dim destwb As Workbook
Set FSO = CreateObject("Scripting.FileSystemObject")
Set fldr = FSO.GetFolder("D:\VBA\SMA 3X4 Day WW\CSV\2")
Set destwb = Workbooks("PricePasteWW.xlsm")
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayStatusBar = True
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
On Error Resume Next
For Each wbFile In fldr.Files
If FSO.GetExtensionName(wbFile.Name) = "csv" Then
Set wb = Workbooks.Open(wbFile.Path)
wb.Worksheets(1).Range("A1", "X800").Clear
destwb.Worksheets("sheet1").Range("S5").Value = Left(wbFile.Name, InStr(wbFile.Name, ".") - 1)
destwb.Worksheets("sheet1").Activate
Call GetYahooDataFromJSON
Application.ScreenUpdating = False
With wb.Worksheets(1)
.Activate
Range("B1").PasteSpecial xlPasteAll
Range("B:B").NumberFormat = "mmm-dd-yyyy hh:mm"
ActiveSheet.Range("A2:A180").Value = Left(wbFile.Name, InStr(wbFile.Name, ".") - 1)
Range("I1").Value = "WWSMA2"
Columns("I:I").NumberFormat = "0.00$"
Range("I2:I180").FormulaR1C1 = "=(((R[1]C[0]*1)+RC[-3])/2)"
Range("I2").AutoFill Destination:=Range("I2:I180")
Range("J1").Value = "WWSMA3"
Columns("J:J").NumberFormat = "0.00$"
Range("J2:J180").FormulaR1C1 = "=(((R[1]C[0]*2)+RC[-4])/3)"
Range("J2").AutoFill Destination:=Range("J2:J180")
'Now lets establish K% period = 30
'and calculate Highest Maximum price
Range("K1").Value = "HighMAX20"
Range("K2").FormulaR1C1 = "=Max(RC[-7]:R[31]C[-7])"
Range("K2").AutoFill Destination:=Range("K2:K180")
'Now lets establish K% period = 30
'and calculate Lowest Minimum price
Range("L1").Value = "LowMin20"
Range("L2").FormulaR1C1 = "=Min(RC[-7]:R[31]C[-7])"
Range("L2").AutoFill Destination:=Range("L2:L180")
'Now lets calculate %K
Range("M1").Value = "%K"
Range("M2").NumberFormat = "0.00"
Range("M2").FormulaR1C1 = "=(((RC[-7] - RC[-1])/(RC[-2] - RC[-1]))*100)"
Range("M2").AutoFill Destination:=Range("M2:M180")
'Now lets calculate Single SMA15 of %K
Range("N1").Value = "Single SMA15 of %K"
Range("N2").FormulaR1C1 = "=AVERAGE(RC[-1]:R[14]C[-1])"
Range("N2").AutoFill Destination:=Range("N2:N180")
'Now lets calculate Double SMA15 of %D
Range("O1").Value = "Double SMA15 of %D"
Range("O2").FormulaR1C1 = "=AVERAGE(RC[-1]:R[14]C[-1])"
Range("O2").AutoFill Destination:=Range("O2:O180")
ActiveSheet.Columns.AutoFit
' End If
' Next ws
ActiveWorkbook.Save
'Now lets make a Graph
For Each ws In Sheets
If ws.Name <> "Control" And ws.Name <> "Response" Then
ws.Activate
Debug.Print ws.Name
' Range("B1:B180").Copy Range("R1")
' Range("B1:B180").Copy Range("V1")
' Range("V1:V180").NumberFormat = "mmm-dd-yyyy hh:mm"
Range("B1").Copy Range("R1")
Range("B2:B180").Copy Range("R2")
Range("R2:R180").NumberFormat = "mmm-dd-yyyy hh:mm"
Range("B1").Copy Range("V1")
Range("B2:B180").Copy Range("V2")
Range("V1").Value = "Date"
Range("V2:V180").NumberFormat = "mmm-dd-yyyy hh:mm"
Range("F1:F180").Copy Range("S1")
Range("I1:I180").Copy
Range("T1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("J1:J180").Copy
Range("U1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("N1:N180").Copy
Range("W1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("W:W").EntireColumn.AutoFit
Range("O1:O180").Copy
Range("X1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("X:X").EntireColumn.AutoFit
Range("S2:S180").NumberFormat = "$#,###.00"
' ActiveSheet.Shapes.AddChart2(289, xlLineMarkers).Select
' ActiveChart.SetSourceData Source:=ActiveSheet.Range("R1:U180")
'
'
' Dim RngToCover1 As Range
' Dim ChtOb1 As ChartObject
' Set RngToCover1 = ActiveSheet.Range("A3:O30")
' Set ChtOb1 = ActiveChart.Parent
'
' ChtOb1.Height = RngToCover1.Height ' resize
' ChtOb1.Width = RngToCover1.Width ' resize
' ChtOb1.Top = RngToCover1.Top ' reposition
' ChtOb1.Left = RngToCover1.Left ' reposition
'
' With ActiveChart.Parent
' .Left = 10
' .Top = 10
' End With
ActiveSheet.Shapes.AddChart2(289, xlLineMarkers).Select
' Set the major unit of the X-axis to 15 minutes
ActiveChart.Axes(xlCategory).MajorUnit = 15 / (24 * 60) ' 15 minutes in days
' Change the data range of the chart
' ActiveChart.SetSourceData Source:=Sheets("YourSheetName").Range("A1:B100") ' Adjust the range as per your data
ActiveChart.SetSourceData Source:=ActiveSheet.Range("V1:X180")
Dim RngToCover2 As Range
Dim ChtOb2 As ChartObject
Set RngToCover2 = ActiveSheet.Range("C3:X42")
Set ChtOb2 = ActiveChart.Parent
ChtOb2.Height = RngToCover2.Height ' resize
ChtOb2.Width = RngToCover2.Width ' resize
ChtOb2.Top = RngToCover2.Top ' reposition
ChtOb2.Left = RngToCover2.Left ' reposition
' With ActiveChart.Parent
' .Left = 0
' .Top = 30
' End With
With Charts("Chart2").SeriesCollection(1)
.HasDataLabels = True
.DataLabels.NumberFormat = "mmm-dd-yyyy hh:mm"
End With
' Add data labels to the data series
' ActiveChart.FullSeriesCollection(1).ApplyDataLabels
' Set the data label format to display the X value (date and time)
' ActiveChart.SeriesCollection(1).DataLabels.NumberFormat = "hh:mm" '"mm/dd/yy hh:mm"
Range("A1").Select
ActiveSheet.Columns.AutoFit
End If
Next 'ws
End With
' Save the opened CSV file as XLS file format
Dim csvFilePath As String
csvFilePath = wb.FullName
wb.SaveAs Replace(csvFilePath, ".csv", ".xls"), FileFormat:=xlExcel8
wb.Close SaveChanges:=False
End If
Next wbFile
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.CutCopyMode = False
Application.DisplayStatusBar = True
'Determine how many seconds code took to run
SecondsElapsed = Round(Timer - StartTime, 2)
destwb.Worksheets("Sheet1").Range("X13").Value = "Code 2 done in " & SecondsElapsed & " seconds"
ActiveSheet.Columns.AutoFit
ActiveWorkbook.Save
' 'Application.Workbooks("PricePasteWW.xlsm").Close True
Call TestPlayWavFile
'Application.Workbooks("PricePasteWW.xlsm").Close True
End Sub| Date | Single SMA15 of %K | Double SMA15 of %D |
Feb-08-2024 21:00 | 63.60 | 62.19 |
Feb-08-2024 20:45 | 62.40 | 62.14 |
Feb-08-2024 20:30 | 61.21 | 62.25 |
Feb-08-2024 20:15 | 60.98 | 62.48 |
Feb-08-2024 20:00 | 59.55 | 62.77 |
Feb-08-2024 19:45 | 60.07 | 63.17 |
Feb-08-2024 19:30 | 61.27 | 63.49 |
Feb-08-2024 19:15 | 61.92 | 63.68 |
Feb-08-2024 19:00 | 62.23 | 63.82 |
Feb-08-2024 18:45 | 62.06 | 63.98 |
Feb-08-2024 18:30 | 63.94 | 64.17 |
Feb-08-2024 18:15 | 64.59 | 64.10 |
Feb-08-2024 18:00 | 63.56 | 63.96 |
Feb-08-2024 17:45 | 62.93 | 64.08 |
Feb-08-2024 17:30 | 62.53 | 64.45 |
Feb-08-2024 17:15 | 62.82 | 65.08 |
Feb-08-2024 17:00 | 64.04 | 65.80 |
Feb-08-2024 16:45 | 64.77 | 66.56 |
Feb-08-2024 16:30 | 65.29 | 67.41 |
Feb-08-2024 16:15 | 65.49 | 68.39 |
Feb-08-2024 16:00 | 64.94 | 69.52 |
Feb-08-2024 15:45 | 64.13 | 70.82 |
Feb-08-2024 15:30 | 63.97 | 72.31 |
Feb-08-2024 15:15 | 64.59 | 73.79 |
Feb-08-2024 15:00 | 64.90 | 75.23 |
Feb-08-2024 14:45 | 62.98 | 76.76 |
Feb-08-2024 14:30 | 62.53 | 78.55 |
Feb-07-2024 20:45 | 65.31 | 80.46 |
Feb-07-2024 20:30 | 68.40 | 82.20 |
Feb-07-2024 20:15 | 71.98 | 83.73 |
Feb-07-2024 20:00 | 73.67 | 85.03 |
Feb-07-2024 19:45 | 75.39 | 86.26 |
Feb-07-2024 19:30 | 77.63 | 87.33 |
Feb-07-2024 19:15 | 79.95 | 88.25 |
Feb-07-2024 19:00 | 82.47 | 88.95 |
Feb-07-2024 18:45 | 84.45 | 89.35 |
Feb-07-2024 18:30 | 86.51 | 89.50 |
Feb-07-2024 18:15 | 86.06 | 89.40 |
Feb-07-2024 18:00 | 86.30 | 89.30 |
Feb-07-2024 17:45 | 87.82 | 89.09 |
Feb-07-2024 17:30 | 89.86 | 88.63 |
Feb-07-2024 17:15 | 91.11 | 87.89 |
Feb-07-2024 17:00 | 91.48 | 87.00 |
Feb-07-2024 16:45 | 91.36 | 86.03 |
Feb-07-2024 16:30 | 91.37 | 84.98 |
Feb-07-2024 16:15 | 92.09 | 83.82 |
Feb-07-2024 16:00 | 91.48 | 82.53 |
Feb-07-2024 15:45 | 91.39 | 81.21 |
Feb-07-2024 15:30 | 90.57 | 79.79 |
Feb-07-2024 15:15 | 88.42 | 78.36 |
Feb-07-2024 15:00 | 86.72 | 77.18 |
Feb-07-2024 14:45 | 85.01 | 75.96 |
Feb-07-2024 14:30 | 84.52 | 74.68 |
Feb-06-2024 20:45 | 83.22 | 73.38 |
Feb-06-2024 20:30 | 80.88 | 72.05 |
Feb-06-2024 20:15 | 78.73 | 70.84 |
Feb-06-2024 20:00 | 77.68 | 69.76 |
Feb-06-2024 19:45 | 76.98 | 68.65 |
Feb-06-2024 19:30 | 75.58 | 67.42 |
Feb-06-2024 19:15 | 74.04 | 66.18 |
Feb-06-2024 19:00 | 72.68 | 64.95 |
Feb-06-2024 18:45 | 71.77 | 63.69 |
Feb-06-2024 18:30 | 70.00 | 62.44 |
Feb-06-2024 18:15 | 69.21 | 61.23 |
Feb-06-2024 18:00 | 70.67 | 59.92 |
Feb-06-2024 17:45 | 68.40 | 58.28 |
Feb-06-2024 17:30 | 65.90 | 56.81 |
Feb-06-2024 17:15 | 64.96 | 55.52 |
Feb-06-2024 17:00 | 63.28 | 54.20 |
Feb-06-2024 16:45 | 62.70 | 52.89 |
Feb-06-2024 16:30 | 62.52 | 51.49 |
Feb-06-2024 16:15 | 61.01 | 49.92 |
Feb-06-2024 16:00 | 58.55 | 48.22 |
Feb-06-2024 15:45 | 56.97 | 46.54 |
Feb-06-2024 15:30 | 55.59 | 44.83 |
Feb-06-2024 15:15 | 53.83 | 43.17 |
Feb-06-2024 15:00 | 53.07 | 41.59 |
Feb-06-2024 14:45 | 51.72 | 40.18 |
Feb-06-2024 14:30 | 49.59 | 38.97 |
Feb-05-2024 20:45 | 46.05 | 38.09 |
Feb-05-2024 20:30 | 46.47 | 37.68 |
Feb-05-2024 20:15 | 46.47 | 37.41 |
Feb-05-2024 20:00 | 45.22 | 37.35 |
Feb-05-2024 19:45 | 43.55 | 37.61 |
Feb-05-2024 19:30 | 41.67 | 38.28 |
Feb-05-2024 19:15 | 38.97 | 39.22 |
Feb-05-2024 19:00 | 35.62 | 40.54 |
Feb-05-2024 18:45 | 33.25 | 42.33 |
Feb-05-2024 18:30 | 31.47 | 44.47 |
Feb-05-2024 18:15 | 30.57 | 46.87 |
Feb-05-2024 18:00 | 30.22 | 49.44 |
Feb-05-2024 17:45 | 31.81 | 52.15 |
Feb-05-2024 17:30 | 33.62 | 54.62 |
Feb-05-2024 17:15 | 36.42 | 56.84 |
Feb-05-2024 17:00 | 39.84 | 58.81 |
Feb-05-2024 16:45 | 42.47 | 60.44 |
Feb-05-2024 16:30 | 45.51 | 61.82 |
Feb-05-2024 16:15 | 49.16 | 62.81 |
Feb-05-2024 16:00 | 53.64 | 63.41 |
Feb-05-2024 15:45 | 55.76 | 63.50 |
Feb-05-2024 15:30 | 58.70 | 63.34 |
Feb-05-2024 15:15 | 62.51 | 62.85 |
Feb-05-2024 15:00 | 65.38 | 61.90 |
Feb-05-2024 14:45 | 67.47 | 60.71 |
Feb-05-2024 14:30 | 69.05 | 59.35 |
Feb-02-2024 20:45 | 70.96 | 57.82 |
Feb-02-2024 20:30 | 68.75 | 56.01 |
Feb-02-2024 20:15 | 67.04 | 54.31 |
Feb-02-2024 20:00 | 65.90 | 52.67 |
Feb-02-2024 19:45 | 64.37 | 50.97 |
Feb-02-2024 19:30 | 63.04 | 49.26 |
Feb-02-2024 19:15 | 60.49 | 47.52 |
Feb-02-2024 19:00 | 58.07 | 45.89 |
Feb-02-2024 18:45 | 55.03 | 44.34 |
Feb-02-2024 18:30 | 53.35 | 42.95 |
Feb-02-2024 18:15 | 51.38 | 41.63 |
Feb-02-2024 18:00 | 48.28 | 40.35 |
Feb-02-2024 17:45 | 47.45 | 39.27 |
Feb-02-2024 17:30 | 47.12 | 38.14 |
Feb-02-2024 17:15 | 45.98 | 36.86 |
Feb-02-2024 17:00 | 43.81 | 35.54 |
Feb-02-2024 16:45 | 43.34 | 34.23 |
Feb-02-2024 16:30 | 42.46 | 32.81 |
Feb-02-2024 16:15 | 40.31 | 31.30 |
Feb-02-2024 16:00 | 38.79 | 29.85 |
Feb-02-2024 15:45 | 36.97 | 28.48 |
Feb-02-2024 15:30 | 36.00 | 27.20 |
Feb-02-2024 15:15 | 34.84 | 25.99 |
Feb-02-2024 15:00 | 34.18 | 24.95 |
Feb-02-2024 14:45 | 33.55 | 23.86 |
Feb-02-2024 14:30 | 32.17 | 22.72 |
Feb-01-2024 20:45 | 32.12 | 21.61 |
Feb-01-2024 20:30 | 30.42 | 20.51 |
Feb-01-2024 20:15 | 27.93 | 19.69 |
Feb-01-2024 20:00 | 26.24 | 19.42 |
Feb-01-2024 19:45 | 24.18 | 19.36 |
Feb-01-2024 19:30 | 22.07 | 19.53 |
Feb-01-2024 19:15 | 19.76 | 19.99 |
Feb-01-2024 19:00 | 18.55 | 20.78 |
Feb-01-2024 18:45 | 18.17 | 21.74 |
Feb-01-2024 18:30 | 17.80 | 22.75 |
Feb-01-2024 18:15 | 17.80 | 23.89 |
Feb-01-2024 18:00 | 19.38 | 25.07 |
Feb-01-2024 17:45 | 17.80 | 26.10 |
Feb-01-2024 17:30 | 16.35 | 27.40 |
Feb-01-2024 17:15 | 15.58 | 28.91 |
Feb-01-2024 17:00 | 15.53 | 30.63 |
Feb-01-2024 16:45 | 18.25 | 32.60 |
Feb-01-2024 16:30 | 23.86 | 34.52 |
Feb-01-2024 16:15 | 25.31 | 36.04 |
Feb-01-2024 16:00 | 26.68 | 37.67 |
Feb-01-2024 15:45 | 29.07 | 39.33 |
Feb-01-2024 15:30 | 31.57 | 40.86 |
Feb-01-2024 15:15 | 32.91 | 42.29 |
Feb-01-2024 15:00 | 33.37 | 43.72 |
Feb-01-2024 14:45 | 34.89 | 45.21 |
Feb-01-2024 14:30 | 35.42 | 46.64 |
Jan-31-2024 20:45 | 34.85 | 48.21 |
Jan-31-2024 20:30 | 37.35 | 49.96 |
Jan-31-2024 20:15 | 39.06 | 51.76 |
Jan-31-2024 20:00 | 41.34 | 53.91 |
Jan-31-2024 19:45 | 45.06 | 56.52 |
Jan-31-2024 19:30 | 47.13 | 59.16 |
Jan-31-2024 19:15 | 46.62 | 61.54 |
Jan-31-2024 19:00 | 49.72 | 63.96 |
Jan-31-2024 18:45 | 51.54 | 64.97 |
Jan-31-2024 18:30 | 52.11 | 66.00 |
Jan-31-2024 18:15 | 53.03 | 67.16 |
Jan-31-2024 18:00 | 54.37 | 68.45 |
Jan-31-2024 17:45 | 55.69 | 69.86 |
Jan-31-2024 17:30 | 56.32 | 71.43 |
Jan-31-2024 17:15 | 58.95 | 73.32 |
Jan-31-2024 17:00 | 61.07 | 75.37 |
Jan-31-2024 16:45 | 64.38 | 77.75 |
Jan-31-2024 16:30 | 71.38 | 80.43 |
Jan-31-2024 16:15 | 80.40 | 82.69 |
Jan-31-2024 16:00 | 84.65 | 83.45 |
Jan-31-2024 15:45 | 82.86 | 82.86 |
Jan-31-2024 15:30 | 82.86 | 82.86 |
