The code still works for oil and gas. It will find and pull in the refined data, I can see it on the current prices worksheet while it's running but when it's finished running it will delete the refined data and doesn't populate any of the other sheets for refined only. I copied all code bits for oil and replaced the word oil with refined.
Sub Prices()
Dim asOfDate As Date
Dim i, c, r As Integer
Dim break As Integer
Dim wf As WorksheetFunction
ws_currentprices.Activate
'Copy date from summary ws to pop asofdate
Cells(ASOFDATE_ROW, BUCKET_COL) = ws_summary.Cells(1, 6)
If IsEmpty(Cells(ASOFDATE_ROW, BUCKET_COL)) Then
asOfDate = Date
Else
asOfDate = Cells(ASOFDATE_ROW, BUCKET_COL)
End If
'Setting to manual calculation must happen after asofdate has been populated
Application.Calculation = xlManual
'Clear all data and headers from the current prices ws
Range(Cells(STATUS_ROW, FIRSTDATA_COL), Cells(110, 50)).ClearContents
Set wf = Application.WorksheetFunction
'Build arraylist of gas markets
Dim gasArray As Object
Set gasArray = CreateObject("System.Collections.ArrayList")
i = 1
Do Until (IsEmpty(ws_gasmarkets.Cells(i, 4)))
If StrComp(ws_gasmarkets.Cells(i, 4), "Yes", vbTextCompare) = 0 Then gasArray.Add ws_gasmarkets.Cells(i, 2).Value
i = i + 1
Loop
'Process arraylist of gas markets
c = FIRSTDATA_COL
For i = 0 To gasArray.Count - 1
Days = 0
Do Until Month(wf.WorkDay(asOfDate, Days)) <> Month(asOfDate)
Cells(COMMODITY_ROW, c) = gasArray(i)
Cells(ASOFDATE_ROW, c) = CDate(wf.WorkDay(asOfDate, Days))
Cells(DATASOURCE_ROW, c) = "Official"
Cells(MARKETTYPE_ROW, c) = "Gas"
break = c - 1
c = c + 1
Days = Days - 1
Loop
Cells(COMMODITY_ROW, c) = gasArray(i)
Cells(ASOFDATE_ROW, c) = dhLastDayInMonth(DateSerial(Year(asOfDate), Month(asOfDate) - 1, 1))
Cells(DATASOURCE_ROW, c) = "Official"
Cells(MARKETTYPE_ROW, c) = "Gas"
break = c - 1
c = c + 1
Next
'Build arraylist of oil markets
Dim oilArray As Object
Set oilArray = CreateObject("System.Collections.ArrayList")
i = 1
Do Until (IsEmpty(ws_oilmarkets.Cells(i, 4)))
If StrComp(ws_oilmarkets.Cells(i, 4), "Yes", vbTextCompare) = 0 Then oilArray.Add ws_oilmarkets.Cells(i, 2).Value
i = i + 1
Loop
'Process arraylist of oil markets
For i = 0 To oilArray.Count - 1
Days = 0
Do Until Month(wf.WorkDay(asOfDate, Days)) <> Month(asOfDate)
Cells(COMMODITY_ROW, c) = oilArray(i)
Cells(ASOFDATE_ROW, c) = CDate(wf.WorkDay(asOfDate, Days))
Cells(DATASOURCE_ROW, c) = "Official"
Cells(MARKETTYPE_ROW, c) = "Oil"
break = c - 1
c = c + 1
Days = Days - 1
Loop
Cells(COMMODITY_ROW, c) = oilArray(i)
Cells(ASOFDATE_ROW, c) = dhLastDayInMonth(DateSerial(Year(asOfDate), Month(asOfDate) - 1, 1))
Cells(DATASOURCE_ROW, c) = "Official"
Cells(MARKETTYPE_ROW, c) = "Oil"
break = c - 1
c = c + 1
Next
'Build arraylist of REFINED markets
Dim REFINEDArray As Object
Set REFINEDArray = CreateObject("System.Collections.ArrayList")
i = 1
Do Until (IsEmpty(ws_REFINEDmarkets.Cells(i, 4)))
If StrComp(ws_REFINEDmarkets.Cells(i, 4), "Yes", vbTextCompare) = 0 Then REFINEDArray.Add ws_REFINEDmarkets.Cells(i, 2).Value
i = i + 1
Loop
'Process arraylist of REFINED markets
For i = 0 To REFINEDArray.Count - 1
Days = 0
Do Until Month(wf.WorkDay(asOfDate, Days)) <> Month(asOfDate)
Cells(COMMODITY_ROW, c) = REFINEDArray(i)
Cells(ASOFDATE_ROW, c) = CDate(wf.WorkDay(asOfDate, Days))
Cells(DATASOURCE_ROW, c) = "Official"
Cells(MARKETTYPE_ROW, c) = "REFINED"
break = c - 1
c = c + 1
Days = Days - 1
Loop
Cells(COMMODITY_ROW, c) = REFINEDArray(i)
Cells(ASOFDATE_ROW, c) = dhLastDayInMonth(DateSerial(Year(asOfDate), Month(asOfDate) - 1, 1))
Cells(DATASOURCE_ROW, c) = "Official"
Cells(MARKETTYPE_ROW, c) = "Refined"
break = c - 1
c = c + 1
Next
'Downloads current prices from Kiodex
DownloadCurrentPrices
'Remove invalid pricing columns
If (PricesCleanup) Then
'Setup GAS and OIL worksheets
REFINEDSetup
GasSetup
OilSetup
'Calculate GAS and OIL worksheets
ws_REFINED.Calculate
ws_oil.Calculate
ws_gas.Calculate
'Refresh and display summary worksheet
'ws_summary.Calculate
Calculate
ws_summary.Activate
Refresh
'Set data source value based on NYMEX - Not Updated (0), Preliminary (1), or Updated (2)
c = FIRSTDATA_COL
ds = 0
gaschk = False
oilchk = False
REFINEDchk = False
Do Until (IsEmpty(ws_currentprices.Cells(COMMODITY_ROW, c)) Or (gaschk And oilchk And REFINEDchk))
If (StrComp(ws_currentprices.Cells(COMMODITY_ROW, c), "GD Henry Hub", vbTextCompare) And Not gaschk) Then
If (ws_currentprices.Cells(ASOFDATE_ROW, c) = ws_currentprices.Cells(ASOFDATE_ROW, BUCKET_COL)) Then
'Data for current date
If (StrComp(ws_currentprices.Cells(DATASOURCE_ROW, c), "Official", vbTextCompare) = 0) Then
'Data is official
ds = ds + 2
Else
'Data is global
ds = ds + 1
End If
Else
'Data for prior date
ds = ds + 0
End If
gaschk = True
End If
If (StrComp(ws_currentprices.Cells(COMMODITY_ROW, c), "NYMEX WTI", vbTextCompare) And Not oilchk) Then
If (ws_currentprices.Cells(ASOFDATE_ROW, c) = ws_currentprices.Cells(ASOFDATE_ROW, BUCKET_COL)) Then
'Data for current date
If (StrComp(ws_currentprices.Cells(DATASOURCE_ROW, c), "Official", vbTextCompare) = 0) Then
'Data is official
ds = ds + 2
Else
'Data is global
ds = ds + 1
End If
Else
'Data for prior date
ds = ds + 0
End If
oilchk = True
End If
'***REFINED addition***
If (StrComp(ws_currentprices.Cells(COMMODITY_ROW, c), "OPIS Ethane Mt Belv non TET", vbTextCompare) And Not REFINEDchk) Then
If (ws_currentprices.Cells(ASOFDATE_ROW, c) = ws_currentprices.Cells(ASOFDATE_ROW, BUCKET_COL)) Then
'Data for current date
If (StrComp(ws_currentprices.Cells(DATASOURCE_ROW, c), "Official", vbTextCompare) = 0) Then
'Data is official
ds = ds + 2
Else
'Data is global
ds = ds + 1
End If
Else
'Data for prior date
ds = ds + 0
End If
REFINEDchk = True
End If
c = c + 1
Loop
Select Case ds
Case Is >= 4
ws_summary.Range("SummaryDataSource") = "Updated"
Case Is > 0
ws_summary.Range("SummaryDataSource") = "Preliminary"
Case Else
ws_summary.Range("SummaryDataSource") = "Not Updated"
End Select
Else
ws_summary.Activate
ws_summary.Range("SummaryDataSource") = "Not Updated"
End If
'Set last updated date
ws_summary.Range("LastUpdatedDateTime") = Now
Application.Calculation = xlAutomatic
Application.ReferenceStyle = xlA1
'Update BOKF Pricing History
If Format(asOfDate, "m/d/yyyy") = Format(WorksheetFunction.WorkDay(WorksheetFunction.EoMonth(asOfDate, 0) + 1, -1), "m/d/yyyy") Then
Call UpdateBOKFPriceHistory(Format(DateSerial(Year(asOfDate), Month(asOfDate) + 1, 1), "mm/dd/yyyy"), False)
End If
End Sub
'This function checks the Current Prices tab for any columns that are duplicates of the day before or weekends and deletes the column
Function PricesCleanup() As Boolean
Dim r, c As Integer
Dim removeCount As Integer
Dim removeColumn As Boolean
Dim isGas, isOil, isREFINED As Boolean
c = FIRSTDATA_COL
removeCount = 0
Do Until IsEmpty(ws_currentprices.Cells(COMMODITY_ROW, c)) 'check every col of prices
'Start at the row of the first date and reset remove flag
r = FIRSTDATE_ROW
removeColumn = True
'Check each column, at least until there is a discrepancy between prices so we know it's not a holiday
Do Until ((r > 12 And IsEmpty(ws_currentprices.Cells(r, c))) Or r > 60 Or Not removeColumn)
'If the prices don't match, we know it's not a holiday
If (ws_currentprices.Cells(r, c) <> ws_currentprices.Cells(r, c + 1)) Then
'If the first row is empty or matches second row, it's likely due to near EoM index shifting and requires special handling
If r = FIRSTDATE_ROW Then
If IsEmpty(ws_currentprices.Cells(r, c)) Then
'Oil index swap
removeColumn = False
End If
If (ws_currentprices.Cells(r, c) = ws_currentprices.Cells(r + 1, c) And ws_currentprices.Cells(MARKETTYPE_ROW, c) = "Gas") Then
removeColumn = False
End If
'***Refined
If (ws_currentprices.Cells(r, c) = ws_currentprices.Cells(r + 1, c) And ws_currentprices.Cells(MARKETTYPE_ROW, c) = "REFINED") Then
removeColumn = False
End If
Else
'Not index related and no match, so don't remove column
removeColumn = False
End If
End If
r = r + 1
Loop
'Check for weekend dates or dates from prior month
If Weekday(ws_currentprices.Cells(ASOFDATE_ROW, c)) = 1 Or Weekday(ws_currentprices.Cells(ASOFDATE_ROW, c)) = 7 Or Month(ws_currentprices.Cells(ASOFDATE_ROW, c)) <> Month(w
