VBa -useform dynamic - stock portolio weight

dnaddeo

New Member
Joined
Dec 29, 2020
Messages
11
Office Version
  1. 2019
  2. 2016
  3. 2013
Dear all,
food year first. I have an issue for the following code. As per photo 1.2 i have create an useform where upload the % of a portfolio allocation (from 0,1 to 1%) for a max of 10 stock and in case put 0 as number if that stock doesn'twant be choosen from the user. The problem is that i need to be dynamic as, in case of add 0 to useform, for my code is adding as well a number and it's count it. (i have highlighted in red, column H10-K10). The second issue is on photo 1.1 as i should compute the calcolation with "volatilidad anual" and no "media anual". As you can see the transport is because the calcolation is run on matric, addiung moltiplin with the value add from the usedfrom.
Here the code:


VBA Code:
Private Sub Cancelar_Click()
   
    Unload Formulario

End Sub

Private Sub Simular_Click()
'guardar los pesos en la pestana Descriptivos

    Worksheets("Descriptiva").Activate
    Cells(10, 1).Value = "Pesos"
   
'notificar si el numero es numero o no
If IsNumeric(Peso_01.Value) And IsNumeric(Peso_02.Value) And IsNumeric(Peso_03.Value) And IsNumeric(Peso_04.Value) _
    And IsNumeric(Peso_05.Value) And IsNumeric(Peso_06.Value) And IsNumeric(Peso_07.Value) And IsNumeric(Peso_08.Value) And IsNumeric(Peso_09.Value) _
    And IsNumeric(Peso_10.Value) Then
    Cells(10, 2).Value = Peso_01.Value
    Cells(10, 3).Value = Peso_02.Value
    Cells(10, 4).Value = Peso_03.Value
    Cells(10, 5).Value = Peso_04.Value
    Cells(10, 6).Value = Peso_05.Value
    Cells(10, 7).Value = Peso_06.Value
    Cells(10, 8).Value = Peso_07.Value
    Cells(10, 9).Value = Peso_08.Value
    Cells(10, 10).Value = Peso_09.Value
    Cells(10, 11).Value = Peso_10.Value
Else
    MsgBox ("pesos entre 0 y 1")
    Exit Sub
End If

' media y voltilidad de la cartera
    Cells(12, 1) = "Cartera"
    Cells(13, 1) = "Media"
    Cells(14, 1) = "Volatilidad"
    Range("B13").Select
    ActiveCell.FormulaR1C1 = "=TRANSPOSE(R[-3]C:R[-3]C[9])*(R[-6]C:R[-6]C[9])"
    Range("B14").Select
    ActiveCell.FormulaR1C1 = "=TRANSPOSE(R[-4]C:R[-4]C[9])*(R[-7]C:R[-7]C[9])"

' simluar 10 acciones en cartera
     Application.Run "ATPVBAEN.XLAM!Random", "Simulaciones", N_sim.Value, Dias.Value, 2, , _
       Cells(13, 2).Value, Cells(14, 2).Value
       Worksheets("Simulaciones").Move After:=Worksheets(Worksheets.Count)

'copiar nombre titulos
   ' Sheets("Cotizaciones").Select
   ' Range("B1:K1").Select
   ' Selection.Copy
   ' Sheets("Simulaciones").Select
   ' Range("A1").Select
   ' ActiveSheet.Paste
   
    Dim lastcolumn As Long
    Worksheets("Cotizaciones").Activate
    lastcolumn = Worksheets("Cotizaciones").Cells(1, Columns.Count).End(xlToLeft).Column

    Worksheets("Cotizaciones").Range("B1", Cells(1, lastcolumn)).Copy
    Sheets("Simulaciones").Select
    Range("A1").Select
    ActiveSheet.Paste

Unload Formulario

End Sub

'para anadir nombre acciones
Private Sub UserForm_Initialize()
    Worksheets("Descriptiva").Activate
    Label1.Caption = Cells(1, 2).Value
    Label2.Caption = Cells(1, 3).Value
    Label3.Caption = Cells(1, 4).Value
    Label4.Caption = Cells(1, 5).Value
    Label5.Caption = Cells(1, 6).Value
    Label6.Caption = Cells(1, 7).Value
    Label9.Caption = Cells(1, 8).Value
    Label10.Caption = Cells(1, 9).Value
    Label11.Caption = Cells(1, 10).Value
    Label12.Caption = Cells(1, 11).Value

End Sub


Thanks to all that can check it, Davide
 

Attachments

  • 1.1.png
    1.1.png
    122.8 KB · Views: 5
  • 1.2.png
    1.2.png
    72.3 KB · Views: 5
Last edited by a moderator:

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Watch MrExcel Video

Forum statistics

Threads
1,128,107
Messages
5,628,724
Members
416,333
Latest member
Time2Learn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top