Thanks bro. This is really a great video and is directly related to many of us. Thanks a lot for making this video and helping. I am going to apply it Inshaallah.
Thanks Kamran I am really happy to help. Stay tuned as on 24th Feb i will publish another with same solution using PowerBI. That will be more interesting and fun.
@@QuerinferHi,I am trying to build a same concept of FIFO but in accounts receivable,so where we get to know working capital..am little confused on the formula,can i send an email?
Thank you it is an icebreaker for many who struggle with traceability and data cleaning in the direction of the block chain. a wild solution with a very nice is presented
I was working on a similar project for days and i too had the idea of using index column. But your work around of using Group By on Index column is next level. Keep up the good work.
Best excel video ever, showing so many complex and easy concepts. Thank you very much for the time it took to explain THIS PERFECTLY how to implement in such an easy manner one quite complex problem. One more subscriber to you :)
I will write Turkish. Please translate brother. Çok teşekkür ederim bu çalışma için. Şuan kullanıyorum. Yatırım fonları ile ilgili bir sayfam var. Oradaki kullanıcılar için portföylerini takip edebilecekleri bir excel oluşturdum ve senin yöntemini kullandım. Allah senden razı olur inşallah. Türkiye'ye gelirsen beni bul 😊
OMG. This was awesome. The effort you took to make this video was also perfect. Well done, sir. You just elevated my Excel knowledge 10 fold. Thanks again!!!
Thank you for this tutorial. This is exactly what I was looking for. It would be perfect if you would expand this with tutorial on how to connect this table with another trading journal table in power pivot to we can create dynamic trading journal linked to this table. Thank you in advance.
Idea well taken. Infact I wanted to keep it simple to a single table so that we could focus on solution. But the same can be done using datamodel with multiple tables...
Thank you for sharing this video. I love the automation with Power Query. Do you have videos on Last in First Out and Highest In First Out? I would love to see a comparison video of the 3 methods using Power Query and Power Pivot.
Very nice and informative video, can you make further to calculate short term capital gain and long term capital gain share which we sold. Egarly waiting your response. Thank you very much 🙏
I would like to track what buying lot is the selling transaction associated with. The problem I see is the selling transactions would need to be split up in some cases. Your first fifo example showed allocations to different lots but the actuall selling transactions in your data doesn't split the sells like that. How would you ensure the sells are split correctly in your transaction data?
Sajad thanks a lot for your feedback. Yes there may be a better method than this or a simpler one. I am looking for ways to simplify things as well as keep them understandable for new users of DAX. Will create a video once i find a better way.
Great job, very useful! I've implemented this appoach for my dataset, works correct for the total base. But now I'm thinking on know to break the report by months, so I could compare monthly performance and etc.. I do understand that somehow the Cost of Sales should be shown in front of the every selling deal, but yet haven't solved how to do this.
@@Querinfer Yep, I guess I've found one. Can't share the calculation as I did it on my work computer, but in nutshell I did the following for every "sell" deal: 1. Made an Index for the beginning of the "buy deals" range, from which shares should be sold. 2. Calculate the number of shares, which has been sold allready (in previus "sale" deal) in the starting row of the range (Start_amount). 3. Found the price of shares for the starting row (Start Price). 4. Made an Index for the range end. 5. Calculate the amount of remaining shares in the last row of the range. (end amount). 6. Found the price for the ending row (end Price). 7. Finally, with sumX simply myltiplied Price and Quantity within previosly difined range, minus Start_amount*Start_price, minus End emount * end Price. So this is the cost of sale for every "sell" deal
@@Querinfer It would be easier with en Excel file, but with regard to our corporate security policy it’s impossible(( Additional Columns I’ve calculated are “total buy”, total buy on previous date”, “total sale”, “total sale on previous date”, using the combination of them I’ve calculated all other columns I’’ve mentioned earlier. Maybe it would help u to make your own calculation)))
Thats great... how about doing it using weighted average method as the industry uses weighted average method for valuing its investments in shares... thanks
Thanks, this is very helpful, appreciate the effort put in. The Pivot tables give the cumulative gain to date, is there a way to split this by each financial year basis the date of sale?
This is really a good explanation of building FIFO logic using excel. Thank you. What changes will need to be done if the logic is HIFO i.e. Highest Purchase Price goes out First. This would be helpful for reducing the tax liability.
Thank a lot Vivek. I dont know if tax laws allow HIFO. However, for your question. FIFO is totally dependent on the dates for creation of index and it was simple as the date for a particular transaction never changes. HIFO will be complex as we cannot allow query to run on all of the lots accounted for tax purposes in previous years. I think your data first needs to be sorted w.r.t tax year (from date) and then w.r.t the purchase price of shares. Then you can create index for your items sorted in this manner. That is all i can think of
Mashallah! It's really awesome. I am learning to apply it for making a FIFO based inventory management of a small manufacturing company. If I need some help can you please guide me in building that
Thank you for making this great video. I was able to follow and build this myself. One question I have is how do I ensure that the cost of sales is mapped to the row of the sales quantity? The reason I want to know the profit on each sale is to show the trending of P&L made each quarter. In the current model in your video, the purchases are tagged to the previous quarter while the sale is in the next quarter, thereby skewing the P&L review by quarter. Any way this can be fixed? Would greatly appreciate your insight. Thanks
Thanks a lot for writing to me. your feedback is really valueable and I am sure we can make a better working solution than this one as soon as I get time out of my ongoing series on financial modeling.
Hi man. You did really great job!!! I added life price from Coinmarketcap ... and connected it to main table in order see current value and price change in timescale. It would be great if you can make video for API connection from Bybit exchange in order get automatically list of filled orders
Very useful one .... In fact I have been just trying with Pivot and Filter functions but could not get the desired result .... thanks a lot... From the trade book whether we can arrange the columns as shown in your table with the help of power query ..?
Also in practice we have the closing stock only in number thus calculate the CoGS and the value of each closing category. We don’t track the consumption rather it becomes the derived figure. How to set abt it ? Thanks
SHKExcelWorld Thank you so much for this tutorial! It learned me many things and help me understand quite a lot. I'm now trying to adapt your template to my needs And I don't know how to do the last part i need. Would you mind helping me? I'll use the same terms as you did in your example: In my use case, the sale price of a share is defined when that share is purchased. So How do I tell excel that when I sell a share it should take into account the sale price that was defined when it was bought while still following the fifo, thus applying the earliest possible sale price of what's left in my inventory for that company share. Thank you for your help!
Thank you for your feedback. I know this project can help brokers, traders, store keepers and even accountants. and I definitely value your feedback to improve the current method. I will do it again, once i am done with my running financial modeling series.
Very well explained video. You are really genius. I will go through all your videos one by one. Thanks lot for hard work you are doing for the society. Keep it up. Earlier, It was very difficult to calculate Capital gains to file the income tax returns. Still two things are not clear to me 1. How to calculate Capital gain Financial year - wise 2. How to bifurcate Long-term Capital Gain (LTCG)and Short Term Capital Gain(STCG). Will you please advise the changes need to be done?
Thanks RP Singh for your lovely feedback. Yes I knew that the method is not fit for all, however, as a first step, it was great experience for me as well. I also want to remake it for better analysis, first I just want to finish up with my Financial modeling series.
Hi Finn! Well i believe you are absolutely right. I didn't do this, probably my table was already sorted :-) . Although, company level results will still be fine, however, if we use date filter to filter our results then this will definitely have an impact.
You video was really helpful! How can I do if I want to use the LIFO version, I tried but didn't suceed... I also tried to calculate short terms and long term capital losses/gains but I don't seem to understand...
To get the cost basis for each sales transaction, would there need to be another index for sales transactions? Also, If I want to add the date of sale, would I create a separate column in the original table? Thank you
Salam, I was googling this issue and found your video. I found it very helpful but I am still looking for one more thing, that is sale date also to be included. In a manner as you shown in first few minutes intro of this full video.
AoA dear brother. First of all thank you for this wonderful video. I'd like to ask about your advice and help regarding the similar application but which can be used for multiple items of purchased product inventory. Many thanks.
Thank you for this awesome video! Any idea about application of this FIFO calculation to be able to put average buy & sale price (and hopefully buy & sale date too), side by side columns in single row. Is it possible using power pivot?
@@Querinfer This is it. I put together something to explain it. www.linkedin.com/posts/bolajiolatunde_problemsolving-dax-powerbi-activity-6802042984142635008-YAiC
Good video... Wondering though on how to apply the FIFO on just one item. Like let's say salt having item code 001. Purchased 10 bags November 1st and 7 bags November 10th. Sold 13 bags and the cost of 1st 10 bags is 50 each while the later 7 bags costs Rs 45 each... Thanks
I have prepared the automation as detailed by you Ji. In the table I have added three additional columns. One in the beginning is ISN No and two at the end Trade Charges and STT. When these charges and STT(Securities transaction tax) are appropriated the three measures will be different. Janab how to appropriate thes charges in your model?
Thank you so much for the easiest possible explanation. Only issue with it , monthly total cost of sales aur monthly closing inventory ke figures incorrect aate hain
Thank you for such great explanation. it is very useful. In this if one want to know the absolute return and annual return on each sale on fifo based than it is not possible. Do you have any solution for this?
Hi there, Thanks for the video. However, this module didn't work for me as it assumes that Shares purchase to date does not exceed Total Sales. For example, if we had bought 700 shares on 2nd of October, then shares purchased to date will be 800 on 2nd row and Remaining Shares would be 0. Is there a workaround to that ? Your help will be appreciated.
I find myself in the same situaton where my TotalSales are most of the time lower than my SharePurchasedToDate so the calculation in the RemShares is mostly wrong since it shows 0 rather than the actual correct remaining number. Maybe instead of 0 I should put: [SharePurchaseToDate]-[TotalSales] ?
Thanks for this great FIFO solution. It really works but there is one problem. If you try to check sale , balance or profit by period (how much profit in 2024) we get a wrong result. Probably because we are dealing with Totalsales instead of each sale as separate transaction. Thank you again
Brother If have only one shares table and I want to apply mentioned format for FIFo and this table what things which I should skip in making computation table.
السلام علیکم Can you calculate Outstanding Balance of a customer on fifo method for a given period in brackets i.e., 0-30, 31-60, 61-90, 91-120, 121-180 and above 180.
A good video to demonstrate the FIFO method but unfortunately It did not work in my scenario. Let me know if I can share the details to you over the email so that you can incorporate that in coming videos as wel
I could not get past the step where you add the custom column, it throws an error. "Expression.Error: We cannot convert the value 1 to type Function. Details: Value=1 Type=[Type] What could be the reason?
I am trying to make a sheet to map Expected delivery dates & Qty. against sales orders from purchase order data. but I couldn't do it. Plz hel me. Purchase Data table having columns Item code Expected date Expected Qty. Sales Data table having Columns Sales Order No. Item code Qty req. Expected Date (to be mapped from Purchase table)
Salam.. Number convert into Word , Wo tou hogaya lekin - mai chahta hoon 15 - Fiteen only aaye ge hamara kaam bags, drums , ctn ka hain. - agar mai likho k 15 number - convert ho - in word mai - fiteen Drums Only - Its Possible
in the case of day trading, if I buy today and sell back the same quantity today, the calculator goes crazy. Because these all calculation based on FIFO. It would be great if you could provide some calculations on this or make a small video.
Hi, great tutorial but I do not know why I cannot see the option "from table/range". Where can be a problem? So wright now I stuck in very beginnig of this video.
I have 365 version at work so I will do a tutorial there. But I hope that after doing all tutorial I will be able to use this excel on older versions. What do you think?
@@Querinfer i don't mind the ad, but the ad repeats several time until I am sick of watching your youtube. sorry, I don't have much time wasting watching repeated ads.
still awaiting for that changes it will help alot of stock member those who are using CDC RDA Account for shares in PSX.they dont have any report on that. If you can do that change ASAP it will be great.
still awaiting for that changes it will help alot of stock member those who are using CDC RDA Account for shares in PSX.they dont have any report on that. If you can do that change ASAP it will be great.
the issue was ::: in the case of day trading, if I buy today and sell back the same quantity today, the calculator goes crazy. Because these all calculation based on FIFO. It would be great if you could provide some calculations on this or make a small video.
Hi Kefayat! sorry for the late reply as I am currently busy with my financial modeling series. Yes with all of your feedback I also want to revisit the whole methodology to calculate and report more.
@@Querinfer thank you sir, its will be a great pleasure for RDA accounts for PSX stocks, they don't have any application for us to calculate only you have create that but one you add the day trade also it will be a complete software of PSX for RDA calculation.
Thanks bro. This is really a great video and is directly related to many of us. Thanks a lot for making this video and helping. I am going to apply it Inshaallah.
Thanks Kamran
I am really happy to help. Stay tuned as on 24th Feb i will publish another with same solution using PowerBI. That will be more interesting and fun.
@@Querinfer Is the power bi version out yet?
Not yet
How will we have Remaining Shares of 400 on 1st Oct, 300 on 2nd etc..?
@@QuerinferHi,I am trying to build a same concept of FIFO but in accounts receivable,so where we get to know working capital..am little confused on the formula,can i send an email?
THANK YOU! Probably the only video explaining how to do this properly. A bit complex but does solve the problem.
I am happy that you liked this video. My objective was to solve the problems.
I was crying to find the asnwer online and i camr across yoy. Thank you alot and keep posting new things
Great! You found it wonderful 👍
Thank you my friend, it's a very challenging excercise but it works. Regards from Bogotá, Colombia.
My pleasure dear!
Thank you
it is an icebreaker for many who struggle with traceability and data cleaning in the direction of the block chain. a wild solution with a very nice is presented
Absolutely lived it. Thanks for putting in the effort. Regards.
My pleasure dear
Thank you for your help. Very useful video. Complicated subject turned into easy steps to understand
Thanks that was my target 🎯
Thanks! I’ve looking for this solution for months. Everything is cristal clear and I just learned new DAX formulas I never used before.
Thank you Andrea! It is an honour for me
I was working on a similar project for days and i too had the idea of using index column. But your work around of using Group By on Index column is next level. Keep up the good work.
Thank you so much Farhan... This is just about implementing in practical scenario...
Amazing! I had programmed this in SAS, but it's great to know that it can be done in Excel... thanks a lot!
Thanks Pathak for this
you are from other world, great job👏
Thanks
Superb outcome of a very vexing requirement. You are the Master in this……
Karanbir dear!
Thank you so much for your praises and appreciation.🥰
Superb rendition of a very time consuming task at the end of the year….
Thank you Karanbir for appreciation
Thank you so much for your support.This is what I was searching for.
Thanks Abubakar
Best excel video ever, showing so many complex and easy concepts. Thank you very much for the time it took to explain THIS PERFECTLY how to implement in such an easy manner one quite complex problem.
One more subscriber to you :)
Thanks David!
This is an amazing feeling when you serve your community.
Excellent video on power query
Thanks a lot
Mashalllah doing a great job.
Thanks Ahmad bhai
Excellent solution. Thanks
Krishna thanks for your praise.
Thanks a lot big brother. I was struggling for the same and you have given me. Thanks to you from bottom of my heart.
Manoj I am happy that it helped ☺️
Brilliant!. Thanks a lot. Will find a way to simplify this.
I will write Turkish. Please translate brother.
Çok teşekkür ederim bu çalışma için. Şuan kullanıyorum. Yatırım fonları ile ilgili bir sayfam var. Oradaki kullanıcılar için portföylerini takip edebilecekleri bir excel oluşturdum ve senin yöntemini kullandım. Allah senden razı olur inşallah. Türkiye'ye gelirsen beni bul 😊
OMG. This was awesome. The effort you took to make this video was also perfect. Well done, sir. You just elevated my Excel knowledge 10 fold. Thanks again!!!
it's very very important lesson I was looking for it, many thanks.
Really happy to hear this, there are tutorials online but application in real life scenario is needed..
Assalamualaikum warahmatullah great learning for me thanks of lot
Thank you for this tutorial. This is exactly what I was looking for. It would be perfect if you would expand this with tutorial on how to connect this table with another trading journal table in power pivot to we can create dynamic trading journal linked to this table. Thank you in advance.
Idea well taken. Infact I wanted to keep it simple to a single table so that we could focus on solution. But the same can be done using datamodel with multiple tables...
@@Querinfer Thank you for your response
Good job Saleem 👍
Thanks Saqib bhai
Very useful jazak Allah
Thanks dear
good informative Video, keep it up.
Thanks a lot for appreciation!
Nice video and explanation
Glad you liked it
Thank you for sharing this video. I love the automation with Power Query. Do you have videos on Last in First Out and Highest In First Out? I would love to see a comparison video of the 3 methods using Power Query and Power Pivot.
Not yet, but that is surely a good idea for my next video
Very nice and informative video, can you make further to calculate short term capital gain and long term capital gain share which we sold. Egarly waiting your response. Thank you very much 🙏
Good Job........Great Going....Keep it Up❤❤❤💥💥💝
Thanks brother!
I would like to track what buying lot is the selling transaction associated with. The problem I see is the selling transactions would need to be split up in some cases. Your first fifo example showed allocations to different lots but the actuall selling transactions in your data doesn't split the sells like that. How would you ensure the sells are split correctly in your transaction data?
Very good one amd it could have had the mathematical method as to why we do each step
Sajad thanks a lot for your feedback. Yes there may be a better method than this or a simpler one. I am looking for ways to simplify things as well as keep them understandable for new users of DAX. Will create a video once i find a better way.
Great job, very useful! I've implemented this appoach for my dataset, works correct for the total base. But now I'm thinking on know to break the report by months, so I could compare monthly performance and etc.. I do understand that somehow the Cost of Sales should be shown in front of the every selling deal, but yet haven't solved how to do this.
Glad it was helpful Nikolay! Yes I also want to explore this further. I will try and do let me know if you found a solution to it.
@@Querinfer Yep, I guess I've found one. Can't share the calculation as I did it on my work computer, but in nutshell I did the following for every "sell" deal: 1. Made an Index for the beginning of the "buy deals" range, from which shares should be sold. 2. Calculate the number of shares, which has been sold allready (in previus "sale" deal) in the starting row of the range (Start_amount). 3. Found the price of shares for the starting row (Start Price). 4. Made an Index for the range end. 5. Calculate the amount of remaining shares in the last row of the range. (end amount). 6. Found the price for the ending row (end Price). 7. Finally, with sumX simply myltiplied Price and Quantity within previosly difined range, minus Start_amount*Start_price, minus End emount * end Price. So this is the cost of sale for every "sell" deal
Hmmm thanks for sharing dear. I will try it myself for clarity
@@Querinfer It would be easier with en Excel file, but with regard to our corporate security policy it’s impossible((
Additional Columns I’ve calculated are “total buy”, total buy on previous date”, “total sale”, “total sale on previous date”, using the combination of them I’ve calculated all other columns I’’ve mentioned earlier. Maybe it would help u to make your own calculation)))
Thanks a lot for elaborating!
Thats great... how about doing it using weighted average method as the industry uses weighted average method for valuing its investments in shares... thanks
Thanks, this is very helpful, appreciate the effort put in. The Pivot tables give the cumulative gain to date, is there a way to split this by each financial year basis the date of sale?
Thank you so much for your video! Is there an option for the cost of sales to be reflected on the date of sales?
thanks for the comment. I will figure it out once I am done with my financial modeling series.
A great solution!
Thanks
This is really a good explanation of building FIFO logic using excel. Thank you.
What changes will need to be done if the logic is HIFO i.e. Highest Purchase Price goes out First. This would be helpful for reducing the tax liability.
Thank a lot Vivek. I dont know if tax laws allow HIFO. However, for your question. FIFO is totally dependent on the dates for creation of index and it was simple as the date for a particular transaction never changes. HIFO will be complex as we cannot allow query to run on all of the lots accounted for tax purposes in previous years. I think your data first needs to be sorted w.r.t tax year (from date) and then w.r.t the purchase price of shares. Then you can create index for your items sorted in this manner. That is all i can think of
Could you make a fifo basis on the order create date, and available stock. Also at last show closing inventory after allocation
Mashallah!
It's really awesome. I am learning to apply it for making a FIFO based inventory management of a small manufacturing company. If I need some help can you please guide me in building that
Thank you for making this great video. I was able to follow and build this myself. One question I have is how do I ensure that the cost of sales is mapped to the row of the sales quantity? The reason I want to know the profit on each sale is to show the trending of P&L made each quarter. In the current model in your video, the purchases are tagged to the previous quarter while the sale is in the next quarter, thereby skewing the P&L review by quarter. Any way this can be fixed? Would greatly appreciate your insight. Thanks
Thanks a lot for writing to me. your feedback is really valueable and I am sure we can make a better working solution than this one as soon as I get time out of my ongoing series on financial modeling.
I would also like to know how to do this! Did you ever figure out a solution to this problem? :)
Hi man. You did really great job!!! I added life price from Coinmarketcap ... and connected it to main table in order see current value and price change in timescale. It would be great if you can make video for API connection from Bybit exchange in order get automatically list of filled orders
Very useful one .... In fact I have been just trying with Pivot and Filter functions but could not get the desired result .... thanks a lot...
From the trade book whether we can arrange the columns as shown in your table with the help of power query ..?
Also in practice we have the closing stock only in number thus calculate the CoGS and the value of each closing category. We don’t track the consumption rather it becomes the derived figure. How to set abt it ? Thanks
SHKExcelWorld Thank you so much for this tutorial! It learned me many things and help me understand quite a lot. I'm now trying to adapt your template to my needs And I don't know how to do the last part i need. Would you mind helping me? I'll use the same terms as you did in your example: In my use case, the sale price of a share is defined when that share is purchased. So How do I tell excel that when I sell a share it should take into account the sale price that was defined when it was bought while still following the fifo, thus applying the earliest possible sale price of what's left in my inventory for that company share.
Thank you for your help!
Thank you for your feedback. I know this project can help brokers, traders, store keepers and even accountants. and I definitely value your feedback to improve the current method. I will do it again, once i am done with my running financial modeling series.
Very well explained video. You are really genius. I will go through all your videos one by one. Thanks lot for hard work you are doing for the society. Keep it up. Earlier, It was very difficult to calculate Capital gains to file the income tax returns. Still two things are not clear to me 1. How to calculate Capital gain Financial year - wise 2. How to bifurcate Long-term Capital Gain (LTCG)and Short Term Capital Gain(STCG). Will you please advise the changes need to be done?
Thanks RP Singh for your lovely feedback. Yes I knew that the method is not fit for all, however, as a first step, it was great experience for me as well. I also want to remake it for better analysis, first I just want to finish up with my Financial modeling series.
@@Querinfer Have you had a chance to revisit creating a FIFO model that RP Singh suggested?
Thank you for a great solution and a great presentation. Way to go. Have you thought about using your idea in ordinary Excel? Without DAX and PQ?
Thank you for such great help. Does this method require us to sort the date from oldest to newest?
Hi Finn! Well i believe you are absolutely right. I didn't do this, probably my table was already sorted :-) . Although, company level results will still be fine, however, if we use date filter to filter our results then this will definitely have an impact.
You video was really helpful! How can I do if I want to use the LIFO version, I tried but didn't suceed... I also tried to calculate short terms and long term capital losses/gains but I don't seem to understand...
Nice video 👍 but opening balance/stock column is missing. How to edit and add into above power query . ?
Can you please do a Phase in Phase out study?
What's that
To get the cost basis for each sales transaction, would there need to be another index for sales transactions? Also, If I want to add the date of sale, would I create a separate column in the original table? Thank you
Salam,
I was googling this issue and found your video. I found it very helpful but I am still looking for one more thing, that is sale date also to be included. In a manner as you shown in first few minutes intro of this full video.
Ok I will work on it and see the possibility as I myself am a student of DAX. Thanks for your feedback.
@@Querinfer Salam Sir,
Any hope 🙏
nice beard style, which oil you are using?
nice observation! its borges olive oil.. :-)
Hey it's really helpful. I need an addition to it. Can you help me with it?
Thank you for an awesome solution. Can you suggest what needs to be done to view the output for specific financial years? Thank you. @Querinfer
AoA dear brother. First of all thank you for this wonderful video. I'd like to ask about your advice and help regarding the similar application but which can be used for multiple items of purchased product inventory.
Many thanks.
Plus the file links are completely different from these that you've shown on the video. :(
Am I right, I think the PrevIndex column can be calculated this way: Index - 1 ?
Do you recommend a course to learn about Power Pivot and how to work with DAX formulas?
You may start with PowerQuery course of Leila Gharani on udemy
@@Querinfer Thank you Sheikh. I already started taking 'Business Intelligence w/ Power Pivot and DAX by Chris Dutton' 😎
Thank you for this awesome video!
Any idea about application of this FIFO calculation to be able to put average buy & sale price (and hopefully buy & sale date too), side by side columns in single row. Is it possible using power pivot?
Thanks Herman for liking this video...
Yes I also want to achieve this but haven't figured out yet .. let's see in the coming days .
@@Querinfer I just created a solution that works a few days ago using some tips from your video
Great Bolaji. Would love to know the steps you used, maybe I can create a video on it
@@BolajiBI I would love to know too
Thanks in advance Sir
@@Querinfer
This is it. I put together something to explain it.
www.linkedin.com/posts/bolajiolatunde_problemsolving-dax-powerbi-activity-6802042984142635008-YAiC
Sir, how to calculate purchase lot wise average sales price in ur given data sheet?. I will awaiting ur reply, thanks in advance
Good video... Wondering though on how to apply the FIFO on just one item. Like let's say salt having item code 001. Purchased 10 bags November 1st and 7 bags November 10th. Sold 13 bags and the cost of 1st 10 bags is 50 each while the later 7 bags costs Rs 45 each... Thanks
I have prepared the automation as detailed by you Ji. In the table I have added three additional columns. One in the beginning is ISN No and two at the end Trade Charges and STT. When these charges and STT(Securities transaction tax) are appropriated the three measures will be different. Janab how to appropriate thes charges in your model?
Thank you so much for the easiest possible explanation.
Only issue with it , monthly total cost of sales aur monthly closing inventory ke figures incorrect aate hain
Nice. But I need Date of purchases when shares cost is sold resulting in capital gains statement
Is there a power bi version of this? I have a very large data set. How would one do this with power bi?
Dear Harvey! No I haven't created yet. But is doable easily
@@Querinfer Great thanks. I am running out of memory in power bi trying to do "purchasestodate" calculation.
Sir I have a query whether we can directly apply FIFO calculation in excel instead of power query ..? if Yes, how ..?
Thank you for such great explanation. it is very useful. In this if one want to know the absolute return and annual return on each sale on fifo based than it is not possible. Do you have any solution for this?
Dear I haven't experimented much further. Will surely try out
the ad i watched is at least half hour than your video. unbelievable.
I don't know how TH-cam is managing ads. But yes this video is really flying 😁. But this is a problem as well...
@@Querinfer I am sorry as much as I like your video, I could not sit here watching the same ad playing within 5 mins. it's not productive.
Hi there, Thanks for the video. However, this module didn't work for me as it assumes that Shares purchase to date does not exceed Total Sales. For example, if we had bought 700 shares on 2nd of October, then shares purchased to date will be 800 on 2nd row and Remaining Shares would be 0. Is there a workaround to that ?
Your help will be appreciated.
Let me check and get back to you
@@Querinfer thanks! will be waiting
I find myself in the same situaton where my TotalSales are most of the time lower than my SharePurchasedToDate so the calculation in the RemShares is mostly wrong since it shows 0 rather than the actual correct remaining number.
Maybe instead of 0 I should put: [SharePurchaseToDate]-[TotalSales] ?
Thanks for this great FIFO solution. It really works but there is one problem. If you try to check sale , balance or profit by period (how much profit in 2024) we get a wrong result. Probably because we are dealing with Totalsales instead of each sale as separate transaction.
Thank you again
how to find aversge price of remaining shares for each date and security?
Brother If have only one shares table and I want to apply mentioned format for FIFo and this table what things which I should skip in making computation table.
Faizan I have also used a single table, so what different are you saying?
السلام علیکم
Can you calculate Outstanding Balance of a customer on fifo method for a given period in brackets i.e., 0-30, 31-60, 61-90, 91-120, 121-180 and above 180.
A good video to demonstrate the FIFO method but unfortunately It did not work in my scenario. Let me know if I can share the details to you over the email so that you can incorporate that in coming videos as wel
Yes please
shkexcelworld@gmail.com
How will we have Remaining Shares of 400 on 1st Oct, 300 on 2nd etc..?
Suppose there’s an opening stock then how should the index be calculated
I could not get past the step where you add the custom column, it throws an error. "Expression.Error: We cannot convert the value 1 to type Function.
Details:
Value=1
Type=[Type]
What could be the reason?
I am trying to make a sheet to map Expected delivery dates & Qty. against sales orders from purchase order data. but I couldn't do it. Plz hel me.
Purchase Data table having columns
Item code
Expected date
Expected Qty.
Sales Data table having Columns
Sales Order No.
Item code
Qty req.
Expected Date (to be mapped from Purchase table)
can we do this in google sheets
I think there is no power query in Google sheets
What version of Excel are you using?
Its office 365
how to do this in google sheets?
Will have to see if Google sheets any functionality like power query
Abandoned learning sumx filter function not working😢
Salam.. Number convert into Word , Wo tou hogaya lekin - mai chahta hoon 15 - Fiteen only aaye ge hamara kaam bags, drums , ctn ka hain. - agar mai likho k 15 number - convert ho - in word mai - fiteen Drums Only - Its Possible
Kya concatenate use Kia?
in the case of day trading, if I buy today and sell back the same quantity today, the calculator goes crazy. Because these all calculation based on FIFO.
It would be great if you could provide some calculations on this or make a small video.
Ok I will recheck for this scenario
Hi, great tutorial but I do not know why I cannot see the option "from table/range". Where can be a problem? So wright now I stuck in very beginnig of this video.
I suppose that reason is on office version ;-/ what version you use tell me please? I have 2016.
I am using office 365.
However there will be an option in earlier versions. Will look into these
I have 365 version at work so I will do a tutorial there. But I hope that after doing all tutorial I will be able to use this excel on older versions. What do you think?
You can use another Excel file to import data from another excel file in older version. But i feel that there will be a hidden option
Instead of excel please share same for Google sheet
Google sheets do not have Power Query and power Pivot. So thats the issue
@@Querinfer I know but any other method for Google sheet for fifo calculation for stock portfolio
Are You Gurjar ?
No, I am Kharal
lol... i see more ad than your video.
I hope the ad was interesting 🙂
@@Querinfer i don't mind the ad, but the ad repeats several time until I am sick of watching your youtube. sorry, I don't have much time wasting watching repeated ads.
Thanks for your feedback. I will certainly check if the ads settings can be changed
still awaiting for that changes it will help alot of stock member those who are using CDC RDA Account for shares in PSX.they dont have any report on that.
If you can do that change ASAP it will be great.
SHORT SELL ,intraday ,AND FUTURE SHORT NOT WORKING , KINDLY WORK FOR THIS MATTER
Cripto currency decimal qty. Not showing, kindly work this.
still awaiting for that changes it will help alot of stock member those who are using CDC RDA Account for shares in PSX.they dont have any report on that.
If you can do that change ASAP it will be great.
the issue was :::
in the case of day trading, if I buy today and sell back the same quantity today, the calculator goes crazy. Because these all calculation based on FIFO.
It would be great if you could provide some calculations on this or make a small video.
Hi Kefayat! sorry for the late reply as I am currently busy with my financial modeling series. Yes with all of your feedback I also want to revisit the whole methodology to calculate and report more.
@@Querinfer thank you sir, its will be a great pleasure for RDA accounts for PSX stocks, they don't have any application for us to calculate only you have create that but one you add the day trade also it will be a complete software of PSX for RDA calculation.