def test_add_keywords(self):
name = 'My Chart'
chart_type = ChartType.xlLine
Range('A1').value = chart_data
chart = Chart().add(chart_type=chart_type, name=name, source_data=Range('A1').table)
chart_actual = Chart(name)
name_actual = chart_actual.name
chart_type_actual = chart_actual.chart_type
assert_equal(name, name_actual)
assert_equal(chart_type, chart_type_actual)
def test_add_keywords(self):
name = 'My Chart'
chart_type = ChartType.xlLine
Range('A1').value = chart_data
chart = Chart.add(chart_type=chart_type, name=name, source_data=Range('A1').table)
chart_actual = Chart(name)
name_actual = chart_actual.name
chart_type_actual = chart_actual.chart_type
assert_equal(name, name_actual)
if sys.platform.startswith('win'):
assert_equal(chart_type, chart_type_actual)
else:
assert_equal(kw.line_chart, chart_type_actual)
def test_add_properties(self):
name = 'My Chart'
chart_type = ChartType.xlLine
Range('Sheet2', 'A1').value = chart_data
chart = Chart.add('Sheet2')
chart.chart_type = chart_type
chart.name = name
chart.set_source_data(Range('Sheet2', 'A1').table)
chart_actual = Chart('Sheet2', name)
name_actual = chart_actual.name
chart_type_actual = chart_actual.chart_type
assert_equal(name, name_actual)
if sys.platform.startswith('win'):
assert_equal(chart_type, chart_type_actual)
else:
assert_equal(kw.line_chart, chart_type_actual)
def main():
wb = Workbook() # Creates a connection with a new workbook
Range('A1').value = 'Foo 1'
print Range('A1').value
# 'Foo 1'
Range('A1').value = [['Foo 1', 'Foo 2', 'Foo 3'], [10.0, 20.0, 30.0]]
print Range('A1').table.value # or: Range('A1:C2').value
# [['Foo 1', 'Foo 2', 'Foo 3'], [10.0, 20.0, 30.0]]
print Sheet(1).name
# 'Sheet1'
chart = Chart.add(source_data=Range('A1').table)
def main():
wb = Workbook() # Creates a connection with a new workbook
Range('A1').value = 'Foo 1'
print Range('A1').value
# 'Foo 1'
Range('A1').value = [['Foo 1', 'Foo 2', 'Foo 3'], [10.0, 20.0, 30.0]]
print Range('A1').table.value # or: Range('A1:C2').value
# [['Foo 1', 'Foo 2', 'Foo 3'], [10.0, 20.0, 30.0]]
print Sheet(1).name
# 'Sheet1'
chart = Chart.add(source_data=Range('A1').table)
def main():
wb = Workbook.caller()
# User Inputs
num_simulations = int(Range('E3').value)
time = Range('E4').value
num_timesteps = int(Range('E5').value)
dt = time / num_timesteps # Length of time period
vol = Range('E7').value
mu = np.log(1 + Range('E6').value) # Drift
starting_price = Range('E8').value
perc_selection = [5, 50, 95] # percentiles (hardcoded for now)
# Animation
if Range('E9').value.lower() == 'yes':
animate = True
else:
animate = False
# Excel: clear output, write out initial values of percentiles/sample path and set chart source
# and x-axis values
Range('O2').table.clear_contents()
Range('P2').value = [
starting_price, starting_price, starting_price, starting_price
]
Chart('Chart 5').set_source_data(Range((1, 15), (num_timesteps + 2, 19)))
Range('O2').value = np.round(
np.linspace(0, time, num_timesteps + 1).reshape(-1, 1), 2)
# Preallocation
price = np.zeros((num_timesteps + 1, num_simulations))
percentiles = np.zeros((num_timesteps + 1, 3))
# Set initial values
price[0, :] = starting_price
percentiles[0, :] = starting_price
# Simulation at each time step
for t in range(1, num_timesteps + 1):
rand_nums = np.random.randn(num_simulations)
price[t, :] = price[t - 1, :] * np.exp((mu - 0.5 * vol**2) * dt +
vol * rand_nums * np.sqrt(dt))
percentiles[t, :] = np.percentile(price[t, :], perc_selection)
if animate:
Range((t + 2, 16)).value = percentiles[t, :]
Range((t + 2, 19)).value = price[t, 0] # Sample path
if sys.platform.startswith('win'):
wb.application.screen_updating = True
if not animate:
Range('P2').value = percentiles
Range('S2').value = price[:, :1] # Sample path
def test_add_keywords(self):
name = 'My Chart'
chart_type = ChartType.xlLine
Range('A1').value = chart_data
chart = Chart.add(chart_type=chart_type, name=name, source_data=Range('A1').table)
chart_actual = Chart(name)
name_actual = chart_actual.name
chart_type_actual = chart_actual.chart_type
assert_equal(name, name_actual)
if sys.platform.startswith('win'):
assert_equal(chart_type, chart_type_actual)
else:
assert_equal(kw.line_chart, chart_type_actual)
def test_add_properties(self):
name = 'My Chart'
chart_type = ChartType.xlLine
Range('Sheet2', 'A1').value = chart_data
chart = Chart().add('Sheet2')
chart.chart_type = chart_type
chart.name = name
chart.set_source_data(Range('Sheet2', 'A1').table)
chart_actual = Chart('Sheet2', name)
name_actual = chart_actual.name
chart_type_actual = chart_actual.chart_type
assert_equal(name, name_actual)
assert_equal(chart_type, chart_type_actual)
def test_add_properties(self):
name = 'My Chart'
chart_type = ChartType.xlLine
Range('Sheet2', 'A1').value = chart_data
chart = Chart.add('Sheet2')
chart.chart_type = chart_type
chart.name = name
chart.set_source_data(Range('Sheet2', 'A1').table)
chart_actual = Chart('Sheet2', name)
name_actual = chart_actual.name
chart_type_actual = chart_actual.chart_type
assert_equal(name, name_actual)
if sys.platform.startswith('win'):
assert_equal(chart_type, chart_type_actual)
else:
assert_equal(kw.line_chart, chart_type_actual)
from xlwings import Workbook, Sheet, Range, Chart
wb = Workbook(r'C:\Users\mih\Desktop\Stock_Screener\Proj1.xlsx')
Range('A1').value = 'Two 2'
print Range('A1').value
Range('A1').value = [['Too 1', 'Foo 2', 'Foo 3'], [10.0, 20.0, 40.0]]
Range('A1').table.value # or: Range('A1:C2').value
Sheet(1).name
chart = Chart.add(source_data=Range('A1').table)
wb.save("C:\Users\mih\Desktop\Stock_Screener\Proj1.xlsx")
def hietograma():
def lluvia_max():
muestra = Range('LLUVIA', 'C5').table.value
#numero de datos
DataNr = len(muestra)
#cambiamos tipo de dato a 'array'
muestra = np.asarray(muestra)
#todos valores de los parametrod de Gumbel hasta 58 datos
Yn = Range('+', 'C4:C29').value
Sigma = Range('+', 'D4:D29').value
Indeks = Range('+', 'B4:B29').value.index(DataNr)
#Valores adoptados por los parametros de la funcion Gumbel:
Yn = Yn[Indeks]
Sigma = Sigma[Indeks]
### empty lists creation
s = []
m = []
alfa = []
mu = []
P = []
Prob = []
#los parametros de la funcion de Gumbel para cada estacion
#ddof: "Delta Degrees of Freedom" by default 0
for i in range(0, 4):
columna = muestra[:, i]
s.append(np.std(columna, ddof=1))
m.append(np.mean(columna))
alfa.append(s[i] / Sigma)
mu.append(m[i] - Yn * alfa[i])
P.append(mu[i] - alfa[i] * np.log(np.log(T / (T - 1))))
Prob.append(1 - 1 / T)
### PESOS
dist = np.asarray([3968, 14046, 13251, 12596]) #distancias...
pesos = 1 / dist**2
sq = [1 / i**2 for i in dist]
pesos = pesos / sum(sq)
### Pmax24
return sum(P * pesos)
wb = Workbook.caller()
#### clear table
Range('R5').table.clear_contents()
#### introduccion de los datos
####periodo de retorno
T = int(Range('INTERFACE', 'D8').value)
#### duracion de lluvia
dur = int(Range('INTERFACE', 'D10').value)
#### intervalo de tiempo
inval = int(Range('INTERFACE', 'D12').value)
#### En este script I1/Id se denomina X
X = int(Range('INTERFACE', 'D14').value)
#### Precipitaciones maximas diarias
P24 = Range('P24', 'C14:J14').value
if Range('INTERFACE', 'D12').value == 'Ya tengo P24':
P24 = Range('INTERFACE', 'D18').value
else:
#P24=Range('P24', 'C14:J14').value
P24 = lluvia_max()
print(P24)
#### periodos de retorno disponibles
Tr = [2, 5, 10, 25, 50, 100, 500, 1000]
ind = Tr.index(T)
#### generacion del tiempo eje X
time = np.arange(inval, dur * 60 + inval, inval)
Range('INTERFACE', 'R5').value = np.arange(inval, dur * 60 + inval,
inval)[:, np.newaxis]
#### Precipitaciones maximas diarias mm/hr
#Id=[i/24 for i in P24]
Id = P24 / 24
#### Intensidad de lluvia mm/hr
I = Id * (X)**((28**0.1 - (time / 60)**0.1) / (28**0.1 - 1))
Range('INTERFACE', 'S5').value = I[:, np.newaxis]
lluvia = I * time / 60
Range('INTERFACE', 'T5').value = lluvia[:, np.newaxis]
x = lluvia[0]
#### Incremental depth
IncDep = []
for i in range(1, len(lluvia)):
IncDep.append(lluvia[i] - lluvia[i - 1])
IncDep.insert(0, x)
IncDep = np.asarray(IncDep)
Range('INTERFACE', 'U5').value = IncDep[:, np.newaxis]
#### Ordenacion methodo de bloques alternos
nr = Range('U5').table.last_cell.row
tt = sorted(Range('INTERFACE', 'U5:U' + str(nr)).value)
new_tt = sorted(tt)[1::2] + sorted(tt)[::2][::-1]
new_tt = np.asarray(new_tt)
Range('INTERFACE', 'V5:V' + str(nr)).value = new_tt[:, np.newaxis]
#### Chart
chart = Chart('Wykres 4', source_data=Range('V5:V' + str(nr)).table)
import xlwings
from xlwings import Workbook, Sheet, Range, Chart
# Creates a connection with a new workbook
# wb = Workbook()
wb = Workbook.caller()
Range('A1').value = 'Foo 1'
print Range('A1').value
Range('A1').value = [['Foo 1', 'Foo 2', 'Foo 3'], [10.0, 20.0, 30.0]]
print Range('A1').table.value # or: Range('A1:C2').value
print Sheet(1).name
chart = Chart.add(source_data=Range('A1').table)
# to open excel template
# from xlwings import Workbook
# Workbook.open_template()
