User Profile
calof1
Iron Contributor
Joined 7 years ago
User Widgets
Recent Discussions
Debug macro for importing and graphing currency prices
Hi All, I am looking to import prices into excel via a userform and then graph the historical prices. I am found some useful code and hints and on the net, however I am still experiencing run errors when run the macro. Any assistance in how to debug the process would be greatly appreciated. kind regards, Callum357Views0likes1CommentHelp to find last balance of account for person
Hi All, I have a large data set which contains prices of a product and the volume (unit held) for each date. However, I am looking to change the price of units, which in turn would change the volume of units transacted. The dataset contains multiple buyers, products and dates. So if I change the price, in turn the units transacted would change and therefore the closing balance. Is there an easy way to find this new/adjusted balance for that person and product? If someone places an order for $1,000 and price is $100, have 10 units. Then if I change price to $50, they would now have 20 units in that product. However the spreadsheet contains multiple products, and am unsure of the last date they traded to find their current balance when it comes to newer transactions. Can anyone please assist with a formula which can find their last balance of units? Greatly appreciate any assistance. Kind regards, Callum415Views0likes0CommentsMacro to find missing data points from list
Hi All, I have a spreadsheet which records prices for items each per day (10am to 5pm). The spreadsheet records prices for many funds and timeslots, so when reviewing to see if any were missed for a period can be time consuming and hard to identify. To help with this I was wondering if I can use a macro to find missing prices, given we have an expected price for 10:00,11:00,12:00,13:00,14:00,15:00,16:00,17:00 each day it has any price. So if a fund has no data/row of information at all for say 17:00, but prices for all other times on the same date it has been missed. I have put together an example with two funds, being ABC and CAT. The example has prices for both funds at each time point on the first date, however then the 17:00 data is missing for CAT on the second. I would manually find this and put the information on the missing prices tab. Since the spreadsheet can cover years of data, i often review a month or quarter at a time. So I am hoping to only include missed prices which are between the start and end of period dates given on the analysis tab. Greatly appreciate any tips or insight on how to do this. Many thanksSolved926Views0likes3CommentsVBA Macro saving files as 0kb without the data in excel
Hi All, I have a process which utilises VBA macro to extract excel files from microsoft outlook and save them in a folder with a specified file name. This process is used for a large number of files based on name, however I have noticed about half of the files get saved as a 0kb excel file. These files then have no data in them, so requires manual fix. Investigation and trials to fix this has shown the issue is there is a macro which is currently being used to save file attachments sent to the specified inbox into the fileshare system at the moment the save function is saving files as 0KB into the folder which is causing the issue with dataflow. Additional information and troubleshooting done: Inbox is running on outlook 2016 which is not outlook 365 the cloud version. PST file has been repaired on outlook server Attachments are able to open when opening through the email itself, therefore files that are being sent aren’t corrupted. System resources have been increased to ensure that the issue isn’t stemming from low resources. Additionally a scheduled job to restart the server/automatically log into the mailbox has been created on the server. I have sent a number of test emails to fund pricing inbox using ABC1 has been consistently failing. When sending these test emails I found that the process will save the attachment although having a low success rate. This means that it isn’t an issue permissions on the folder. I was wondering if there are any potential solutions or updates to the macro which could help resolve this file saving issue, to help remove the daily manual intervention. Greatly appreciate any supports or ideas. Many thanksHelp to find average of values before/after a certain date
Hi All, I have a set of data which I wish to show the average value for. There is a couple criteria so i am using averageifs to find the average value. However i wish to include a criteria to include values after a certain date. I have tried to use the formula similar to sumifs, however it appears to not be working. I have attached the excel spreadsheet i am working on, looking to update the formula is column T. Does anyone happen to know how this can be done? Greatly appreciate any assistance. Many thanks2.8KViews0likes4CommentsRe: Help to find average of values before/after a certain date
Hi thanks for the message. I have gone through and checked each stage of the other criteria. I can been able to validate the other two, just the final one which includes date is not working. I have been using 1/2/2023 as a test case in the latest spreadsheet. So i have an answer on either side of the date.2.6KViews0likes0CommentsRe: Help to find average of values before/after a certain date
Hi, thanks for the information. I have had a further look and implemented some dates which are above my set dates (1/12/2022), then some below. However i appear to still be getting an error, when there is data available for both. Any thoughts? Many thanks2.7KViews0likes2CommentsHelp with process to separate data from one excel file in multiple files in bulk
Hi All, I have a process which is very volume intensive, however the system uploading the data is only able to extract and upload one row (a days) worth of data per an excel file. Given the large volume of data which needs to be uploaded, I am wondering if there is a way I can split information from one excel into multiple files. The goal is aiming to avoid manually copying and pasting rows of data, then saving as a separate file. For the required information I have an excel file which contains all of the data which I need to separate into multiple excel files. The files I need to generate as an output require a specific naming convention. All the data is in the same format. For example if I have fund name "Fund 1", then the date is 1/11/2022. I need to save the file name as "Fund1.1-Nov-2022.xlxs". Sample of the data, output file required and construction of the naming convention is attached. I would greatly appreciate any insights or assistance on how to potentially solve this issue. Many thanks and greatly appreciate any help in advance,1.7KViews0likes4CommentsFormula to sum by month, quarter, year
Hi All, I have a spreadsheet which contains multiple tabs and is uploaded into another system. The contains information which shows for each month, quarter and year. Currently the sum for each quarter and year is manually calculated. Given the large data set, and number of sheets which get uploaded i am wondering if i can use a formula to automatically calculate this for me. I have a sample attached of monthly data, with the formula's and how i sum currently. If there are any suggestions to improve this process to make it more efficient and easier to use going forward, please let me know. Appreciate any insight/ideas. Kind regards,Help with Percentile function
Hi, I have the attached spreadsheet which contains a large volume of data. I have found the percentile function works for my top cells (first 20,000). I am aware the formula seems to stop if the row count is beyond that. However even if i change to keep the row count the same, the formula seems to stop working. Does anyone have any ideas for the reason for this? Many thanks,Solved1.1KViews0likes2CommentsDoes the percentile function on stop working if data set is to large?
Hi, I have a large data set, 86,000 results. I am hoping to use percentile to find certain levels of results. The results work fine with the formula when there is less results, however doesn't seem to calculate with the full data set. Is there a limit to the number of results it will work for? Kind regards,916Views0likes1CommentRe: Help using Nested IF formula in PERCENTILE
Hi Riny, Thank you kindly for your assistance. From your comments i noticed i needed to copy down my formula in column E, so now it has the full set. I did notice that when i did this, i no longer have the results for row 335 and 336. To run the formula, i just pressed calculate now. Is there an additional step i need to do? Many thanks for your assistance. Kind regards,2.8KViews0likes3CommentsHelp using Nested IF formula in PERCENTILE
Hi, I have a large data set, which contains many entities. I am looking to find the value of a percentile, using the percentile function on excel. To have the formula only select the entity and check in question i am looking to use the IF function. However i seem to not be able to get it to work. My two criteria are to match the "entity code" & "validation" in columns A & B. Any help or insight on what i am doing incorrectly would be very much appreciated. Kind regards,Solved3KViews0likes5CommentsUpdate Macro to be clear old values, then paste new values
To inorder to populate totals on stacked bar charts i have found the following macro. Note: The routine does not add a column in the worksheet for the totals. Instead, it adds the data in VBA, creating an array of totals, then uses this array rather than a worksheet range to populate series it adds to the chart. This makes it a bit cleaner, and it’s easier than figuring out where in the sheet to put the totals. However, it’s also not dynamic, so if the values change, the labeled totals will no longer be correct. Is there a way to update this, so i can be used again each month? Here’s the routine: Sub AddTotalsToStackedColumnChart() If ActiveSheet Is Nothing Then GoTo ExitProc If ActiveChart Is Nothing Then GoTo ExitProc Dim cht As Chart Set cht = ActiveChart If cht.ChartType = xlColumnStacked Then Dim SeriesCount As Long SeriesCount = cht.SeriesCollection.Count Dim PointCount As Long PointCount = cht.SeriesCollection(1).Points.Count Dim Totals() As Double ReDim Totals(1 To PointCount) As Double Dim SeriesIndex As Long For SeriesIndex = 1 To SeriesCount Dim YVals As Variant YVals = cht.SeriesCollection(SeriesIndex).Values Dim PointIndex As Long For PointIndex = 1 To PointCount If IsNumeric(YVals(PointIndex)) Then Totals(PointIndex) = Totals(PointIndex) + YVals(PointIndex) End If Next Next Dim IsAxisBetweenCategories As Boolean IsAxisBetweenCategories = cht.Axes(xlCategory).AxisBetweenCategories Dim NewSeries As Series Set NewSeries = cht.SeriesCollection.NewSeries With NewSeries .ChartType = xlLine .Values = Totals .Format.Line.Visible = False .HasDataLabels = True With .DataLabels .ShowValue = True .ShowCategoryName = False .ShowSeriesName = False .ShowBubbleSize = False .ShowPercentage = False .ShowLegendKey = False .Position = xlLabelPositionAbove End With End With cht.Axes(xlCategory).AxisBetweenCategories = IsAxisBetweenCategories cht.Legend.LegendEntries(cht.Legend.LegendEntries.Count).Delete End If ExitProc: End Sub Appreciate the assistance. Kind regards,1.1KViews0likes0Comments
Recent Blog Articles
No content to show