Thank you very much. Appreciated.
Posts by Maqbool
-
-
Hi Carim:
Thank you very much for the help. Really appreciated.
Sorry:
Doesn't work as expected.
I have added another transaction in the last row. Expected average is 442.01 instead the formula suggested is giving me 400.40
Please see the attachment.
Best Regards,
Maqbool
-
Thank you verymuch for the reply and appreciated.
The Average Purchase Price needs to be calculated based on the entire database considering the sale, not on a monthly basis.
This February position sold out on Oct. 25th.
Then initated another purchase (100 qty) on 17th Dec. If there is no purchase after that your above formula will work fine. But another purchase booked on 23 Dec. (25 qty). Out this only 75 shares sold on 28/12. Until the entire position is sold out, need to get the average using the purchase value of Dec. 23 and Dec. 25 so on.....
I have updated the spreasheet with expected result in column I.
If not clear let me know will try to explain it further.
Best Regards
Maqbool
-
Hi Everyone:
Hope everyone is doing well and safe.
I need an help to find out the average purchase price of a stock. When i sell the entire position and initiate a new position for the same stock the average calculation should not include the purchases initated prior to "sale of entire position". I have attached a sample spreadsheet. On the average price column, i have calculated the average price using SUMIFS, unfortunately it wont gives me the correct average price if there are multiple purchase and sales. I have created a column for the expected result.
I am using Office 365 and any solution using formula or query is fine.
Best Regards
Maqbool
-
Hi
I think the problem is here:
With ActiveCell
'>>>>>>>>>>> Failing to paste NEXT LINE <<<<<<<<<<<<
ActiveSheet.PasteEnd With
Can you try replacing it with
Regards
Maqbool
-
Hi
Can you please post a sample workbook with expected results?
-
Hi Bob:
Please read the forum rules. You need to tag the code when you post.
I am not sure i understand you fully.
Try the following:
Code
Display MoreDim strDate As String 'paste below code after "IF sheets("Mandatory").Cells("i,1)...................................... Then" With Sheets("Mandatory") strDate = Me.Textbox4.Value If IsNumber(strDate) Then 'check the value is in textbox .Cells(i, 5).Value = strDate Else MsgBox "Invalid Date." End If End With
otherwise post a sample workbook with expected result.
Maqbool
-
Please note that: this is to plot chart. Otherwise I can use a conditional formatting to hide.
-
Hi Everyone:
Hope Everyone doing well.
Can someone help me on how to hide the running total of a field on a Pivot Table. See the attached spreadsheet. I need to hide the highlighted numbers until the months data available on table. Any help will be appreciated.
Regards
Maqbool
-
Hi
Welcome to the forum:
Do you want store all changes (I mean, history of the changes) every changes on the column (what changed to what and the time of both changes) etc..
Can you please provide a sample of this?Regards,
Maqbool
-
-
Thank you Ali.
Finally, I managed to do this. Thanks a lot for the help.
Regards,
Maqbool
-
Thank you for the reply.
No - I need a flexibility to choose historic files also. I found a way to do this with latest file . That does not meet my requirement
Maqbool
-
Hi
Can someone guide me how to dynamically change the source of a Power Query; My source files are in a folder. I am trying to change the source of the query by typing the file name in a cell. I found few videos which takes all files from the folder. But I need to use a single file as my data source. This source files are created on a weekly basis with a date suffix.
Thank you very much
Any help will be highly appreciated. -
Hi
Try the code below:
Code
Display MoreSub SumSheets() Sheets("Summary").Range("A2:A1000").ClearContents ' clear everything in summary before pasting Dim sh As Worksheet For Each sh In ThisWorkbook.Sheets If sh.Name <> "Summary" And sh.Name <> "Sheet1" And sh.Name <> "Sheet2" Then sh.Range("F17:F66").Copy Sheets("Summary").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlValues End If Next End Sub
Regards
Maqbool
-
Hi
Code below will solve both issues:
Please leave blank row 1 on consolidation workbook.
Code
Display MoreSub Test() Dim wbSls As Workbook Dim wbMnthly As String Dim wsSls As Worksheet Dim x As Long Dim strDir As String Dim lstRw As Long Dim strLkpArra As String Set wbSls = ThisWorkbook strPath = "'C:\Test\[" ' change accordingly strSht = "Master Data'!R7C2:R" With wbSls Set wsSls = .Sheets("Sales") End With n = 11 With wsSls Do Until n = lstRw lstRwInClsd = .Range("D4").Value + 6 x = .Range("D8").End(xlToRight).Column lstRw = .Rows.Count lstRw = .Range("D" & lstRw).End(xlUp).Offset(0, -1).Row For x = 4 To x strfile = .Cells(8, x) strLkpArray = strPath & strfile & "]" & strSht 'levae row 1 empty on consolidated workbook - this helper row to get the last row in closed workbook .Cells(1, x).Value = "=COUNTA('" & strPath & strfile & "]Master Data'!$B$8:$B$10000)" lstRwInClsd = .Cells(1, x).Value + 6 '(6 rows blank on the monthly workbook - may need to adjust accordingly) .Cells(n, x).Value = "=VLOOKUP(RC2," & "'" & strLkpArray & lstRwInClsd & "C11,10,FALSE)" .Cells(1, x).Value = "" Next n = n + 1 Loop End With End Sub
Regards
Maqbool
-
Hi
Change the code as below:
Code
Display MoreSub Test() Dim wbSls As Workbook Dim wbMnthly As String Dim wsSls As Worksheet Dim x As Long Dim strDir As String Dim lstRw As Long Dim rngFmula As Range Set wbSls = ThisWorkbook strpath = "'C:\Test\[" ' change accordingly strSht = "Master Data'!$B$7:$K$33,10,FALSE)" With wbSls Set wsSls = .Sheets("Sales") End With n = 11 With wsSls Do Until n = lstRw x = .Range("D8").End(xlToRight).Column lstRw = .Rows.Count lstRw = .Range("D" & lstRw).End(xlUp).Offset(0, -1).Row For x = 4 To x strfile = .Cells(8, x) strlkparray = strpath & strfile & "]" & strSht .Cells(n, x).Value = "=VLOOKUP($B11," & strlkparray Next n = n + 1 Loop End With End Sub
Regards
Maqbool
-
Hi
Try the following. I have noticed an issues with your file extension (why you do have January 2010.xlsm.xlsx) . This needs to be corrected. Or change the file names on row 8.
Code
Display MoreSub Test() Dim wbSls As Workbook Dim wbMnthly As String Dim wsSls As Worksheet Dim x As Long Dim strDir As String Set wbSls = ThisWorkbook strpath = "'C:\Test\[" ' change accordingly strSht = "Master Data'!$B$7:$K$33,10,FALSE)" With wbSls Set wsSls = .Sheets("Sales") End With With wsSls x = .Range("D8").End(xlToRight).Column For x = 4 To x strfile = .Cells(8, x) strlkparray = strpath & strfile & "]" & strSht Debug.Print strlkparray .Cells(11, x).Value = "=VLOOKUP($B11," & strlkparray Next End With End Sub
Regards
Maqbool
-
Hi
I can't upload the workbook now. I will upload the workbook later today.
Follow the below steps:
Create three ranged names as below:
Use below formula against each name
[F]
tblData: Sheet1!$C$1:INDEX(Sheet1!$C$1:$G$1000,COUNTA(Sheet1!$C:$C),5)rngCat : =INDEX(tblData,0,2)
rngPercent =OFFSET(Sheet1!$C$1,MATCH(Sheet1!$J$1,rngCat,0)-1,4,COUNTIF(rngCat,Sheet1!$J$1))
[/F]Then create the following formula on your sample sheet
L21 - enter as an array formula (CTRL+SHIFT+Enter][F]
N21 = LARGE(rngPercent,$K21)
L21 = OFFSET(rngPercent,MATCH(N21,rngPercent,0)-1,-4)
M21= VLOOKUP(L21,tblData,MATCH($M$20,$C$1:$G$1,0),FALSE)
[/F]
Regards
Maqbool
-
Hi
try the below :
Code
Display MoreSub Test() Dim wsOne As Worksheet Dim rngOne As Range Dim testOne As String Dim testTwo As String Set wsOne = ThisWorkbook.Sheets("Sheet1") With wsOne x = .Rows.Count Set rngOne = .Range("F2", .Range("F" & x).End(xlUp)) End With With rngOne n = rngOne.Count For x = n To 1 Step -1 testOne = .Cells(x, 1).Value testTwo = .Cells(x, -1).Value Debug.Print testOne If testOne = "" And testTwo = "AFC" Then .Cells(x, -1).Font.Bold = True 'this will highlight the cell which is going to be deleted '.Cells(x, -1).EntireRow.Delete 'to delete entire row - enable this part, if everything works as expected End If Next End With End Sub
Regards
Maqbool