import openpyxl_lib as xlLib
import time
import numpy as np
# import matplotlib.pyplot as plt
# import pandas as pd
# path_to_folder = 'E:/Working/0 Submission CF approved annex5/Aprobadas y ajustadas por eco 19-01-2019/adjusted by eco/'
# path_to_folder = 'C:/Users/212566876/Box Sync/Rotation 4/Ecopetrol Reserve Audit/Cashflow Fix/adjusted by eco/'
# path_to_folder = 'C:/Users/212566876/Box Sync/Rotation 4/Ecopetrol Reserve Audit/Cashflow Fix/Adjusted by Ecopetrol Jan 25 2019/'
# path_to_folder = 'C:/Users/212566876/Box Sync/Rotation 4/Ecopetrol Reserve Audit/Cashflow Fix/Adjusted by Eco Jan252019/'
path_to_folder = 'C:/Users/212566876/Box Sync/Rotation 4/Ecopetrol Reserve Audit/Cashflow Fix/Approved by GCA adjusted by GCA(SAI)/'
# path_to_folder = 'C:/Users/212566876/Box Sync/Rotation 4/Ecopetrol Reserve Audit/Cashflow Fix/test 2/'
# cashFlowFiles = []
# Get column numbers
colNum_AX = xlLib.col2num('AX') # oil price ($/BBL)
colNum_AY = xlLib.col2num('AY') # oil offset calidad price ($/BBL)
colNum_AZ = xlLib.col2num('AZ') # oil offset transporte price ($/BBL)
colNum_AQ = xlLib.col2num('AQ') # net oil volume (BBL)
colNum_AR = xlLib.col2num('AR') # fuel oil volume (BBL)
colNum_AS = xlLib.col2num('AS') # net gas volume (MCF)
colNum_AO = xlLib.col2num('AO') # royalties gas volume (MCF)
colNum_BA = xlLib.col2num('BA') # gas calorific factor (MMBTU/MCF)
colNum_BB = xlLib.col2num('BB') # gas price ($/MMBTU)
colNum_AU = xlLib.col2num('AU') # propane volume (BBL)
colNum_AV = xlLib.col2num('AV') # butane volume (BBL)
colNum_AW = xlLib.col2num('AW') # gasoline volume (BBL)
colNum_AP = xlLib.col2num('AP') # royalties NGL volume (BBL)
colNum_BC = xlLib.col2num('BC') # propane price ($/BBL)
colNum_BD = xlLib.col2num('BD') # butane price ($/BBL)
colNum_BE = xlLib.col2num('BE') # gasoline price ($/BBL)
ws['T1'] = 'Reserves Net to Ecopetrol Interest'
ws['T2'] = 'Oil'
ws['T3'] = 'MBbl'
ws['U2'] = 'Fuel Oil'
ws['U3'] = 'MBbl'
ws['V2'] = 'NGLs'
ws['V3'] = 'MBbl'
ws['W2'] = 'Gas'
ws['W3'] = 'MMscf'
ws['X2'] = 'Fuel Gas'
ws['X3'] = 'MMscf'
for i in range(fieldNames.shape[0]): # 64 fields
# field name
_ = ws.cell(column=xlLib.col2num('A'), row=i + 4,
value=fieldNames[i]) # Field
# oil
_ = ws.cell(column=xlLib.col2num('B'), row=i + 4,
value=oil[i, 0, k]) # GTV, oil
_ = ws.cell(column=xlLib.col2num('H'), row=i + 4,
value=oil[i, 1, k]) # GFV, oil
_ = ws.cell(column=xlLib.col2num('N'), row=i + 4,
value=oil[i, 2, k]) # GFV to ECP, oil
_ = ws.cell(column=xlLib.col2num('T'), row=i + 4,
value=oil[i, 3, k]) # Net to ECP, oil
# fuel oil
_ = ws.cell(column=xlLib.col2num('C'),
row=i + 4,
value=fuel_oil[i, 0, k]) # GTV, fuel oil
_ = ws.cell(column=xlLib.col2num('I'),
import openpyxl
import openpyxl_lib as xlLib
import time
# import numpy as np
# import matplotlib.pyplot as plt
# import pandas as pd
# import Frac_ML_Library as fracmlLib
# path_to_folder = 'E:/Working/0 Submission CF approved annex5/Aprobadas y ajustadas por eco 19-01-2019/adjusted by eco/'
# path_to_folder = 'C:/Users/212566876/Box Sync/Rotation 4/Ecopetrol Reserve Audit/Cashflow Fix/adjusted by eco/'
path_to_folder = 'C:/Users/212566876/Box Sync/Rotation 4/Ecopetrol Reserve Audit/Cashflow Fix/Approved by GCA adjusted by GCA(SAI)/'
# path_to_folder = 'C:/Users/212566876/Box Sync/Rotation 4/Ecopetrol Reserve Audit/Cashflow Fix/test approved/'
# cashFlowFiles = []
# Get column numbers
colNum_C = xlLib.col2num('C')
colNum_D = xlLib.col2num('D')
colNum_G = xlLib.col2num('G')
colNum_H = xlLib.col2num('H')
colNum_J = xlLib.col2num('J')
colNum_K = xlLib.col2num('K')
colNum_L = xlLib.col2num('L')
colNum_O = xlLib.col2num('O')
colNum_P = xlLib.col2num('P')
colNum_R = xlLib.col2num('R')
colNum_S = xlLib.col2num('S')
colNum_T = xlLib.col2num('T')
colNum_W = xlLib.col2num('W')
colNum_AA = xlLib.col2num('AA')
colNum_AB = xlLib.col2num('AB')
colNum_AC = xlLib.col2num('AC')
##
################################################################################
import os
import openpyxl
import openpyxl_lib as xlLib
import time
import numpy as np
path_to_source = 'C:/Users/212566876/Box Sync/Rotation 4/Ecopetrol Reserve Audit/Cashflow Fix/Annex 4 Jesus/Eco CF/'
path_to_annex3 = 'C:/Users/212566876/Box Sync/Rotation 4/Ecopetrol Reserve Audit/Cashflow Fix/Annex 4 Jesus/annex 3/'
path_to_target = 'C:/Users/212566876/Box Sync/Rotation 4/Ecopetrol Reserve Audit/Cashflow Fix/Annex 4 Jesus/CRColas/'
# Get column numbers
# source docs
colNum_AQ = xlLib.col2num('AQ') # net Oil (BBL)
colNum_AS = xlLib.col2num('AS') # net gas (MCF)
colNum_AU = xlLib.col2num('AU') # net propane (BBL)
colNum_AV = xlLib.col2num('AV') # net butane (BBL)
colNum_AW = xlLib.col2num('AW') # net gasoline (BBL)
# -----------------------ANNEX 5------------------------------------------------
# source defintions & allocations
cashFlowList = [
file for file in os.listdir(path_to_source) if file.endswith('.xlsx')
and os.path.isfile(os.path.join(path_to_source, file))
]
cashFlowList.sort() # sort alphabetically
numFields = len(cashFlowList)
netOil = np.zeros((61, 10, numFields))
netGas = np.zeros((61, 10, numFields))
and os.path.isfile(os.path.join(path_to_source, file))
]
fileList.sort()
numFields = len(fileList)
faulty_fields = []
# for loop to get all the data needed
for i, file in enumerate(fileList):
startTime = time.time() # start timer for loop duration (DEBUG)
filePath = os.path.join(path_to_source, file) # get file path
wb = openpyxl.load_workbook(
filePath, data_only=True) # load workbook (data-only mode, faster)
ws_gross = wb['Gross']
data = []
for rowNum in range(79, 141):
data_row = [
ws_gross.cell(row=rowNum, column=colNum).value
for colNum in range(xlLib.col2num('B'), xlLib.col2num('BJ'))
]
data.append(data_row)
data = np.array(data)
data = np.absolute(data)
sum = np.sum(data)
if sum != 0:
faulty_fields.append(file)
# write fault fields list to text
with open('FID_field_list.txt', 'w') as f:
for field in faulty_fields:
f.write("{}\n".format(field))