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.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_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)
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()